Variable length substring with charindex

• cgomez

  SSC Journeyman

  Points: 95

  More actions

  May 21, 2008 at 3:03 pm

  #207575

  Hello all, any assistance with this challenge would be greatly appreciated.

  I have a field in one of my tables that i need to extract a piece of data from. The challenge is that i have multiple characters that are the same and the text i need to extract can vary in lenght. What i do know is that my start position is AFTER the 4th "|" and my end position is before the 3rd to last "|". Sample data below:

  MyField

  I|LB|||TEXT1|0||

  I|LB|||TEXT12|0||

  I|LB|||TEXT123|0||

  I|LB|||TEXT2|0||

  I|LB|||TEXT23|0||

  I think i can achieve with substring/charindex.

  Thank you in advanced.

• antonio.collins

  SSCrazy Eights

  Points: 9600

  More actions

  May 21, 2008 at 7:00 pm

  #818938

  here's a function that will help:

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  ALTER function [dbo].[fListToVarchars]( @list varchar(max), @delim varchar(6) )

  returns @returnTable table

  ( item varchar(255) not null, itemSequence smallint not null )

  as begin

  declare @xml XML

  set @xml = char(60)+'item>' + REPLACE(REPLACE(@list,char(60),'<'),@delim,char(60)+'/item>'+char(60)+'item>') + char(60)+'/item>'

  insert into @returnTable

  SELECT ltrim(data.item.value('.','varchar(255)')), row_number() over (order by getdate())

  FROM @xml.nodes('//item') as data(item)

  return

  end

  ;

  assuming 'my start position is AFTER the 4th "|" and my end position is before the 3rd to last "|"' means you want the 5th pipe delimited token, here's an example that should do what you want:

  select cast('I|LB|||TEXT1|0||' as varchar(40)) as MyField into #x

  insert into #x

  select 'I|LB|||TEXT12|0||' union

  select 'I|LB|||TEXT123|0||' union

  select 'I|LB|||TEXT2|0||' union

  select 'I|LB|||TEXT23|0||'

  select #x.MyField, F.*

  from #x cross apply dbo.fListToVarchars(#x.MyField,'|') as F

  where F.itemSequence = 5