Substring Parse

• am-244616

  SSCarpal Tunnel

  Points: 4936

  More actions

  July 28, 2005 at 10:08 am

  #164375

  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

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  July 28, 2005 at 10:28 am

  #577959

  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