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

  • 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

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

    -- Gianluca Sartori

  • 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?


  • Hi,

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

    The examples are sample records found in the Address Column.

    Thanks

  • 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;


  • spaghettidba (6/3/2015)


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

    So sorry. I missed this.


  • Hi Phil,

    Absolutely perfect!!!

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

    Many Thanks

  • 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 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

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