Post reply

CharIndex Manipulation (SQL 2012). Select Case Statement to remove Part of String after one or two specific characters

  • June 3, 2015 at 4:50 am

    #317894

    Hi All,

    I have an Address column that I need to Substring. I want to remove part of the string after either, or both of the following characters i.e ',' OR '*'

    Example Record 1. Elland **REQUIRES BOOKING IN***

    Example Record 2. Theale, Nr Reading, Berkshire

    Example Record 3. Stockport

    How do I achieve this in a CASE Statement?

    The following two case statements return the correct results, but I some how need to combine them into a single Statement?

    ,LEFT(Address ,CASE WHEN CHARINDEX(',',Address) =0

    THEN LEN(Address )

    ELSE CHARINDEX(',' ,Address ) -1 END) AS 'Town Test'

    ,LEFT(Address ,CASE WHEN CHARINDEX('*',Address ) =0

    THEN LEN(Address)

    ELSE CHARINDEX('*' ,Address ) -1 END) AS 'Town Test2'

    Thanks in advance

  • June 3, 2015 at 5:01 am

    #1802546

    Duplicate post. Replies here please: http://qa.sqlservercentral.com/Forums/FindPost1691082.aspx

    -- Gianluca Sartori

  • June 3, 2015 at 5:32 am

    #1802553

    Pack_Star (6/3/2015)

    Hi All,

    I have an Address column that I need to Substring. I want to remove part of the string after either, or both of the following characters i.e ',' OR '*'

    Example Record 1. Elland **REQUIRES BOOKING IN***

    Example Record 2. Theale, Nr Reading, Berkshire

    Example Record 3. Stockport

    How do I achieve this in a CASE Statement?

    The following two case statements return the correct results, but I some how need to combine them into a single Statement?

    ,LEFT(Address ,CASE WHEN CHARINDEX(',',Town) =0

    THEN LEN(Address )

    ELSE CHARINDEX(',' ,Address ) -1 END) AS 'Town Test'

    ,LEFT(Address ,CASE WHEN CHARINDEX('*',Address ) =0

    THEN LEN(Address)

    ELSE CHARINDEX('*' ,Address ) -1 END) AS 'Town Test2'

    Thanks in advance

    What is the difference between 'Town' and 'Address' and which is the sample data referring to?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • June 3, 2015 at 5:35 am

    #1802557

    Hi,

    Sorry typo on my part. Address/Town are the same column.

    The examples are sample records found in the Address Column.

    Thanks

  • June 3, 2015 at 6:02 am

    #1802573

    OK. Here is a hack for you.

    with addresses

    as (select address = 'Elland **REQUIRES BOOKING IN***'

    union all

    select 'Theale, Nr Reading, Berkshire'

    union all

    select 'Stockport'

    )

    select *

    ,left(address, case when charindex(',', address) = 0 then len(address)

    else charindex(',', address) - 1

    end) as 'Town Test'

    ,left(address, case when charindex('*', address) = 0 then len(address)

    else charindex('*', address) - 1

    end) as 'Town Test2'

    ,left(address, case when charindex(',', replace(address,'*',',')) = 0 then len(address)

    else charindex(',', replace(address,'*',',')) - 1

    end) as 'Town Test3'

    from addresses;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • June 3, 2015 at 6:03 am

    #1802574

    spaghettidba (6/3/2015)

    Duplicate post. Replies here please: http://qa.sqlservercentral.com/Forums/FindPost1691082.aspx

    So sorry. I missed this.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • June 3, 2015 at 6:56 am

    #1802604

    Hi Phil,

    Absolutely perfect!!!

    I have been struggling with this for a couple of days.

    Many Thanks

  • June 3, 2015 at 7:08 am

    #1802607

    Pack_Star (6/3/2015)

    Hi Phil,

    Absolutely perfect!!!

    I have been struggling with this for a couple of days.

    Many Thanks

    Nigel

    That's too long! Post here straight after day 1 in future 🙂

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • June 3, 2015 at 3:19 pm

    #1802820

    If you just need whichever comes first, I suggest:

    with addresses

    as (select address = 'Elland **REQUIRES BOOKING IN***'

    union all

    select 'Theale, Nr Reading, Berkshire'

    union all

    select 'Stockport'

    )

    select address

    ,left(address, PATINDEX('%[,*]%', address + ',') - 1) as address_trimmed

    from addresses;

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply