Thursday, 19 September 2013

How to Split a string by delimited char in SQL Server..............

However I posted this one in one of my post.
Querying Microsoft SQL Server : Functions in SQL Server: Functions in SQL Server In SQL Server functions are subrotienes that encapsulate a group of T-SQL statements for reuse.SQL Server pro...

Here I separate ,Split function from that post.

CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1
       
        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)
        
    END 
    RETURN 
END


Exceute this T-sql statements to create function and use as

select *from dbo.fnSplitString('Querying SQL Server','')

Output:

12 comments:

  1. Need some help, If i have a row like this

    identity Name Column1 Column2 Column 3

    1 test 1,2,3 100,200,300 55,65,75

    and i want to split it like

    identity id name column1 column2 column3
    1 test 1 100 55
    1 test 2 200 65
    1 test 3 300 75

    How to do it?

    ReplyDelete
  2. Hi,

    How do i write a query using the split function for the following requirment

    I have a table in the following way

    Identity Name Col1 Col2 Col3
    1 Test1 1,2,3 200,300,400 3.4,6
    2 Test2 3,4,5 300,455,600 2,3,8

    I want an output in the following format

    Identity Name Col1 Col2 Col3
    1 Test1 1 200 3
    1 Test1 2 300 4
    1 Test1 3 400 6
    2 Test2 3 300 2
    2 Test2 4 455 3
    2 Test2 5 600 8

    this col1, col2 etc is an identity value from another table and i need ti use joins to get it

    ReplyDelete
    Replies
    1. Use Cross Apply
      as

      SELECT a.name, b.splitdata
      FROM dbo.tblsplit a
      CROSS APPLY dbo.fnSplitString(a.col1,',') AS b

      Delete
    2. This comment has been removed by the author.

      Delete
  3. This works like a charm when i have one column to do it, if you see my example i need string split in 3 columns and map the values in the column

    Identity Name Col1 Col2 Col3
    1 Test1 1,2,3 200,300,400 3.4,6
    2 Test2 3,4,5 300,455,600 2,3,8

    I want an output in the following format

    Identity Name Col1 Col2 Col3
    1 Test1 1 200 3
    1 Test1 2 300 4
    1 Test1 3 400 6
    2 Test2 3 300 2
    2 Test2 4 455 3
    2 Test2 5 600 8

    ReplyDelete
    Replies
    1. To join based on id of table and comma delimited values
      as

      select t.value,k.name,k.col1 from table1 t
      inner join
      (SELECT a.name, b.splitdata as col1
      FROM dbo.tblsplit a
      CROSS APPLY dbo.fnSplitString(a.col1,',') AS b) k on k.col1=t.id

      and if you want to put this in one table
      select k.name,k.col1,k.col2,k.col3 from
      (
      SELECT a.name, b.splitdata as col1 ,c.splitdata as col2,d.splitdata as col3
      FROM dbo.tblsplit a
      CROSS APPLY dbo.fnSplitString(a.col1,',') AS b
      CROSS APPLY dbo.fnSplitString(a.col2,',') AS c
      CROSS APPLy dbo.fnSplitString(a.col3,',') AS d
      )k

      Delete
  4. How to split this

    select *from dbo.fnSplitString( 'Quer(,)ying,SQL,Server' , ',' )

    I want 3 rows
    Quer(,)ying
    SQL
    Server

    ReplyDelete
    Replies
    1. For this you need to change function definition , or encode decode your input string and results after splitting.
      For EX:
      CREATE FUNCTION [dbo].[fnSplitString]
      (
      @string NVARCHAR(MAX),
      @delimiter CHAR(1)
      )
      RETURNS @output TABLE(splitdata NVARCHAR(MAX)
      )
      BEGIN
      SET @string=REPLACE(@string,'(,)','$$')
      DECLARE @start INT, @end INT
      SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
      WHILE @start < LEN(@string) + 1 BEGIN
      IF @end = 0
      SET @end = LEN(@string) + 1

      INSERT INTO @output (splitdata)
      VALUES(REPLACE(SUBSTRING(@string, @start, @end - @start),'$$','(,)'))
      SET @start = @end + 1
      SET @end = CHARINDEX(@delimiter, @string, @start)

      END
      RETURN
      END

      select *from dbo.fnSplitString( 'Quer(,)ying,SQL,Server' , ',' )
      returns expected result.

      Delete
  5. Hey Uma,

    I just came across your post while searching google, and I want to thank you because it has been very helpful and has solved half of my headaches.I was wondering if you can help me expand it further...

    How do I use your SplitStrin function for splitting column/fields with multiple values, instead of taking just a raw string as input, I want it to take a column as input and go through the whole column and split the fields that have multiple values in them into rows. Example

    SELECT * FROM dbo.fnSplitStrings (dbo.JobFunction, '#')

    I have a table that has:

    ID | Fname | Lname | ....other fields...... | JobFunction | ......etc.
    12 | John | Doe |....other fields........| 102#103#105 | .......etc.

    I want the output to be:

    ID | Fname | Lname | ....other fields...... | JobFunction | ......etc.
    12 | John | Doe |....other fields........| 102 | .......etc.
    12 | null | null |....other fields........| 103 | .......etc.
    12 | null | null |....other fields........| 105 | .......etc.

    ReplyDelete
  6. Try this I think it will help
    SELECT a.ID , a.Fname , a.Lname, b.splitdata
    FROM dbo.TableName a
    CROSS APPLY dbo.fnSplitString(a.col1,',') AS b

    ReplyDelete
  7. I have 30 items inside a sql database, when it is called by $feat_list on the web page all 30 items are displayed in one loooong column, how do I split this so it is placed inside 2 columns on the web page, tried css but cant get it right

    ReplyDelete
    Replies
    1. if you are using php,you can use script as
      $i = 0;
      echo "<tr>";
      while($row = mysql_fetch_array($results))
      {
      if ($i % 25 == 0) echo "</tr><tr>";

      echo "<td>".$row['Destination']."</td>";
      $i++;
      }
      echo "</tr>";
      http://stackoverflow.com/questions/18013970/split-table-rows-into-columns

      Delete

Please leave a comment for this post