Split up comma-delimited field

[Jayson]

I have a row in a SQL table that has 4 numerical values, separated by comma. I'd like to take this and make it 4 separate columns. Values are not always the same length, but are always delimited by commas.

Any ideas how I could do this in T-SQL?

[Jun]

Hi Jayson! Are you doing a lot of string manipulation?

Here is the way you can try: (this may not be the best way)

-- Create 5 extra columns except your TextNum column:
ALTER TABLE YourTable -- Suppose your table name is YourTable
Add Column1 Varchar (50), Column2 Varchar (50), Column3 Varchar (50), Column4 Varchar (50), Column5 Varchar (50);


-- Divide your TextNum column and move the first and last number out
Update YourTable -- Your data in Column 'TextNum'
-- Get the first number into column1
SET Column1 = LEFT (TextNum, CHARINDEX (',', TextNum)-1),

-- Get the last number into column4
Column4 = RIGHT (TextNum, CHARINDEX (',', REVERSE(TextNum) ) -1),

-- Get the two numbers in the middle into column5
Column5 = SUBSTRING (TextNum, CHARINDEX (',', TextNum)+1), LEN(TextNum) -CHARINDEX (',', TextNum) );


-- Divide Column5 into Column3 and Column4
Update YourTable
-- Get the first number into column1
SET Column2 = LEFT (column5, CHARINDEX (',', Column5)-1),

-- Get the last number into column4
Column3 = RIGHT (Column5, CHARINDEX (',', REVERSE(Column5) ) -1);

-- Delete Column5
ALTER TABLE YourTable
DROP Column5;

Hope this is the route you would like to go!

Jun














------------
Jayson at 6/7/2002 3:39:12 PM


I have a row in a SQL table that has 4 numerical values, separated by comma. I'd like to take this and make it 4 separate columns. Values are not always the same length, but are always delimited by commas.

Any ideas how I could do this in T-SQL?

[Bill]

I haven't checked the whole procedure, but this code:

Column4 = RIGHT (TextNum, CHARINDEX (',', REVERSE(TextNum) ) -1),

won't work correctly. The number will be reversed. Do this instead:

Column4 = REVERSE(RIGHT (TextNum, CHARINDEX (',', REVERSE(TextNum) ) -1)),


Another alternative in SQL 2000 would be to build a user-defined function that parses a string delimited by commas, spaces, etc. and returns the requested element.
For example, a function such as fnStringParse('this is a test','space',2) returns 'is'. I haven't built such a function otherwise I'd paste it here, but with it you could:

update YourTable
set NewColumn1 = fnStringParse(TextNum,'comma',1),
NewColumn2 = fnStringParse(TextNum,'comma',2),
NewColumn3 = fnStringParse(TextNum,'comma',3),
NewColumn4 = fnStringParse(TextNum,'comma',4),
NewColumn5 = fnStringParse(TextNum,'comma',5)



------------
Jun at 6/8/2002 2:32:25 PM

Hi Jayson! Are you doing a lot of string manipulation?

Here is the way you can try: (this may not be the best way)

-- Create 5 extra columns except your TextNum column:
ALTER TABLE YourTable -- Suppose your table name is YourTable
Add Column1 Varchar (50), Column2 Varchar (50), Column3 Varchar (50), Column4 Varchar (50), Column5 Varchar (50);


-- Divide your TextNum column and move the first and last number out
Update YourTable -- Your data in Column 'TextNum'
-- Get the first number into column1
SET Column1 = LEFT (TextNum, CHARINDEX (',', TextNum)-1),

-- Get the last number into column4
Column4 = RIGHT (TextNum, CHARINDEX (',', REVERSE(TextNum) ) -1),

-- Get the two numbers in the middle into column5
Column5 = SUBSTRING (TextNum, CHARINDEX (',', TextNum)+1), LEN(TextNum) -CHARINDEX (',', TextNum) );


-- Divide Column5 into Column3 and Column4
Update YourTable
-- Get the first number into column1
SET Column2 = LEFT (column5, CHARINDEX (',', Column5)-1),

-- Get the last number into column4
Column3 = RIGHT (Column5, CHARINDEX (',', REVERSE(Column5) ) -1);

-- Delete Column5
ALTER TABLE YourTable
DROP Column5;

Hope this is the route you would like to go!

Jun














