Extracting from a table

• nneka echebe

  SSC Enthusiast

  Points: 106

  More actions

  October 2, 2008 at 8:19 am

  #193944

  Dear all, I have a table which has a column that contains data like '

  '654:JT12;678:JT56;'

  It is sometimes more or less but it is always in the pattern shown

  I am trying to extract the item between the ';' and the ':'

  (In other words, I am trying to extract 'JT12' and 'JT56' and store it in another table).

  Can anyone be kind enough to help me out? I tried Patindex and substring but I can't seem to be able to construct a usable function that will strip out what I need

  Any help will be highly appreciated

  Nneka

• RyanRandall

  SSChampion

  Points: 13623

  More actions

  October 2, 2008 at 10:47 am

  #880084

  Here are a few possibilities...

  declare @t table (id int, v varchar(50))

  insert @t

  select 1, '654:JT12;678:JT56;'

  union all select 2, '6549:JT129;6789:JT569;453:JT435;'

  select *,

  x.value('(root/a)[1]', 'varchar(9)') as v1,

  x.value('(root/a)[2]', 'varchar(9)') as v2,

  x.value('(root/a)[3]', 'varchar(9)') as v3

  from (

  select *, cast(' ' as xml) as x from @t) a

  ; with

  a as (select *, cast(' ' as xml) as x from @t)

  SELECT a.*, t.c.value('.', 'varchar(max)') x

  FROM a CROSS APPLY x.nodes('//a') AS t(c)

  Ryan Randall

  Solutions are easy. Understanding the problem, now, that's the hard part.