Solved

How to update column with domain name extension?

Posted on 2009-07-09
3
256 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

911 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now