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:38 am

    #150339

    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

  • June 4, 2015 at 4:59 am

    #1802939

    Far from elegant, but this may work

    SELECT RTRIM(CASE WHEN CHARINDEX(',',Address) < CHARINDEX('*',Address)

    THEN SUBSTRING(Address,0, CHARINDEX(',',Address))

    WHEN CHARINDEX(',',Address) > CHARINDEX('*',Address)

    THEN SUBSTRING(Address,0, CHARINDEX('*',Address))

    ELSE Address

    END)

    LinkedIn

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

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