November 27, 2009 at 5:10 am
Could someone please help?
I have the following data:
create table #tb (first_name varchar(255), Last_name varchar (255), email_address varchar (255))
Insert into #tb (first_name, Last_name, email_address)
Select 'Paul','Berry','p.berry@the_end.com'
union all select 'John','Jackson','jj@hotmale.com_QWER20090101'
union all select 'Simon','McKenzie','macca@weyhey.co.uk'
union all select 'Wendy','Sinclair','Wendy.sinclair@nobodyaround.com_QWER20070914'
union all select 'Neil','McCann','neil@homealone.ie_QWER20080718'
union all select 'Steve','Arnold','arnie@stevearnold.co.uk_QWER20091111'
union all select 'Graham','Beavis','g_beavis112@freeinternetbaby.com'
union all select 'Emma','Micklewright','ems@fresco.net'
union all select 'Tracey','McFee','neil@homealone.ie'
union all select 'Judi','Kristoffersen','neil@home_alone.ie_QWER20080718'
select * from #tb
I would like to remove everything after the underscore on the invalid email addresses. In each case, the invalid part begins with "_QWER", but they are not all valid.
I am not having much success using the following:
SELECT (
IF (CHARINDEX( '_QWER, Email_Address) >0)
select left(Email_Address, ((SELECT CHARINDEX( '_QWER', Email_Address)-1)))
IF (CHARINDEX( '_QWER', Email_Address) <1)
select Email_Address
November 27, 2009 at 5:37 am
Try this:
select email_address = case
when charindex('_QWER', email_address) > 0 then substring(email_address, 1, charindex('_QWER', email_address) - 1)
else email_address
end
from #tb
November 27, 2009 at 6:58 am
Brilliant. Works a treat thanks.
I will rememebr to use CASE rather than IF where possible. I must admit that I have not used CASE very much.
Thanks again!:-)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply