Solved

How to update column with domain name extension?

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query for timesheet application 3 23
RAISERROR WITH NOWAIT 2 24
query error in former passthrough query 2 31
SQL Rewrite without the NULLIF 4 25
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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.

679 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