All posts by David Hall

About David Hall

David Hall is Director of Software Development at Webnames.ca Inc. (www.webnames.ca), Canada’s original .CA Registrar and one of the country’s leading webhosting and Internet solutions companies. David has had a wide variety of experiences in different industries, originally in State/Provincial Lottery Systems (GTECH/Riptide Technologies), to Open Pit Mine Fleet Management Systems (Wenco International) to Internet Technologies with Webnames.ca. David is an active member in many .NET and SQL Server groups in the lower mainland as well as for writing articles for the Database.tips and Webnames.ca blog.

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.