What I am trying to do is feed in a small string and it will filter some values out for me. This might be easier to do in visual studio than in a TSQL function. Here are some notes on my findings.

I am simply feeding in “ABCD” and it will match the 2 letters AB and CD and filter out the corresponding values. So if I feed in ABCD from _ABWXYXCD_ITEM1_ITEM2_ITEM3_ITEM4_ then I expect the output _ABCD_ITEM1_ITEM4_

Simple right? Phhh! Not quite in TSQL. Super easy in c#, but I have never made a dll before.

Here is a start to my TSQL that would be part of a function. I thought to myself – lets stop while we are ahead…

— http://stackoverflow.com/questions/659051/check-if-temp-table-exist-and-delete-if-it-exists-before-creating-a-temp-table
— http://stackoverflow.com/questions/8726111/sql-server-find-nth-occurrence-in-a-string

[sql]

IF OBJECT_ID(‘tempdb..#T’) IS NOT NULL
DROP TABLE #T

select ‘abc_1_2_3_4.gif ‘ as img, 0 As id into #T

insert INTO #T values (‘zzz_12_3_3_45.gif’,0)
select * from #T
;with T as (
select 0 as row, charindex(‘_’, img) pos, img, id from #T
union all
select pos + 1, charindex(‘_’, img, pos + 1), img, id + 1
from T
where pos > 0
)
select
img, pos, id
from T
where pos > 0
AND id = 2

order by img, pos

[/sql]