Solved

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

Posted on 2009-07-09
15
626 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
[X]
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
  • 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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
 

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

691 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