------------
Jayson at 6/7/2002 3:39:12 PM


I have a row in a SQL table that has 4 numerical values, separated by comma. I'd like to take this and make it 4 separate columns. Values are not always the same length, but are always delimited by commas.

Any ideas how I could do this in T-SQL?

[Bill]

Here's a function that can't use white characters as delimiters, but is robust enough for this problem.


CREATE FUNCTION fnStringToken(@InputString varchar(8000),@Delimiter varchar(20),@TokenNum int)
RETURNS varchar(8000)
/*
This function parses/tokenizes an input string given a delimiter and returns the Nth token
No delimiter is expected at the end of the input string so the code accommodates this

Required parameters:
@InputString varchar(8000)
@Delimiter varchar(20)
@TokenNum int

Returns varchar(8000)

Sample call:
select dbo.fnStringToken('this is a test',' ',2) will return 'is'
*/
AS
BEGIN
declare @DelimiterPosition int
declare @Token varchar(8000)

if @TokenNum<=0 RETURN (null)

-- move through the string to find the Nth token
while @TokenNum >= 1
begin
set @DelimiterPosition=charindex(@Delimiter,@InputStri ng)

if @DelimiterPosition <> 0
set @Token = left(@InputString,@DelimiterPosition-1)
else
begin
if @TokenNum=1 set @Token = @InputString -- no delimiter found so must be last/only token
else set @Token = null -- requested Nth token is past end of string
set @TokenNum = 0
end

-- strip off token from input string
set @InputString = right(@InputString,len(@InputString)-@DelimiterPosition)
set @TokenNum = @TokenNum - 1
end

RETURN (@Token)
END




------------
Bill at 6/10/2002 10:03:49 AM

I haven&#39;t checked the whole procedure, but this code:

