Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How to update column with domain name extension?

Posted on 2009-07-09
3
259 Views
Last Modified: 2012-05-07
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
Comment
Question by:drgdrg
3 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 24817502
update clients
set extension = REVERSE(LEFT(REVERSE(Domain), CHARINDEX('.', REVERSE(Domain) ) ) )
0
 
LVL 1

Author Closing Comment

by:drgdrg
ID: 31601798
Wow, you're fast.  Perfect solution.  Thank you !!!
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24818173
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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ServiceCenter IR Query Expressions 1 39
How to count the days a record spends in a step 21 48
Merge two rows in SQL 4 25
Re-appearing SQL Server Agent jobs 7 28
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question