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:

13 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
  8. How to split below using SQL server 2008
    -- Name: Split_String
    -- Desc: This function splits a string into 3 columns based on a delimiter, while being limited by maximum length allowed in each column
    -- Input Parameters:
    -- p_string (mandatory) - string to be split
    -- p_delimiter (optional, defaulted to comma) - the delimiter to be used to split
    -- p_limit (optional, defaulted to 45) - maximum length of each component
    -- p_ind (optional, defaulted to return all 3 columns separated by pipes) - indicates which column to be sent as result i.e column 1 or 2 or 3

    create or replace function split_string
    ( p_string in varchar2
    , p_delimiter in varchar2 := ','
    , p_limit in number := 45
    , p_ind in number := 0
    )
    return varchar2 is

    -- Defining local variables and their default values
    v_split1 number;
    v_split2 number;
    v_column1 varchar2(1000) := NULL;
    v_column2 varchar2(1000) := NULL;
    v_column3 varchar2(1000) := NULL;
    v_return varchar2(1000) := NULL;
    v_output_delimiter char(1) := '|';

    begin

    v_column1 := trim(p_string);

    -- Split only if length is more than allowed length
    if (length(v_column1) > p_limit) then

    -- Find out the LAST delimiter position with the allowed length - thereby taking as much data as possible in the first column.
    v_split1 := instr(substr(v_column1,1,p_limit+1),p_delimiter,-1);

    -- Continue only if there is a delimiter found within allowed length of characters
    if (v_split1 <> 0) then

    -- Split the string into two columns. First column is frozen from now on. Operate only on second column.
    -- IMP: Do not change the order. Column 2 is used first, as Column 1 is overwritten in the next command.
    v_column2 := trim(substr(v_column1,v_split1+1));
    v_column1 := trim(substr(v_column1,1,v_split1-1));

    -- Split only if column 2 length is more than allowed length
    if (length(v_column2) > p_limit) then

    -- Find out the LAST delimiter position with the allowed length - thereby taking as much data as possible in the second column.
    v_split2 := instr(trim(substr(v_column2,1,p_limit+1)),p_delimiter,-1);

    -- Continue only if there is a delimiter found within allowed length of characters
    if (v_split2 <> 0) then

    -- Split the column 2 further into two columns.
    -- IMP: Do not change the order. Column 3 is used first, as Column 2 is overwritten in the next command.
    v_column3 := trim(substr(v_column2,v_split2+1));
    v_column2 := trim(substr(v_column2,1,v_split2-1));
    end if;

    end if;

    end if;

    end if;

    -- Return column 1/2/3 based on the output indicator
    -- If the value is not 1/2/3, then return full string delimited by pipe.
    case p_ind
    when 1 then
    v_return := v_column1;
    when 2 then
    v_return := v_column2;
    when 3 then
    v_return := v_column3;
    else
    v_return := v_column1 || v_output_delimiter || v_column2 || v_output_delimiter || v_column3;
    end case;

    return v_return;
    end;

    ReplyDelete

Please leave a comment for this post