Hi

Without some example data this is just a guess at how you could do. The following example depends on the ids being 9 characters long with an arbitrary delimiter.

Hope this helps

with sampledata as (

-- Guess at how the data may look

SELECT *

FROM (VALUES

('aaaaa-aaa','one')

,('bbbbbbbbb.ccccccccc','two')

,('ddddddddd-eeeee.eee-fffffffff','three')

,('ggggggggg.hhhhhhhhh-iiiiiiiii.jjjjjjjjj','four')

,('kkkk-kkkk-lllllllll.mmmmmmmmm.nnnnnnnnn-ooooooooo','five')

,('ppppppppp.qqqqqqqqq|rrrrrrrrr|sssssssss,ttttttttt;uuuuuuuuu','six')

,('vvvvvvvvv.wwwwwwwww.xxxxxxxxx.yyyyyyyyy.zzzzzzzzz.000000000.111111111','seven')

) AS SD(ID,VALUE)

)

-- Little tally table to split the id with

,smallTally as (

SELECT * FROM (VALUES (0), (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS T(N)

)

-- Unpivot the id column

,unpivotID as (

SELECT SUBSTRING(s.ID,(t.N * 10) + 1,9) ID -- Get exactly 9 characters every 10th position

, s.VALUE, N

FROM sampledata s

cross apply (

SELECT TOP ((len(s.id) / 10) + 1) -- Determine the number of IDs to unpivot

N

FROM smallTally

ORDER BY N) t

)

-- Replace unnecessary prefix modifer in character location 6

SELECT CASE WHEN CHARINDEX('-',REPLACE(ID,'.','-')) = 6 THEN STUFF(ID,6,1,'') ELSE ID END ID

, VALUE

FROM unpivotID