question

Your solution is, as I think you have found, quite complex to scale. I'm going to assume that you may (in future) have 5 or more periods. I would use a CTE to split this out. So I would use a CTE like this: ;WITH CTE (main -- This is the part of the string which has been isolated. ,part, -- This is the order which the parts appeared in the string rest --This will store the "remainder" - i.e. the bit of the string @a left over ) AS ( SELECT LEFT(@a,CHARINDEX('.',@a,0)-1) /* Everything before we find a period */,1 /* Part number */,RIGHT(@a,LEN(@a)-CHARINDEX('.',@a,1)) /* What's left */ --This line picks out the first combination, and the remainder UNION ALL --The next bit will take the output, and cause it to run the same logic on each "remainder" until we run out of remainders SELECT CASE WHEN CHARINDEX('.',rest) = 0 THEN rest ELSE LEFT(rest,CHARINDEX('.',rest)-1) end,part+1, CASE WHEN CHARINDEX('.',rest)=0 THEN '' ELSE RIGHT(rest,LEN(rest)-CHARINDEX('.',rest,1)) end FROM CTE WHERE rest != '' ) This produces a table CTE which can be either queried to display our parts as rows: SELECT main,part FROM CTE --<<-- This gives you a simple breakdown in row format - pick the part you want or alternatively you can use the PIVOT operator to give you something very similar to what you had before: SELECT @a AS nominal_account, MAX([1]) AS account, MAX([2]) AS ele2, MAX([3]) AS ele3, MAX([4]) AS ele4 FROM CTE PIVOT ( max(main) FOR part IN ([0], [1], [2], [3], [4]) ) AS PivotTable; You want to put the CTE section with whichever output style suits your need best. The advantage of this approach is that it scales, but also that it can be easily extended to draw from a table if required. Whilst there is a CTE limit (default 100 recursions, so 100 "parts" to your string), your question implies that you will not reach this any time soon. I hope that helps.

Using the data for p.nominal_account 6201.D310.Z914 5405.L40.S300.XL SELECT p.nominal_account [nominal_account] ,REVERSE(PARSENAME(REVERSE(p.nominal_account),1))[account] ,REVERSE(PARSENAME(REVERSE(p.nominal_account),2))[ele1] ,REVERSE(PARSENAME(REVERSE(p.nominal_account),3))[ele2] ,case when REVERSE(PARSENAME(REVERSE(p.nominal_account),4)) is null then '' else REVERSE(PARSENAME(REVERSE(p.nominal_account),4)) end [ele3] Adding the case clause the results are as follows: nominal_account account ele1 ele2 ele3 6201.D310.Z914 6201 D310 Z914 5405.L40.S300.XL 5405 L40 S300 XL