Solved

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

Posted on 2009-07-09
15
622 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
backup and restore 21 30
SQL Server / Update DB? 22 38
SQL Group By Question 4 20
SQL 2012 clustering 9 13
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

828 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