function for delimiters

• am-244616

  SSCarpal Tunnel

  Points: 4936

  More actions

  April 19, 2007 at 1:46 pm

  #119827

  Guys,

  I have created function where I pass delimiter string and it gets me the charecters after the delimiter.

  I have address field which I need to split to city and state.

  I have the following examples

  VA Beach, VA 23542 to VA Beach and VA

  VIRGINIA BEACH, VA to VIRGINIA BEACH and VA

  -- @src source string

  -- @dlm delimiter

  -- @ind token's number, i.e. 1st, 2nd token, ...

  CREATE function getToken(@src varchar(2000), @dlm varchar(2000), @ind int)

  returns varchar(2000)

  as

  begin

  declare @pos int,

  @posn int,

  @cnt int,

  @token varchar(2000)

  set @pos=1

  set @cnt=1

  set @src=@src+@dlm

  while @cnt<=@ind
  begin
  set @posn=charindex(@dlm,@src, @pos)
  if @posn>0

  select @token=substring(@src, @pos, @posn-@pos)

  else

  return null

  set @pos=@posn+len(@dlm+'1')-1

  set @cnt=@cnt+1

  end

  if @@error<>0 or len(@token)=0

  return null

  return @token

  end

  SELECT DBO.GETTOKEN('VA Beach, VA 23542', ',', 1)

  I need result VA instead of VA 23542

  Any suggestions/inputs would help.

  Thanks

• Anders Pedersen

  SSChampion

  Points: 11410

  More actions

  April 19, 2007 at 2:21 pm

  #701578

  Is it ok to cheat?   How well defined is the data?  If just like you said there, the following would work...

   

  declare

  @r varchar(30)

  select

  @r = dbo.GetToken ('VA Beach, VA 23542', ',',2)

  select

  @r = dbo.GetToken (@r, ' ',2)

  select

  @r

   

• John Rowan

  SSC Guru

  Points: 56440

  More actions

  April 19, 2007 at 5:04 pm

  #701601

  declare @r varchar(30)

  select @r = dbo.GetToken (dbo.GetToken ('VA Beach, VA 23542', ',',2), ' ',2)

  select @r

  John Rowan

  ======================================================
  ======================================================
  Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden