Geek Answers Handbook

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 string values ​​and put them on the first line, double values ​​and put them on the second line and int values ​​and put them on the third line. The line of logic is as follows:

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

but there are times when there is

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

and I would like the third line, where the default int should be 1, so the table will 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 code in which you can extract all the values ​​from a string and put them in a string, but this 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)
+4
source share
2 answers

Final version (hopefully):

sql server 2008 , cte, sum, :

;WITH cteAllRows as
(
     SELECT Item, 
            ItemIndex, 
            CASE WHEN ISNUMERIC(Item) = 0 THEN 'String'
            WHEN ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) > 0 THEN 'Double'
            WHEN ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) = 0 THEN 'Integer'
            END As DataType
     FROM dbo.SplitStrings_Numbers(@string, ',')
), cteAll as
(
    SELECT  Item, 
            DataType, 
            ItemIndex, 
            (
                SELECT COUNT(*)
                FROM cteAllRows tInner
                WHERE tInner.DataType = 'String'
                AND tInner.ItemIndex <= tOuter.ItemIndex
            ) As RowIndex
    FROM cteAllRows tOuter
)

.

Update

, , , , . , , . , , :

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Tally
    FROM sys.objects s1       
    CROSS JOIN sys.objects s2 
ALTER TABLE Tally ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number)
GO

, ( Aaron, ):

CREATE FUNCTION dbo.SplitStrings_Numbers
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN
   (
       SELECT   Item = SUBSTRING(@List, Number, CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number),
                ROW_NUMBER() OVER (ORDER BY Number) As ItemIndex
       FROM dbo.Tally
       WHERE Number <= CONVERT(INT, LEN(@List))
         AND SUBSTRING(@Delimiter + @List, Number, LEN(@Delimiter)) = @Delimiter
   );
GO

, , , , RowIndex, , :

 SELECT Item, 
        CASE WHEN ISNUMERIC(Item) = 0 THEN 'String'
        WHEN ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) > 0 THEN 'Double'
        WHEN ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) = 0 THEN 'Integer'
        END As DataType,
        SUM(CASE WHEN ISNUMERIC(Item) = 0 THEN 1 END) OVER(ORDER BY ItemIndex) As RowIndex
 FROM dbo.SplitStrings_Numbers(@string, ',')

:

Item       DataType RowIndex
---------- -------- -----------
ddd        String   1
1.5        Double   1
1          Integer  1
eee        String   2
2.3        Double   2
0          Integer  2
fff        String   3
1.2        Double   3
ggg        String   4
6.123      Double   4
1          Integer  4

, , :

;WITH cteAll as
(
     SELECT Item, 
            CASE WHEN ISNUMERIC(Item) = 0 THEN 'String'
            WHEN ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) > 0 THEN 'Double'
            WHEN ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) = 0 THEN 'Integer'
            END As DataType,
            SUM(CASE WHEN ISNUMERIC(Item) = 0 THEN 1 END) OVER(ORDER BY ItemIndex) As RowIndex
     FROM dbo.SplitStrings_Numbers(@string, ',')
), cteString AS
(
    SELECT Item, RowIndex
    FROM cteAll
    WHERE DataType = 'String'
), cteDouble AS
(
    SELECT Item, RowIndex
    FROM cteAll
    WHERE DataType = 'Double'
), cteInteger AS
(
    SELECT Item, RowIndex
    FROM cteAll
    WHERE DataType = 'Integer'
)

SELECT  T1.Item As [String],
        T2.Item As [Double],
        T3.Item As [Integer]
FROM dbo.Tally 
LEFT JOIN cteString T1 ON T1.RowIndex = Number 
LEFT JOIN cteDouble T2 ON t2.RowIndex = Number 
LEFT JOIN cteInteger T3 ON t3.RowIndex = Number
WHERE COALESCE(T1.Item, T2.Item, T3.Item) IS NOT NULL

:

String     Double     Integer
---------- ---------- ----------
ddd        1.5        1
eee        2.3        0
fff        1.2        NULL
ggg        6.123      1

, . , , :-)

, . . Aaron Bertrand - .

SplitStrings_XML.

, :

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

:

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

4 - , , . row_number() - ​​ :

;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 
)

. COALESCE , :

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

:

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

, :

declare @string as nvarchar(MAX)
declare @id int=0
set @string = 'aaa,bbb,ccc,ddd,1.5,1,eee,2.3,1,fff,1.2,ggg,6.123,1'
;with tmp( id,[type],DataItem, Data) 
as (
select 
id=row_number() over(order by @string), 'string',
LEFT(@string, CHARINDEX(',',@string+',')-1),
    STUFF(@string, 1, CHARINDEX(',',@string+','), '')
union all
select 
        case when LEFT(Data, CHARINDEX(',',Data+',')-1) like '%[a-Z]%'  then id+1
             when LEFT(Data, CHARINDEX(',',Data+',')-1) like '%[0-9]%'  then id 
        end,
        case when LEFT(Data, CHARINDEX(',',Data+',')-1) like '%[a-Z]%'  then 'string'
             when LEFT(Data, CHARINDEX(',',Data+',')-1) like '%[0-9]%' and LEFT(Data, CHARINDEX(',',Data+',')-1) not like '%.%' then 'int' 
             when LEFT(Data, CHARINDEX(',',Data+',')-1) like '%[0-9]%' and LEFT(Data, CHARINDEX(',',Data+',')-1) like '%.%' then 'double' 
        end,
        LEFT(Data, CHARINDEX(',',Data+',')-1) as dataItem,
        STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from tmp
where Data > ''
)
select  
        id,
        min(case [type]  when 'string' then DataItem end) as 'String',
        min(case [type]  when 'int' then DataItem end) as 'Int',
        min(case [type]  when 'double' then DataItem end) as 'Double'
from tmp 
group by id
option (maxrecursion 0)
+1

More articles:


All Articles