SQLTeam.com | Weblogs | Forums

Get the last number of a string ("1.3.4", "1.3.2.1")


#1

I have the column "structure" with string values like: (and that feed a .net treeview)
1.3.2
1.2.4.5
1.11
3
I need to get the upcoming number, remaining on the same level (1.3.3, 1.2.3.6, 1.12, 4)
The expression I found is really complicated for something that looks quite simple.

CAST(CASE WHEN CHARINDEX('.', structure) > 0 THEN substring(structure, len(structure) - CHARINDEX ( '.' ,REVERSE(structure))+2, CHARINDEX ( '.' ,REVERSE(structure))-1) ELSE structure END as int) +1

... and this is only the last number; now I should get the first part with the same strategy; so I thought somebody might have a smarter approach


#2

If there are never more than 4 levels, you can use PARSENAME(). For more than that, you can use a good splitter like DelimitedSplit8K. For just the right-most level, you could also do this:

SELECT RIGHT('.' + structure, charindex('.', reverse('.' + structure)) - 1)
FROM (
    select '1.3.2' as structure UNION ALL
    select '1.2.4.5' UNION ALL
    select '1.11' UNION ALL
    select '3'
    ) as test_data

#3

If the maximum number of split is 4, you can use

SELECT parsename(structure,1)
FROM (
select '1.3.2' as structure UNION ALL
select '1.2.4.5' UNION ALL
select '1.11' UNION ALL
select '3'
) as test_data