value extraction

• znkin

  Old Hand

  Points: 382

  More actions

  February 24, 2010 at 11:33 pm

  #220891

  I have a varchar value as

  C-P-M-T-U-R

  how can I find out the value between the first two hypens(-) that is in above example it would be P

• Kingston Dhasian

  SSCoach

  Points: 19799

  More actions

  February 25, 2010 at 12:41 am

  #1124776

  Try the following..

  DECLARE@strString VARCHAR(100)

  DECLARE@strDelimiter VARCHAR(1)

  DECLARE@iPosition1 INT

  DECLARE@iPosition2 INT

  SELECT@strString = 'C-P-M-T-U-R',

  @strDelimiter = '-',

  @iPosition1 = CHARINDEX( @strDelimiter, @strString ),

  @iPosition2 = CHARINDEX( @strDelimiter, @strString, iPosition1 + 1 )

  SELECT SUBSTRING( @strString, @iPosition1 + 1, @iPosition2 - @iPosition1 - 1)

  ---

  Kingston Dhasian

  How to post data/code on a forum to get the best help - Jeff Moden
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• Divya Agrawal

  Hall of Fame

  Points: 3144

  More actions

  February 25, 2010 at 2:49 am

  #1124810

  declare @s-2 as varchar(100),@sxml as xml,@no as int

  set @s-2 ='C-P-M-T-U-R'

  set @sxml = cast('<X>'+replace(@s,'-','</X><X>')+'</X>' as xml)

  set @no =2

  select v

  from

  (

  select d.v.value('.','varchar(50)') as v,Row_number() OVER (order by (select 1)) as rnum

  from @sxml.nodes('X') d(v)

  )t where rnum = @no

  pass the value @no as the order of hyphen u want to fetch

  --Divya