Solved

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

Posted on 2009-07-09
15
625 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
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 INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

739 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