Searching for strings in files using sql

Question

I have something like

set @file = 'aaaa,bbbb,cccc,dddd,eeee,ffff'
SELECT SUBSTRING(@file,CHARINDEX(',',@file)+1, CHARINDEX(',',@file,CHARINDEX(',',@file)+1) -CHARINDEX(',',@file)-1) as My_string

This will print out

     My_string
    ----------------
   1|     bbbb

How could I make it print?

   My_string
 ------------
1|   bbbb
2|   cccc
3|   dddd
4|   eeee

Take a look here http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows — Rich Benner, Jul 06 '16 at 10:57

score 2 · Answer 1 · answered Jul 06 '16 at 11:03

Use one of the Split string functions ..I used below function..

declare @file nvarchar(max)
set @file = 'aaaa,bbbb,cccc,dddd,eeee,ffff'

select row_number() over (order by (select  null)) as number,b.*
from
[dbo].[SplitStrings_Numbers](@file,',') b

Output:

number  Item
1   aaaa
2   bbbb
3   cccc
4   dddd
5   eeee
6   ffff

split string source code i used for Completeness:

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)
           FROM dbo.Numbers
           WHERE Number <= CONVERT(INT, LEN(@List))
             AND SUBSTRING(@Delimiter + @List, Number, LEN(@Delimiter)) = @Delimiter
       );

score 2 · Answer 2 · answered Jul 06 '16 at 11:05

You could do it this way using XML.

Test Data

IF OBJECT_ID('tempdb..#TestData') IS NOT NULL DROP TABLE #TestData
GO
CREATE TABLE #TestData (StringName varchar(100))
INSERT INTO #TestData (StringName) 
VALUES ('aaaa,bbbb,cccc,dddd,eeee,ffff')

Query

SELECT Split.a.value('.', 'VARCHAR(100)') AS String  
FROM  (SELECT [StringName],  
        CAST ('<M>' + REPLACE(StringName, ',', '</M><M>') + '</M>' AS XML) AS String  
     FROM  #TestData) AS A 
CROSS APPLY String.nodes ('/M') AS Split(a);

Result

String
aaaa
bbbb
cccc
dddd
eeee
ffff

score 2 · Accepted Answer · edited May 23 '17 at 12:16

2

Try this code.,(Refered from Turning a Comma Separated string into individual rows)

declare @file varchar(max)
set @file = 'aaaa,bbbb,cccc,dddd,eeee,ffff'

;with tmp(DataItem, Data) 
as (
select LEFT(@file, CHARINDEX(',',@file+',')-1),
    STUFF(@file, 1, CHARINDEX(',',@file+','), '')
union all
select LEFT(Data, CHARINDEX(',',Data+',')-1),
    STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from tmp
where Data > '')
select DataItem from tmp

edited May 23 '17 at 12:16

Community

1
1

answered Jul 06 '16 at 11:08

DineshDB

5,998
7
33
49

I must ask something that I am curious about,is there a way to say something like `set @file = 'aaa,2,1,bbb,3,2,ccc,1,ddd,2,eee,3,2'`,is there a way to place `aaa&bbb&ccc&ddd&eee` as first_column `2&3&1&2&3`as second_column and `1&2&NULL&NULL&2`as third_column? And I am asking for this specific example not for others that would be string string int or int int string,I am asking for this example where you have string,int,int – John Pietrar Jul 06 '16 at 11:36
Yes but the inclusion of optional columns complicates matters. If you can spec out the rules, perhaps in a new question, I'm sure we can come up with something. – David Rushton Jul 06 '16 at 11:50
http://stackoverflow.com/questions/38238242/read-char-double-int-pattern-from-string-in-sql – John Pietrar Jul 07 '16 at 06:41

score 2 · Answer 4 · answered Jul 06 '16 at 11:21

DECLARE @file varchar(255)
SET @file = 'aaaa,bbbb,cccc,dddd,eeee,ffff'
DECLARE @X xml
DECLARE @Split char(1) = ','
SELECT @X = CONVERT(xml,'<root><s>' + REPLACE(@file,@Split,'</s><s>') + '</s></root>') 

SELECT [Value] = T.c.value('.','varchar(20)')
FROM @X.nodes('/root/s') T(c)

Output

Value
aaaa
bbbb
cccc
dddd
eeee
ffff

Searching for strings in files using sql

4 Answers4