How to update column with domain name extension?

I have a table with the clients' domain names.

I need to add a column that has the domain name extension (ie. '.com', '.uk', etc.)

I only need the last piece after the last decimal (ie.  '.uk'  ... I don't need '')

I already have a column with the full domain name.  How would I build a query with string functions to make this update?

I'm trying to avoid a series of queries, so that this one query will be reusable in the future as new records are added.

Who is Participating?
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
update clients
set extension = REVERSE(LEFT(REVERSE(Domain), CHARINDEX('.', REVERSE(Domain) ) ) )
drgdrgAuthor Commented:
Wow, you're fast.  Perfect solution.  Thank you !!!
Kevin CrossChief Technology OfficerCommented:
Here is another version if curious:
+Add minus one (-1) after CHARINDEX piece to remove the '.' so you get 'uk' instead of '.uk' if desired.
update clients
set extension = RIGHT(Domain, CHARINDEX('.', REVERSE(Domain)))

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.