Solved

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

Posted on 2009-07-09
15
619 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

758 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

27 Experts available now in Live!

Get 1:1 Help Now