T-SQL – Find the last occurrence of a sub-string

Working in the domain name business, I frequently have to find the last occurrence of a sub-string in T-SQL in order to properly parse out the domain extension. For example, if I have the domain name ‘subdomain1.subdomain2.bc.ca’ and I want to know whether the domain is a .com or .ca or other domain extension, the fastest T-SQL command I have found that works is:

SELECT REVERSE(SUBSTRING(REVERSE('subdomain1.subdomain2.bc.ca'),
CHARINDEX('.',REVERSE('subdomain1.subdomain2.bc.ca')),
len('subdomain1.subdomain2.bc.ca'))) AS "First Part",
REVERSE(SUBSTRING(REVERSE('subdomain1.subdomain2.bc.ca'),1,
CHARINDEX('.',REVERSE('subdomain1.subdomain2.bc.ca')))) AS "Second Part"

The result of this TSQL query, is two values:

FirstPart

SecondPart

subdomain1.subdomain2.bc.

.ca

This T-SQL works well to find the last occurrence of a sub-string within a large string of text that is separated by a character like a period, comma or other character.

Leave a Reply