John Pietrar John Pietrar - 4 months ago 9
SQL Question

Read char,double,int pattern from string in sql

You have a string like

set @string = 'ddd,1.5,1,eee,2.3,0,fff,1.2,ggg,6.123,1'


I would like to know if there is a way to extract the string values and place them in the first row,the double values and place them in the second row and the int values and place them in the third row.The string logic is like this

"string,double,int,string,double,int..."


but there are cases when there is

"string,double,int,string,double,string,double,int"


and I would like in the third row where the int should be to be 1 by default so the table would look something like this.

First Row Second Row Third Row
ddd 1.5 1
eee 2.3 0
fff 1.2 1
ggg 6.123 1


I have a code where u can extract all the values from the string and place them in a row but that is not enough.

declare @string as nvarchar(MAX)

set @string = 'aaa,bbb,ccc,ddd,1.5,1,eee,2.3,1,fff,1.2,ggg,6.123,1'
;with tmp(DataItem, Data)
as (
select LEFT(@string, CHARINDEX(',',@string+',')-1),
STUFF(@string, 1, CHARINDEX(',',@string+','), '')
union all
select LEFT(Data, CHARINDEX(',',Data+',')-1),
STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from tmp
where Data > '')
select DataItem from tmp
option (maxrecursion 0)

Answer

Well, first you have to split that string into a table. To do that you should use a user defined function. You can pick the one best suited for you from Aaron Bertrand's Split strings the right way – or the next best way article.

For this demonstration I've chosen to use the SplitStrings_XML.

So first, create the function:

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );
GO

Now, declare and initialize the variable:

declare @string nvarchar(max) = 'ddd,1.5,1,eee,2.3,0,fff,1.2,ggg,6.123,1'

Then, Create 4 common table expressions - one for all items, one for strings, one for doubles and one for integers. Note the use of the row_number() function - it will be used later to join all the results together:

;WITH AllItems as
(
    SELECT Item, ROW_NUMBER() OVER(ORDER BY (select null)) as rn
    FROM dbo.SplitStrings_XML(@string, ',')
)

, Strings as
(
    SELECT Item as StringItem, ROW_NUMBER() OVER(ORDER BY (select null))  as rn
    FROM dbo.SplitStrings_XML(@string, ',')
    WHERE ISNUMERIC(Item) = 0
), Doubles as 
(
    SELECT Item as DoubleItem, ROW_NUMBER() OVER(ORDER BY (select null))  as rn
    FROM dbo.SplitStrings_XML(@string, ',')
    WHERE ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) > 0
), Integers as
(
    SELECT Item as IntegerItem, ROW_NUMBER() OVER(ORDER BY (select null))  as rn
    FROM dbo.SplitStrings_XML(@string, ',')
    WHERE ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) = 0 
)

Then, select from joining all these common table expressions. Note the use of the COALESCE built in function to only return rows where at least one value is present:

SELECT StringItem,  DoubleItem, IntegerItem
FROM AllItems A
LEFT JOIN Strings S ON A.rn = S.rn
LEFT JOIN Doubles D ON A.rn = D.rn
LEFT JOIN Integers I ON A.rn = I.rn
WHERE COALESCE(StringItem,  DoubleItem, IntegerItem) IS NOT NULL

Results:

StringItem  DoubleItem  IntegerItem
----------  ----------  -----------
ddd         1.5         1
eee         2.3         0
fff         1.2         1
ggg         6.123       NULL
Comments