DotNet Solution

  • Home
  • Asp.net
    • Controls
    • DataControl
    • Ajax
  • Web Design
    • Html
    • Css
    • Java Script
  • Sql
    • Queries
    • Function
    • Stored Procedures
  • MVC
    • OverView
    • Create First Application
  • BootStrap
    • Collapse Function

Saturday, 16 July 2016

Split Function in Sql Serever

  Unknown       03:31       No comments    

We Use This Function and Get Values .Values split With Comma Operator and Get Split Values

Ex.
CREATE function [dbo].[SplitPra]       
(       
 @list varchar(8000)       
)       
returns @t table        
(       
 Word varchar(MAX) not null,       
 Position int identity(1,1) not null       
)       
as begin       
  declare        
    @pos int,       
    @lpos int,       
    @item varchar(100),       
    @ignore varchar(100),       
    @dl int,       
    @a1 int,       
    @a2 int,       
    @z1 int,       
    @z2 int,       
    @n1 int,       
    @n2 int,       
 @n3 int,      
 @n4 int,     
 @n5 int,      
    @c varchar(1),       
    @a smallint       
  select        
    @a1 = ascii('a'),       
    @a2 = ascii('A'),       
    @z1 = ascii('z'),       
    @z2 = ascii('Z'),       
    @n1 = ascii('0'),       
    @n2 = ascii('9'),       
 @n3 = ascii('.'),     
 @n4 = ascii('-'),     
 @n5 = ascii(' ')     
  set @ignore = '''"'       
  set @pos = 1       
  set @dl = datalength(@list)       
  set @lpos = 1       
  set @item = ''       
  while (@pos <= @dl) begin       
    set @c = substring(@list, @pos, 1)       
    if (@ignore not like '%' + @c + '%') begin       
      set @a = ascii(@c)       
      if ((@a >= @a1) and (@a <= @z1))         
        or ((@a >= @a2) and (@a <= @z2))       
        or ((@a >= @n1) and (@a <= @n2))       
  or (@a=@n3) or (@a=@n4) or (@a=@n5)      
      begin       
        set @item = @item + @c       
      end else if (@item > '') begin       
        insert into @t values (@item)       
        set @item = ''       
      end       
    end        
    set @pos = @pos + 1       
  end       
  if (@item > '') begin       
    insert into @t values (@item)       
  end       
  return       
end


  • Share This:  
  •  Facebook
  •  Twitter
  •  Google+
Email This BlogThis! Share to X Share to Facebook
Newer Post Older Post Home

0 comments :

Post a Comment

Popular Posts

  • Auto Increment Id in Sql Server
    SQL server identity column values use in table Steps- Firstly Open-> Sql Server Create Table -> First Field Like ID Usually Autoi...
  • (no title)
    SQL SELECT Query The SELECT statement is used to select data from a database. Query SELECT * FROM TABLE NAME. Use this query we get All...
  • MVC OverView
    I ntroduction The Model-View-Controller (MVC) architectural pattern separates an application into three main components: the model, the v...
  • CSS 3D Transforms
    <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head...
  • Create First MVC Application
    How to create MVC First simple application It is very easy to make application in mvc follow some steps and create first application ST...
  • Alter Query
    To add a column in a table, use Alter Query the following syntax: ALTER TABLE table_name ADD column_name datatype. To delete a column ...
  • Values Enter With SplitFunction in Sql Server
    Basically We Use Split Function Thorugh Commma Sepertaed string value with  Comma  Means One Then more value through split function Enter...
  • Get Random Rows in SqlServer
    How To Get  Random  ROWS in  Table  Using Sql Server Step 1-                Open Sql Server and Create Table like in Ex. Ex. Step...
  • Current Date
    Show Current Date-  I Want To Show Current Date Then Use-   SELECT GETDATE()  AS CurrentDate OutPut-            2016-06-08 12:11:00.08...
  • Javascript Regular Expression Email Validate
    Validate Email using Regular Expression In JavaScript- 1. Create Input type Text box and and onclick cal  checkemail function of Javascr...

Blog Archive

  • ▼  2016 ( 36 )
    • ►  February ( 1 )
    • ►  March ( 5 )
    • ►  April ( 1 )
    • ►  June ( 10 )
    • ▼  July ( 6 )
      • Asp.net- MultiView
      • Reset Identity Column in Sql Server
      • Count Number Of Word In Sql Server
      • Values Enter With SplitFunction in Sql Server
      • Split Function in Sql Serever
      • Numeric Value Validation Through JavaScript
    • ►  November ( 8 )
    • ►  December ( 5 )
  • ►  2017 ( 1 )
    • ►  January ( 1 )
Powered by Blogger.

Categories

  • Ajax
  • AllFunction
  • Controls
  • CreateApplication
  • css
  • Function
  • javascript
  • Js
  • over view
  • OverView
  • Queries
  • StoredProcedures

Text Widget

Sample Text

Pages

  • Home

Copyright © DotNet Solution | Powered by Blogger
Design by Vibha Acharya