the problem with abstracting out the problem is it hides the actual issue.

i suspect that this would involve splitting ont he # sign, cleaning up any elements that meet the criteria i could not figure out, and then using FOR XML to concatenate them back together again.

search for "DelimitedSplit8K" here on SSC to get teh function and the great article on how to use it.

try this, and see how it turns the values into a column of data? i think that's the key here:

with MyTable(ID,SomeString)

AS

(

SELECT 1,'A#+AB#+BA#+B#' UNION ALL

SELECT 2,'C#+AB#+BC#+B#'

)

select * from MyTable

cross apply dbo.delimitedsplit8k(SomeString,'#')

ID SomeString ItemNumber Item

1 A#+AB#+BA#+B# 1 A

1 A#+AB#+BA#+B# 2 +AB

1 A#+AB#+BA#+B# 3 +BA

1 A#+AB#+BA#+B# 4 +B

1 A#+AB#+BA#+B# 5

2 C#+AB#+BC#+B# 1 C

2 C#+AB#+BC#+B# 2 +AB

2 C#+AB#+BC#+B# 3 +BC

2 C#+AB#+BC#+B# 4 +B

2 C#+AB#+BC#+B# 5