| Author |
 Topic  |
|
|
zain2
Starting Member
United Kingdom
16 Posts |
 Posted - 03/09/2011 : 09:39:29
|
I got a table with 3 columns
ID,FNAME,TESTS
1, xyz, 'abc,tcdf,hjj,jj,jjj,j,j,j,j,j,j,'
2, zyx, 'bccc'ddd'ddd'ddd'ddd''ddd'
.. upto One thousand records
What I am trying to achieve is to separate Tests delimited string into number of columns and the output should be in this format: which would be stored in a separate table.
ID,Fname,Test1,Test2,Test3,Test4,Test5,Test6,Test7,Test8,Test9,Test10,Test11,Test12
1 xyz abc tcdf hjj jj jjj j j j j j j
First I tried to split the string (Test3) and stored in temporary table #tbl this works fine. The second step is to display each record in the above format...
I am not sure this is the best method....
I am really struggling and help in this regard is highly appreciated...
Here is split string code where i manage to split one record and not sure how to pull records from a existing table.
IF object_id('tempdb..#tbl') IS NOT NULL
BEGIN
DROP TABLE #tbl
END
create Table #tbl
(
Idx BIGINT IDENTITY (1,1),LineCounter INT,
SplitedLine varchar(8000),
Size INT
)
GO
DECLARE @WhileStart1 INT;
DECLARE @LoCofComma INT;
DECLARE @sep varchar(1);
Declare @str nvarchar(100);
DECLARE @LineCounter INT;
SET @sep =','
SET @str = '7,t,g,g,,df,d,d,d,'
SET @WhileStart1=0;
set @LineCounter = 1;
WHILE @WhileStart1 >-1
BEGIN;
SELECT @LoCofComma = CHARINDEX(@sep,@str,@WhileStart1);
IF @LoCofComma > 0
BEGIN;
INSERT INTO #tbl(SplitedLine,Size,LineCounter)
SELECT SUBSTRING(@str,@WhileStart1,@LoCofComma-@WhileStart1),@LoCofComma,@LineCounter
SET @WhileStart1 = @LoCofComma+1;
Set @LineCounter = @Linecounter+1
END;
ELSE
BEGIN;
INSERT INTO #tbl(SplitedLine,Size,LineCounter)
select '',@LoCofComma,@LineCounter
SET @WhileStart1 = -1;
END;
END;
RETURN;
go
select * from #tbl; |
Edited by - zain2 on 03/09/2011 09:43:45
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
|
|
zain2
Starting Member
United Kingdom
16 Posts |
Posted - 03/09/2011 : 09:44:51
|
Well number of columns are 12 but some columns can be empty...
Can anyone explain how the fnParseString FUNCTION please... |
Edited by - zain2 on 03/09/2011 09:53:29 |
 |
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 03/09/2011 : 09:46:22
|
quote:
Originally posted by zain2
Well number of columns are 12 but some columns can be empty...
then use fnParseString()
KH
Time is always against us
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 03/09/2011 : 10:06:24
|
copy & paste and execute
declare @str varchar(100)
select @str = 'abc,tcdf,hjj,jj,jjj,j,j,j,j,j,j'
select dbo.fnParseString(-1, ',', @str),
dbo.fnParseString(-2, ',', @str),
dbo.fnParseString(-3, ',', @str)
KH
Time is always against us
|
|
|
|
zain2
Starting Member
United Kingdom
16 Posts |
Posted - 03/09/2011 : 10:07:35
|
When i tried to execute the function it shows an error
CREATE FUNCTION dbo.fnParseString
(
@Section SMALLINT,
@Delimiter CHAR,
@Text TEXT
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @NextPos SMALLINT,
@LastPos SMALLINT,
@Found SMALLINT
IF @Section > 0
SELECT @Text = REVERSE(@Text)
SELECT @NextPos = CHARINDEX(@Delimiter, @Text, 1),
@LastPos = 0,
@Found = 1
WHILE @NextPos > 0 AND ABS(@Section) <> @Found
SELECT @LastPos = @NextPos,
@NextPos = CHARINDEX(@Delimiter, @Text, @NextPos + 1),
@Found = @Found + 1
RETURN CASE
WHEN @Found <> ABS(@Section) OR @Section = 0 THEN NULL
WHEN @Section > 0 THEN REVERSE(SUBSTRING(@Text, @LastPos + 1, CASE WHEN @NextPos = 0 THEN DATALENGTH(@Text) - @LastPos ELSE @NextPos - @LastPos - 1 END))
ELSE SUBSTRING(@Text, @LastPos + 1, CASE WHEN @NextPos = 0 THEN DATALENGTH(@Text) - @LastPos ELSE @NextPos - @LastPos - 1 END)
END
END
Msg 8116, Level 16, State 1, Procedure fnParseString, Line 16
Argument data type text is invalid for argument 1 of reverse function. |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 03/09/2011 : 10:25:40
|
change @Text to varchar(max)
@Text TEXT varchar(max)
KH
Time is always against us
|
|
|
|
zain2
Starting Member
United Kingdom
16 Posts |
Posted - 03/09/2011 : 10:59:21
|
It works great!! this is a life saving function...
I have to remove '"' from first and last column, this is how i use it..
dbo.fnParseString(-1, ',', Replace(Tests,'"','')) As Test1,
dbo.fnParseString(-2, ',', Tests) as Test2,
dbo.fnParseString(-3, ',', Replace(Tests,'"','')) As Test3
Can someone explain what is Tabled valued functions, Scalar Functions, Aggregate Function and System functions and what is the difference between them please.. |
Edited by - zain2 on 03/09/2011 11:00:34 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
|
|
ram235
Starting Member
2 Posts |
Posted - 11/01/2011 : 11:55:47
|
| if the number of strings is known then how can we do it dynamically??????????? |
|
|
|
ram235
Starting Member
2 Posts |
Posted - 11/01/2011 : 11:56:28
|
quote:
Originally posted by ram235
if the number of strings is unknown then how can we do it dynamically???????????
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
|
|
maxigodd
Starting Member
1 Posts |
Posted - 11/07/2012 : 05:11:22
|
quote:
Originally posted by khtan
copy & paste and execute
declare @str varchar(100)
select @str = 'abc,tcdf,hjj,jj,jjj,j,j,j,j,j,j'
select dbo.fnParseString(-1, ',', @str),
dbo.fnParseString(-2, ',', @str),
dbo.fnParseString(-3, ',', @str)
KH
Time is always against us
Khtan I use your solution and it works great, but iwant to insert the result of the function, into anoher table and i am stack, can you please help me out. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 11/07/2012 : 09:25:54
|
..
INSERT INTO YourTable
select dbo.fnParseString(-1, ',', @str),
dbo.fnParseString(-2, ',', @str),
dbo.fnParseString(-3, ',', @str)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Split String and store into multiple columns
