May 21, 2008 at 3:03 pm
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.
May 21, 2008 at 7:00 pm
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply