Hi, I suggest you create a split function
CREATE FUNCTION [dbo].[Split]
(
@delimited nvarchar(max),
@delimiter nvarchar(100))
RETURNS @t TABLE
(
id int identity(1,1),
val nvarchar(max))AS
BEGIN
declare @xml xml
set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'
insert into @t(val)
select
r.value('.','varchar(15)') as item
from @xml.nodes('//root/r') as records(r)
RETURN
END
Then cross apply to return your desired column. eg.
declare @tmp table (value varchar(100))
insert into @tmp values ('ASD654-100-CCCEEE4444-200-')
insert into @tmp values ('QWEQWEQ-2000-JJJLLL-20-PPPBBMMNN-600-')
select val
from @tmp
cross apply dbo.split(value,'-')
where id = 3
Ryan
