Solved

Remove and append characters before a specific character in a string in sql

Posted on 2009-07-09
15
620 Views
Last Modified: 2012-08-14
HI
I have 2 tables:
1.  'dbcontact' with column name = 'contwebsite' containing urls
2.  'dbcontactemails' with column name = 'contemail' containing email addresses

I would like to strip out all characters preceeding and including the '@' symbol in the 'contemail' column, append 'www.' to this value and write or update the new value to the 'contwebsite' column.

So column value 'john@ici.com' will write 'www.ici.com' to the dbcontact.contwebsite column

Any help very much appreciated.
0
Comment
Question by:paddymc
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24814676
insert into dbContact
select 'http://www.'+ LEFT (Email, CHARINDEX('@',Email)-1 )
from dbcontactEmails
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24814806
Try this:
insert dbContact 

select  'http://www.'+ right(email, charindex('@', reverse(email)) - 1) 

from dbcontactEmails

Open in new window

0
 
LVL 8

Expert Comment

by:Hadush
ID: 24814856
You can use CHARINDEX function where it finds the @ symbol and get all to the right of that and concatnate with WWW.

 DECLARE @STRING_TO_SEARCH_WITHIN VarChar(25)

 SET @STRING_TO_SEARCH_WITHIN= 'john@ici.com' 

 SELECT 

 'www.' + Right(@STRING_TO_SEARCH_WITHIN,CHARINDEX('@',@STRING_TO_SEARCH_WITHIN)+2)

Open in new window

0
 

Author Comment

by:paddymc
ID: 24815523
Hi

The above solutions seem to return everything before the @ symbol giving me an example output of 'http://www.john.smith' rather than everything after the @ symbol to give me 'http://www.bbc.com' for example.

Please note that I am attempting to update around 4000 records  - the 'john@ici.com' was just an example.

Please also note that I am wanting to update the existing 'dbcontact.contwebsite' column and not INSERT a new column.

Many thanks
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24815629
insert into dbContact
select 'http://www.'+ RIGHT (Email, LEN(Email)-CHARINDEX('@',Email) )
from dbcontactEmails
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24816143
Have you tried mine? See comment 24814806
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24816273
If you want to update, then use this: (just replace id with the column that links both tables)
update dbcontact

set dbcontact.contwebsite = 'http://www.'+ right(dbcontactemail.contemail, charindex('@', reverse(dbcontactemail.contemail)) - 1) 

from dbcontact inner join dbcontactemail on dbcontact.id = dbcontactemail.id

Open in new window

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:paddymc
ID: 24816561
Thanks Ralmada.  We have just run this and realsied that we have a many to one relationship.  So in some cases we have multiple records in the dbcontactemails table to a single record in the dbcontact table.   This gives us (see snippet).  Is there any way we can modify this apply the first ID linked record (eg Top N) from the dbcontactemails table?  Perhaps a sub-query?    (have upped point value)
Msg 536, Level 16, State 3, Line 2

Invalid length parameter passed to the RIGHT function.

The statement has been terminated.

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 24816670
On approach could be this:
update dbcontact

set dbcontact.contwebsite = a.newdomain

from dbcontact

inner join (select distinct id, 'http://www.'+ right(a.contemail, charindex('@', reverse(a.contemail)) - 1) as newdomain

from dbcontactemail) a on dbcontact.id = a.id

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 24816709
Sorry like this:
update dbcontact

set dbcontact.contwebsite = a.newdomain

from dbcontact

inner join (select distinct id, 'http://www.'+ right(contemail, charindex('@', reverse(contemail)) - 1) as newdomain

from dbcontactemail) a on dbcontact.id = a.id

Open in new window

0
 

Author Comment

by:paddymc
ID: 24817736
Hi

Ran:

update dbcontact
set dbcontact.contwebsite = a.newdomain
from dbcontact
inner join (select distinct contid, 'http://www.'+ right(contemail, charindex('@', reverse(contemail)) - 1) as newdomain
from dbcontactemails) a on dbcontact.contid = a.contid

Gives me:

'Msg 536, Level 16, State 3, Line 4
Invalid length parameter passed to the RIGHT function.
The statement has been terminated.'

Think we are getting close - your help much appreciated
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24817815
Did you try my last post
0
 
LVL 41

Accepted Solution

by:
ralmada earned 200 total points
ID: 24817935
using aneesh approach:

update dbcontact

set dbcontact.contwebsite = a.newdomain

from dbcontact

inner join (select distinct contid, 'http://www.'+ right(contemail,  LEN(contEmail)-CHARINDEX('@',contEmail)) as newdomain

from dbcontactemails) a on dbcontact.contid = a.contid

Open in new window

0
 

Author Comment

by:paddymc
ID: 24818708
Worked perfectly - Ralmada
0
 

Author Closing Comment

by:paddymc
ID: 31601653
Thanks for all your help Aneesh - helped us out a lot
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

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

19 Experts available now in Live!

Get 1:1 Help Now