'RN-CITC050021-5223-B00-000'

SELECT 'RN'    AS [Pos1]
, 'CITC050021' AS [Pos2]
, '5223'       AS [Pos3]
, 'B00'        AS [Pos4]
, '000'        AS [Pos5]

Pos1 Pos2       Pos3 Pos4 Pos5
---- ---------- ---- ---- ----
RN   CITC050021 5223 B00  000

SELECT 'RN'    AS [Pos1]
, 'CITC050021' AS [Pos2]
, '5223'       AS [Pos3]
, 'B00'        AS [Pos4]
, '000'        AS [Pos5]

;with yourTable (s) as 
(
       select 'RN-CITC050021-5223-B00-000' union all
       select 'Z-AB1-123-A0-23' union all
       select 'ZZZ-F-AB-K-69' union all
       select 'not valid' union all
       select 'not-valid'
)

,c2 (s,p,i) as 
(--last value
       select s
              ,right(s, charindex('-', reverse(s))-1)
              ,len(s)
       from   yourTable
       --make sure we have a 5 part string
       where  len(s) - len(replace(s, '-','')) = 4
)
,c3 (s,p,i) as
(--first through fourth values
       select s
              ,substring(s, 1, charindex('-', s)-1)
              ,charindex('-', s)
       from   c2
       where  charindex('-', s) > 0
       union all
       select s
              ,substring(s, i+1, charindex('-', s, i+1)-(i+1))
              ,charindex('-', s, i+1)
       from   c3
       where  charindex('-', s, i+1) > 0
)
,c4 (s,p,i) as
(--combine the last value with the first four
       select * from c3 
       union all 
       select * from c2
)
--Pivot the vaues into columns
select s
       ,left([1], 15) as pos1
       ,left([2], 15) as pos2
       ,left([3], 15) as pos3
       ,left([4], 15) as pos4
       ,left([5], 15) as pos4
from   (--name the values that will become columns
       select s
              ,pos = row_number() over (partition by s order by i)
              ,p 
       from c4 
       ) d
pivot (
       max(p)
       for [pos] in ([1],[2],[3],[4],[5])
       ) p

OUTPUT:

s                          pos1            pos2            pos3            pos4            pos4
-------------------------- --------------- --------------- --------------- --------------- ---------------
RN-CITC050021-5223-B00-000 RN              CITC050021      5223            B00             000
Z-AB1-123-A0-23            Z               AB1             123             A0              23
ZZZ-F-AB-K-69              ZZZ             F               AB              K               69

quote:

---

Originally posted by Bex

Hi there

Sorry for not responding, I had gone home (on UK time). Anyway, the reason I don't have the freedom to use variables, etc is because of the application that will be calling the sql. It is very limited. Anyway, all your assumptions were spot on, so thank you for taking the time to give me a solution.

Much appreciated!

---