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

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.
paddymcAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
insert into dbContact
select 'http://www.'+ LEFT (Email, CHARINDEX('@',Email)-1 )
from dbcontactEmails
0
ralmadaCommented:
Try this:
insert dbContact 
select  'http://www.'+ right(email, charindex('@', reverse(email)) - 1) 
from dbcontactEmails

Open in new window

0
HadushCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

paddymcAuthor Commented:
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
Aneesh RetnakaranDatabase AdministratorCommented:
insert into dbContact
select 'http://www.'+ RIGHT (Email, LEN(Email)-CHARINDEX('@',Email) )
from dbcontactEmails
0
ralmadaCommented:
Have you tried mine? See comment 24814806
0
ralmadaCommented:
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
paddymcAuthor Commented:
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
ralmadaCommented:
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
ralmadaCommented:
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
paddymcAuthor Commented:
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
Aneesh RetnakaranDatabase AdministratorCommented:
Did you try my last post
0
ralmadaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
paddymcAuthor Commented:
Worked perfectly - Ralmada
0
paddymcAuthor Commented:
Thanks for all your help Aneesh - helped us out a lot
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.