Column4 = RIGHT (TextNum, CHARINDEX (&#39;,&#39;, REVERSE(TextNum) ) -1),

won&#39;t work correctly. The number will be reversed. Do this instead:

Column4 = REVERSE(RIGHT (TextNum, CHARINDEX (&#39;,&#39;, REVERSE(TextNum) ) -1)),


Another alternative in SQL 2000 would be to build a user-defined function that parses a string delimited by commas, spaces, etc. and returns the requested element.
For example, a function such as fnStringParse(&#39;this is a test&#39;,&#39;space&#39;,2) returns &#39;is&#39;. I haven&#39;t built such a function otherwise I&#39;d paste it here, but with it you could:

update YourTable
set NewColumn1 = fnStringParse(TextNum,&#39;comma&#39;,1),
NewColumn2 = fnStringParse(TextNum,&#39;comma&#39;,2),
NewColumn3 = fnStringParse(TextNum,&#39;comma&#39;,3),
NewColumn4 = fnStringParse(TextNum,&#39;comma&#39;,4),
NewColumn5 = fnStringParse(TextNum,&#39;comma&#39;,5)



------------
Jun at 6/8/2002 2:32:25 PM

Hi Jayson! Are you doing a lot of string manipulation?

Here is the way you can try: (this may not be the best way)

-- Create 5 extra columns except your TextNum column:
ALTER TABLE YourTable -- Suppose your table name is YourTable
Add Column1 Varchar (50), Column2 Varchar (50), Column3 Varchar (50), Column4 Varchar (50), Column5 Varchar (50);


-- Divide your TextNum column and move the first and last number out
Update YourTable -- Your data in Column &#39;TextNum&#39;
-- Get the first number into column1
SET Column1 = LEFT (TextNum, CHARINDEX (&#39;,&#39;, TextNum)-1),

-- Get the last number into column4
Column4 = RIGHT (TextNum, CHARINDEX (&#39;,&#39;, REVERSE(TextNum) ) -1),

-- Get the two numbers in the middle into column5
Column5 = SUBSTRING (TextNum, CHARINDEX (&#39;,&#39;, TextNum)+1), LEN(TextNum) -CHARINDEX (&#39;,&#39;, TextNum) );


-- Divide Column5 into Column3 and Column4
Update YourTable
-- Get the first number into column1
SET Column2 = LEFT (column5, CHARINDEX (&#39;,&#39;, Column5)-1),

-- Get the last number into column4
Column3 = RIGHT (Column5, CHARINDEX (&#39;,&#39;, REVERSE(Column5) ) -1);

-- Delete Column5
ALTER TABLE YourTable
DROP Column5;

Hope this is the route you would like to go!

Jun














------------
Jayson at 6/7/2002 3:39:12 PM


I have a row in a SQL table that has 4 numerical values, separated by comma. I&#39;d like to take this and make it 4 separate columns. Values are not always the same length, but are always delimited by commas.

Any ideas how I could do this in T-SQL?

[Jun]

Hi Bill,

What do you mean &#34;Column4 = RIGHT (TextNum, CHARINDEX (&#39;,&#39;, REVERSE(TextNum) ) -1)&#34; won&#39;t work correctly? The Function &#34;Right (TextNum, ...)&#34; will NOT reverse your number, and the &#39;REVERSE&#39; function used here is just for finding the length of the part of last number.

If I can use a simple function to get my string manipulated, I wouldn&#39;t bother to use a very long user-defined stored procedure.

Have good day!

Jun

------------
Bill at 6/10/2002 10:03:49 AM

I haven&#39;t checked the whole procedure, but this code:

Column4 = RIGHT (TextNum, CHARINDEX (&#39;,&#39;, REVERSE(TextNum) ) -1),

won&#39;t work correctly. The number will be reversed. Do this instead:

Column4 = REVERSE(RIGHT (TextNum, CHARINDEX (&#39;,&#39;, REVERSE(TextNum) ) -1)),


Another alternative in SQL 2000 would be to build a user-defined function that parses a string delimited by commas, spaces, etc. and returns the requested element.
For example, a function such as fnStringParse(&#39;this is a test&#39;,&#39;space&#39;,2) returns &#39;is&#39;. I haven&#39;t built such a function otherwise I&#39;d paste it here, but with it you could:

update YourTable
set NewColumn1 = fnStringParse(TextNum,&#39;comma&#39;,1),
NewColumn2 = fnStringParse(TextNum,&#39;comma&#39;,2),
NewColumn3 = fnStringParse(TextNum,&#39;comma&#39;,3),
NewColumn4 = fnStringParse(TextNum,&#39;comma&#39;,4),
NewColumn5 = fnStringParse(TextNum,&#39;comma&#39;,5)



------------
Jun at 6/8/2002 2:32:25 PM

Hi Jayson! Are you doing a lot of string manipulation?

Here is the way you can try: (this may not be the best way)

-- Create 5 extra columns except your TextNum column:
ALTER TABLE YourTable -- Suppose your table name is YourTable
Add Column1 Varchar (50), Column2 Varchar (50), Column3 Varchar (50), Column4 Varchar (50), Column5 Varchar (50);


-- Divide your TextNum column and move the first and last number out
Update YourTable -- Your data in Column &#39;TextNum&#39;
-- Get the first number into column1
SET Column1 = LEFT (TextNum, CHARINDEX (&#39;,&#39;, TextNum)-1),

-- Get the last number into column4
Column4 = RIGHT (TextNum, CHARINDEX (&#39;,&#39;, REVERSE(TextNum) ) -1),

-- Get the two numbers in the middle into column5
Column5 = SUBSTRING (TextNum, CHARINDEX (&#39;,&#39;, TextNum)+1), LEN(TextNum) -CHARINDEX (&#39;,&#39;, TextNum) );


-- Divide Column5 into Column3 and Column4
Update YourTable
-- Get the first number into column1
SET Column2 = LEFT (column5, CHARINDEX (&#39;,&#39;, Column5)-1),

-- Get the last number into column4
Column3 = RIGHT (Column5, CHARINDEX (&#39;,&#39;, REVERSE(Column5) ) -1);

-- Delete Column5
ALTER TABLE YourTable
DROP Column5;

Hope this is the route you would like to go!

Jun














------------
Jayson at 6/7/2002 3:39:12 PM


I have a row in a SQL table that has 4 numerical values, separated by comma. I&#39;d like to take this and make it 4 separate columns. Values are not always the same length, but are always delimited by commas.

Any ideas how I could do this in T-SQL?

[Bill]

Hi Jun
You&#39;re right...the statement will work correctly. I don&#39;t know what I was thinking. (apologies to Jayson if he used my &#34;correction&#34 If you did Jayson, re-run Jun&#39;s update procedure.

------------
Jun at 6/10/2002 8:17:27 PM

Hi Bill,

What do you mean &#34;Column4 = RIGHT (TextNum, CHARINDEX (&#39;,&#39;, REVERSE(TextNum) ) -1)&#34; won&#39;t work correctly? The Function &#34;Right (TextNum, ...)&#34; will NOT reverse your number, and the &#39;REVERSE&#39; function used here is just for finding the length of the part of last number.

If I can use a simple function to get my string manipulated, I wouldn&#39;t bother to use a very long user-defined stored procedure.

Have good day!

Jun

------------
Bill at 6/10/2002 10:03:49 AM

I haven&#39;t checked the whole procedure, but this code:

Column4 = RIGHT (TextNum, CHARINDEX (&#39;,&#39;, REVERSE(TextNum) ) -1),

won&#39;t work correctly. The number will be reversed. Do this instead:

Column4 = REVERSE(RIGHT (TextNum, CHARINDEX (&#39;,&#39;, REVERSE(TextNum) ) -1)),


Another alternative in SQL 2000 would be to build a user-defined function that parses a string delimited by commas, spaces, etc. and returns the requested element.
For example, a function such as fnStringParse(&#39;this is a test&#39;,&#39;space&#39;,2) returns &#39;is&#39;. I haven&#39;t built such a function otherwise I&#39;d paste it here, but with it you could:

update YourTable
set NewColumn1 = fnStringParse(TextNum,&#39;comma&#39;,1),
NewColumn2 = fnStringParse(TextNum,&#39;comma&#39;,2),
NewColumn3 = fnStringParse(TextNum,&#39;comma&#39;,3),
NewColumn4 = fnStringParse(TextNum,&#39;comma&#39;,4),
NewColumn5 = fnStringParse(TextNum,&#39;comma&#39;,5)



------------
Jun at 6/8/2002 2:32:25 PM

Hi Jayson! Are you doing a lot of string manipulation?

Here is the way you can try: (this may not be the best way)

-- Create 5 extra columns except your TextNum column:
ALTER TABLE YourTable -- Suppose your table name is YourTable
Add Column1 Varchar (50), Column2 Varchar (50), Column3 Varchar (50), Column4 Varchar (50), Column5 Varchar (50);


-- Divide your TextNum column and move the first and last number out
Update YourTable -- Your data in Column &#39;TextNum&#39;
-- Get the first number into column1
SET Column1 = LEFT (TextNum, CHARINDEX (&#39;,&#39;, TextNum)-1),

-- Get the last number into column4
Column4 = RIGHT (TextNum, CHARINDEX (&#39;,&#39;, REVERSE(TextNum) ) -1),

-- Get the two numbers in the middle into column5
Column5 = SUBSTRING (TextNum, CHARINDEX (&#39;,&#39;, TextNum)+1), LEN(TextNum) -CHARINDEX (&#39;,&#39;, TextNum) );


-- Divide Column5 into Column3 and Column4
Update YourTable
-- Get the first number into column1
SET Column2 = LEFT (column5, CHARINDEX (&#39;,&#39;, Column5)-1),

-- Get the last number into column4
Column3 = RIGHT (Column5, CHARINDEX (&#39;,&#39;, REVERSE(Column5) ) -1);

-- Delete Column5
ALTER TABLE YourTable
DROP Column5;

Hope this is the route you would like to go!

Jun














------------
Jayson at 6/7/2002 3:39:12 PM


I have a row in a SQL table that has 4 numerical values, separated by comma. I&#39;d like to take this and make it 4 separate columns. Values are not always the same length, but are always delimited by commas.

Any ideas how I could do this in T-SQL?

[Jayson]

------------
Bill at 6/11/2002 9:37:26 AM

Hi Jun
You&#39;re right...the statement will work correctly. I don&#39;t know what I was thinking. (apologies to Jayson if he used my &#34;correction&#34 If you did Jayson, re-run Jun&#39;s update procedure.

------------
Jun at 6/10/2002 8:17:27 PM

Hi Bill,

What do you mean &#34;Column4 = RIGHT (TextNum, CHARINDEX (&#39;,&#39;, REVERSE(TextNum) ) -1)&#34; won&#39;t work correctly? The Function &#34;Right (TextNum, ...)&#34; will NOT reverse your number, and the &#39;REVERSE&#39; function used here is just for finding the length of the part of last number.

If I can use a simple function to get my string manipulated, I wouldn&#39;t bother to use a very long user-defined stored procedure.

Have good day!

Jun

------------
Bill at 6/10/2002 10:03:49 AM

I haven&#39;t checked the whole procedure, but this code:

Column4 = RIGHT (TextNum, CHARINDEX (&#39;,&#39;, REVERSE(TextNum) ) -1),

won&#39;t work correctly. The number will be reversed. Do this instead:

Column4 = REVERSE(RIGHT (TextNum, CHARINDEX (&#39;,&#39;, REVERSE(TextNum) ) -1)),


Another alternative in SQL 2000 would be to build a user-defined function that parses a string delimited by commas, spaces, etc. and returns the requested element.
For example, a function such as fnStringParse(&#39;this is a test&#39;,&#39;space&#39;,2) returns &#39;is&#39;. I haven&#39;t built such a function otherwise I&#39;d paste it here, but with it you could:

update YourTable
set NewColumn1 = fnStringParse(TextNum,&#39;comma&#39;,1),
NewColumn2 = fnStringParse(TextNum,&#39;comma&#39;,2),
NewColumn3 = fnStringParse(TextNum,&#39;comma&#39;,3),
NewColumn4 = fnStringParse(TextNum,&#39;comma&#39;,4),
NewColumn5 = fnStringParse(TextNum,&#39;comma&#39;,5)



------------
Jun at 6/8/2002 2:32:25 PM

Hi Jayson! Are you doing a lot of string manipulation?

Here is the way you can try: (this may not be the best way)

-- Create 5 extra columns except your TextNum column:
ALTER TABLE YourTable -- Suppose your table name is YourTable
Add Column1 Varchar (50), Column2 Varchar (50), Column3 Varchar (50), Column4 Varchar (50), Column5 Varchar (50);


-- Divide your TextNum column and move the first and last number out
Update YourTable -- Your data in Column &#39;TextNum&#39;
-- Get the first number into column1
SET Column1 = LEFT (TextNum, CHARINDEX (&#39;,&#39;, TextNum)-1),

-- Get the last number into column4
Column4 = RIGHT (TextNum, CHARINDEX (&#39;,&#39;, REVERSE(TextNum) ) -1),

-- Get the two numbers in the middle into column5
Column5 = SUBSTRING (TextNum, CHARINDEX (&#39;,&#39;, TextNum)+1), LEN(TextNum) -CHARINDEX (&#39;,&#39;, TextNum) );


-- Divide Column5 into Column3 and Column4
Update YourTable
-- Get the first number into column1
SET Column2 = LEFT (column5, CHARINDEX (&#39;,&#39;, Column5)-1),

-- Get the last number into column4
Column3 = RIGHT (Column5, CHARINDEX (&#39;,&#39;, REVERSE(Column5) ) -1);

-- Delete Column5
ALTER TABLE YourTable
DROP Column5;

Hope this is the route you would like to go!

Jun








It gets more complicated. I just did a select and had it return in text form and found that there are carriage returns in the text:

46.21
, 53.79
, 38875.69140625
, 45256.3046875

So now the question is, how can I get each of these numbers moved to another column? I&#39;ve managed to create 4 other columns as mentioned in some of your replies, but still am miffed as to how to separate this funny data.

Thanks,

jayson




------------
Jayson at 6/7/2002 3:39:12 PM


I have a row in a SQL table that has 4 numerical values, separated by comma. I&#39;d like to take this and make it 4 separate columns. Values are not always the same length, but are always delimited by commas.

Any ideas how I could do this in T-SQL?

[Jayson]

It gets more complicated. I just did a select and had it return in text form and found that there are carriage returns in the text:

46.21
, 53.79
, 38875.69140625
, 45256.3046875

So now the question is, how can I get each of these numbers moved to another column? I&#39;ve managed to create 4 other columns as mentioned in some of your replies, but still am miffed as to how to separate this funny data.

Thanks,

jayson




------------
Jun at 6/10/2002 8:17:27 PM

Hi Bill,

What do you mean &#34;Column4 = RIGHT (TextNum, CHARINDEX (&#39;,&#39;, REVERSE(TextNum) ) -1)&#34; won&#39;t work correctly? The Function &#34;Right (TextNum, ...)&#34; will NOT reverse your number, and the &#39;REVERSE&#39; function used here is just for finding the length of the part of last number.

If I can use a simple function to get my string manipulated, I wouldn&#39;t bother to use a very long user-defined stored procedure.

Have good day!

Jun

------------
Bill at 6/10/2002 10:03:49 AM

I haven&#39;t checked the whole procedure, but this code:

Column4 = RIGHT (TextNum, CHARINDEX (&#39;,&#39;, REVERSE(TextNum) ) -1),

won&#39;t work correctly. The number will be reversed. Do this instead:

Column4 = REVERSE(RIGHT (TextNum, CHARINDEX (&#39;,&#39;, REVERSE(TextNum) ) -1)),


Another alternative in SQL 2000 would be to build a user-defined function that parses a string delimited by commas, spaces, etc. and returns the requested element.
For example, a function such as fnStringParse(&#39;this is a test&#39;,&#39;space&#39;,2) returns &#39;is&#39;. I haven&#39;t built such a function otherwise I&#39;d paste it here, but with it you could:

update YourTable
set NewColumn1 = fnStringParse(TextNum,&#39;comma&#39;,1),
NewColumn2 = fnStringParse(TextNum,&#39;comma&#39;,2),
NewColumn3 = fnStringParse(TextNum,&#39;comma&#39;,3),
NewColumn4 = fnStringParse(TextNum,&#39;comma&#39;,4),
NewColumn5 = fnStringParse(TextNum,&#39;comma&#39;,5)



------------
Jun at 6/8/2002 2:32:25 PM

Hi Jayson! Are you doing a lot of string manipulation?

Here is the way you can try: (this may not be the best way)

-- Create 5 extra columns except your TextNum column:
ALTER TABLE YourTable -- Suppose your table name is YourTable
Add Column1 Varchar (50), Column2 Varchar (50), Column3 Varchar (50), Column4 Varchar (50), Column5 Varchar (50);


-- Divide your TextNum column and move the first and last number out
Update YourTable -- Your data in Column &#39;TextNum&#39;
-- Get the first number into column1
SET Column1 = LEFT (TextNum, CHARINDEX (&#39;,&#39;, TextNum)-1),

-- Get the last number into column4
Column4 = RIGHT (TextNum, CHARINDEX (&#39;,&#39;, REVERSE(TextNum) ) -1),

-- Get the two numbers in the middle into column5
Column5 = SUBSTRING (TextNum, CHARINDEX (&#39;,&#39;, TextNum)+1), LEN(TextNum) -CHARINDEX (&#39;,&#39;, TextNum) );


-- Divide Column5 into Column3 and Column4
Update YourTable
-- Get the first number into column1
SET Column2 = LEFT (column5, CHARINDEX (&#39;,&#39;, Column5)-1),

-- Get the last number into column4
Column3 = RIGHT (Column5, CHARINDEX (&#39;,&#39;, REVERSE(Column5) ) -1);

-- Delete Column5
ALTER TABLE YourTable
DROP Column5;

Hope this is the route you would like to go!

Jun














------------
Jayson at 6/7/2002 3:39:12 PM


I have a row in a SQL table that has 4 numerical values, separated by comma. I&#39;d like to take this and make it 4 separate columns. Values are not always the same length, but are always delimited by commas.

Any ideas how I could do this in T-SQL?

[Bill]

Find out which white space characters are really in your data. There could be carriage return line-feeds or just carriage returns. Use my fnStringToken function posted earlier and call it like this depending on your white space:

update YourTable
set NewColumn1 = dbo.fnStringToken(TextNum,char(13)+&#39;, &#39;,1),
NewColumn2 = dbo.fnStringToken(TextNum,char(13)+&#39;, &#39;,2),
NewColumn3 = dbo.fnStringToken(TextNum,char(13)+&#39;, &#39;,3),
NewColumn4 = dbo.fnStringToken(TextNum,char(13)+&#39;, &#39;,4)


------------
Jayson at 6/11/2002 11:01:14 AM

It gets more complicated. I just did a select and had it return in text form and found that there are carriage returns in the text:

46.21
, 53.79
, 38875.69140625
, 45256.3046875

So now the question is, how can I get each of these numbers moved to another column? I&#39;ve managed to create 4 other columns as mentioned in some of your replies, but still am miffed as to how to separate this funny data.

Thanks,

jayson




------------
Jun at 6/10/2002 8:17:27 PM

Hi Bill,

What do you mean &#34;Column4 = RIGHT (TextNum, CHARINDEX (&#39;,&#39;, REVERSE(TextNum) ) -1)&#34; won&#39;t work correctly? The Function &#34;Right (TextNum, ...)&#34; will NOT reverse your number, and the &#39;REVERSE&#39; function used here is just for finding the length of the part of last number.

If I can use a simple function to get my string manipulated, I wouldn&#39;t bother to use a very long user-defined stored procedure.

Have good day!

Jun

------------
Bill at 6/10/2002 10:03:49 AM

I haven&#39;t checked the whole procedure, but this code:

Column4 = RIGHT (TextNum, CHARINDEX (&#39;,&#39;, REVERSE(TextNum) ) -1),

won&#39;t work correctly. The number will be reversed. Do this instead:

Column4 = REVERSE(RIGHT (TextNum, CHARINDEX (&#39;,&#39;, REVERSE(TextNum) ) -1)),


Another alternative in SQL 2000 would be to build a user-defined function that parses a string delimited by commas, spaces, etc. and returns the requested element.
For example, a function such as fnStringParse(&#39;this is a test&#39;,&#39;space&#39;,2) returns &#39;is&#39;. I haven&#39;t built such a function otherwise I&#39;d paste it here, but with it you could:

update YourTable
set NewColumn1 = fnStringParse(TextNum,&#39;comma&#39;,1),
NewColumn2 = fnStringParse(TextNum,&#39;comma&#39;,2),
NewColumn3 = fnStringParse(TextNum,&#39;comma&#39;,3),
NewColumn4 = fnStringParse(TextNum,&#39;comma&#39;,4),
NewColumn5 = fnStringParse(TextNum,&#39;comma&#39;,5)



------------
Jun at 6/8/2002 2:32:25 PM

Hi Jayson! Are you doing a lot of string manipulation?

Here is the way you can try: (this may not be the best way)

-- Create 5 extra columns except your TextNum column:
ALTER TABLE YourTable -- Suppose your table name is YourTable
Add Column1 Varchar (50), Column2 Varchar (50), Column3 Varchar (50), Column4 Varchar (50), Column5 Varchar (50);


-- Divide your TextNum column and move the first and last number out
Update YourTable -- Your data in Column &#39;TextNum&#39;
-- Get the first number into column1
SET Column1 = LEFT (TextNum, CHARINDEX (&#39;,&#39;, TextNum)-1),

-- Get the last number into column4
Column4 = RIGHT (TextNum, CHARINDEX (&#39;,&#39;, REVERSE(TextNum) ) -1),

-- Get the two numbers in the middle into column5
Column5 = SUBSTRING (TextNum, CHARINDEX (&#39;,&#39;, TextNum)+1), LEN(TextNum) -CHARINDEX (&#39;,&#39;, TextNum) );


-- Divide Column5 into Column3 and Column4
Update YourTable
-- Get the first number into column1
SET Column2 = LEFT (column5, CHARINDEX (&#39;,&#39;, Column5)-1),

-- Get the last number into column4
Column3 = RIGHT (Column5, CHARINDEX (&#39;,&#39;, REVERSE(Column5) ) -1);

-- Delete Column5
ALTER TABLE YourTable
DROP Column5;

Hope this is the route you would like to go!

Jun














------------
Jayson at 6/7/2002 3:39:12 PM


I have a row in a SQL table that has 4 numerical values, separated by comma. I&#39;d like to take this and make it 4 separate columns. Values are not always the same length, but are always delimited by commas.

Any ideas how I could do this in T-SQL?

[Bill]

Jayson -
The one change to make in the fnStringToken because you have a Delimiter of more than one character is to change the line:

set @InputString = right(@InputString,len(@InputString)-@DelimiterPosition))

into:

set @InputString = right(@InputString,len(@InputString)-(@DelimiterPosition+len(@Delimiter)-1))


------------
Bill at 6/11/2002 12:44:19 PM

Find out which white space characters are really in your data. There could be carriage return line-feeds or just carriage returns. Use my fnStringToken function posted earlier and call it like this depending on your white space:

update YourTable
set NewColumn1 = dbo.fnStringToken(TextNum,char(13)+&#39;, &#39;,1),
NewColumn2 = dbo.fnStringToken(TextNum,char(13)+&#39;, &#39;,2),
NewColumn3 = dbo.fnStringToken(TextNum,char(13)+&#39;, &#39;,3),
NewColumn4 = dbo.fnStringToken(TextNum,char(13)+&#39;, &#39;,4)


------------
Jayson at 6/11/2002 11:01:14 AM

It gets more complicated. I just did a select and had it return in text form and found that there are carriage returns in the text:

46.21
, 53.79
, 38875.69140625
, 45256.3046875

So now the question is, how can I get each of these numbers moved to another column? I&#39;ve managed to create 4 other columns as mentioned in some of your replies, but still am miffed as to how to separate this funny data.

Thanks,

jayson




------------
Jun at 6/10/2002 8:17:27 PM

Hi Bill,

What do you mean &#34;Column4 = RIGHT (TextNum, CHARINDEX (&#39;,&#39;, REVERSE(TextNum) ) -1)&#34; won&#39;t work correctly? The Function &#34;Right (TextNum, ...)&#34; will NOT reverse your number, and the &#39;REVERSE&#39; function used here is just for finding the length of the part of last number.

If I can use a simple function to get my string manipulated, I wouldn&#39;t bother to use a very long user-defined stored procedure.

Have good day!

Jun

------------
Bill at 6/10/2002 10:03:49 AM

I haven&#39;t checked the whole procedure, but this code:

Column4 = RIGHT (TextNum, CHARINDEX (&#39;,&#39;, REVERSE(TextNum) ) -1),

won&#39;t work correctly. The number will be reversed. Do this instead:

Column4 = REVERSE(RIGHT (TextNum, CHARINDEX (&#39;,&#39;, REVERSE(TextNum) ) -1)),


Another alternative in SQL 2000 would be to build a user-defined function that parses a string delimited by commas, spaces, etc. and returns the requested element.
For example, a function such as fnStringParse(&#39;this is a test&#39;,&#39;space&#39;,2) returns &#39;is&#39;. I haven&#39;t built such a function otherwise I&#39;d paste it here, but with it you could:

update YourTable
set NewColumn1 = fnStringParse(TextNum,&#39;comma&#39;,1),
NewColumn2 = fnStringParse(TextNum,&#39;comma&#39;,2),
NewColumn3 = fnStringParse(TextNum,&#39;comma&#39;,3),
NewColumn4 = fnStringParse(TextNum,&#39;comma&#39;,4),
NewColumn5 = fnStringParse(TextNum,&#39;comma&#39;,5)



------------
Jun at 6/8/2002 2:32:25 PM

Hi Jayson! Are you doing a lot of string manipulation?

Here is the way you can try: (this may not be the best way)

-- Create 5 extra columns except your TextNum column:
ALTER TABLE YourTable -- Suppose your table name is YourTable
Add Column1 Varchar (50), Column2 Varchar (50), Column3 Varchar (50), Column4 Varchar (50), Column5 Varchar (50);


-- Divide your TextNum column and move the first and last number out
Update YourTable -- Your data in Column &#39;TextNum&#39;
-- Get the first number into column1
SET Column1 = LEFT (TextNum, CHARINDEX (&#39;,&#39;, TextNum)-1),

-- Get the last number into column4
Column4 = RIGHT (TextNum, CHARINDEX (&#39;,&#39;, REVERSE(TextNum) ) -1),

-- Get the two numbers in the middle into column5
Column5 = SUBSTRING (TextNum, CHARINDEX (&#39;,&#39;, TextNum)+1), LEN(TextNum) -CHARINDEX (&#39;,&#39;, TextNum) );


-- Divide Column5 into Column3 and Column4
Update YourTable
-- Get the first number into column1
SET Column2 = LEFT (column5, CHARINDEX (&#39;,&#39;, Column5)-1),

-- Get the last number into column4
Column3 = RIGHT (Column5, CHARINDEX (&#39;,&#39;, REVERSE(Column5) ) -1);

-- Delete Column5
ALTER TABLE YourTable
DROP Column5;

Hope this is the route you would like to go!

Jun














------------
Jayson at 6/7/2002 3:39:12 PM


I have a row in a SQL table that has 4 numerical values, separated by comma. I&#39;d like to take this and make it 4 separate columns. Values are not always the same length, but are always delimited by commas.

Any ideas how I could do this in T-SQL?