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

• Pack_Star

  Mr or Mrs. 500

  Points: 547

  More actions

  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

• tindog

  SSCarpal Tunnel

  Points: 4892

  More actions

  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