Post reply

String Manipulation

  • November 27, 2009 at 5:10 am

    #218049

    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

    #1084870

    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

    Karl
    source control for SQL Server

  • November 27, 2009 at 6:58 am

    #1084900

    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