Complex T-SQL Substring

Question

Just to be clear, I cannot use CLR UDF for this and SUBSTRING and CHARINDEX just don't cut the mustard.

We have a faux account management system with accounts being sub-accounts of others, better described here (with tables too :) )

Now, assuming I have an account 2.4.1.3 (obviously, the parent becomes 2.4.1) and if wanted to extract the 'prefix' 2.4.1 so that I may create another sibling account with the next ID in line (assume 2.4.1.4) how would I go about splitting such a string in T-SQL?

Of course, a similar way can be applied to children accounts, but that's just butterscotch for this sundae.

Answer 1

Try something like this:

DECLARE @accountno VARCHAR(50) = '2.4.1.3'

SELECT 
   REVERSE(@accountno),
   CHARINDEX('.', REVERSE(@accountno)),
   SUBSTRING(@accountno, 1, LEN(@accountno) - CHARINDEX('.', REVERSE(@accountno)))

That third element in the SELECT statement should be the one that extracts the "prefix" 2.4.1 from your account number string.

Basically, what I do is reverse the string and then look for the first occurence of the dot ('.') - the first in the reversed string is the last in the original string, and that's what you want to extract up to.