July 28, 2005 at 10:08 am
Hi
I have data in a column which I have to parse it into different columns according to delimiters, but I realised
substring function in SQL Server does not allow delimiters in its arguments
column Parse1 Parse2
______________________________________
AB 234/145 234 145
AB123/245 123 245
Since in the above eg I am not always sure if delimiter '/' always exists position 6 or 5, I am able to use the
substring function to parse the column and populate 243 and 145 into 2 different columns.
Any suggestions/inputs would be helpful
Thanks
July 28, 2005 at 10:28 am
Substring does allow an expression,
Use charindex nested inside the substring function.
if you have more than 1 delimiter you would want 1 statement for each distinct delimiter
-- Create test table
create table test(pk int identity, Value1 varchar(50))
-- Create second test table to insert parsed values into
create table test2(pk int identity, Value1 varchar(50), Value2 varchar(50))
-- Insert test values
insert into Test(Value1)
select 'AB 234/145 234 145'
union all
select 'AB123/245 123 245'
union all
select 'AB123-245 123 245'
union all
select 'AB 234-145 234 145'
-- Insert into test table
Insert into Test2(Value1, Value2)
-- Parses all rows with '/' as delimiter
select substring(Value1,1,Charindex('/',Value1)-1) as Value1,
substring(Value1,Charindex('/',Value1)+ 1,50) as Value2
from Test
where Charindex('/',Value1) > 0
union all
-- Parses all rows with '-' as delimiter
select substring(Value1,1,Charindex('-',Value1)-1) as Value1,
substring(Value1,Charindex('-',Value1)+ 1,50) as Value2
from Test
where Charindex('-',Value1) > 0
select * from Test2
-- Results
1 AB 234 145 234 145
2 AB123 245 123 245
3 AB123 245 123 245
4 AB 234 145 234 145
Drop table Test
Drop table Test2
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply