Split CSV string into columns (T-SQL)

ulf-jzl
ulf-jzl used Ask the Experts™
on
Hi guys!

I like to split a csv string into columns

Declare @data varchar(300)
SET @data = '234234.24;234.54;23443.75'

=> Result:
Col1             |  Col2       |      Col3
-----------------------------------------------------
234234.24   |  234.54   |      23443.75'

The csv string is much longer in reality, over 300 characters

Important!!  The result must be inserted into a new table.

Any tips?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Anuradha GoliSystems Development / Support Specialist

Commented:
Create user defined function in SQL server
CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))       
returns @temptable TABLE (items varchar(8000))       
as       
begin       
    declare @idx int       
    declare @slice varchar(8000)       
      
    select @idx = 1       
        if len(@String)<1 or @String is null  return       
      
    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       
          
        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       
  
        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return       
end  

Open in new window

Usage:
Declare @data varchar(300)
SET @data = '234234.24;234.54;23443.75'
select *  into Datatable from dbo.split(@data,';') 

Open in new window

You can use a column to store data split into multiple rows.
Hope this helps.

Author

Commented:
No, sorry, I asked for splitting the data into columns, this does the following

This is want I need;
=> Result:
Col1             |  Col2       |      Col3
-----------------------------------------------------
234234.24   |  234.54   |      23443.75'

Not
=> Result:
Col1      
-----------      
234234.24  
234.54
23443.75'
Commented:
"I" solved it, any tips to make this go any faster?

CREATE FUNCTION [dbo].[inline_split_me](@param varchar(300))
RETURNS TABLE AS
RETURN(
SELECT
	ROW_NUMBER() OVER(ORDER BY Number) AS pos,
	ltrim(
		rtrim(
			convert(varchar(300), substring(@param, Number,
				charindex(N';', @param + convert(varchar(300), N';'), Number) - Number)
	))) AS Value
FROM dbo.fn_nums(convert(int, len(@param)))
WHERE substring(convert(varchar(300), N';') + @param, Number, 1) = N';' 
)

SELECT
	[TimeStamp],
	CAST(REPLACE([1], ',', '.') as float) as Freq_250, 
	CAST(REPLACE([2], ',', '.') as float) as Freq_265, 
FROM
	(SELECT
	[TimeStamp],
	[THD] 
FROM 
	[dbo].[TResults] 
WHERE 
	[THD] IS NOT NULL AND
	[TimeStamp] >= '2012-01-01') AS T
	OUTER APPLY
	inline_split_me(T.[THD]) AS S
	PIVOT
	(
	MAX(Value)
	FOR pos IN ([1], [2])
	) AS P;

Open in new window

Author

Commented:
I solved it..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial