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
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
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&3as second_column and 1&2&NULL&NULL&2as 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
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
);
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
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
