Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

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 '.co.uk')

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.

Thanks
0
drgdrg
Asked:
drgdrg
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
update clients
set extension = REVERSE(LEFT(REVERSE(Domain), CHARINDEX('.', REVERSE(Domain) ) ) )
0
 
drgdrgAuthor Commented:
Wow, you're fast.  Perfect solution.  Thank you !!!
0
 
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

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now