Search code examples
sqlregexstringsubstringdelimiter

SQL Select substring if delimiter exists else give full string

I'm trying to select the first part of a string with a delimiter that doesn't always exist. I have the below SUBSTRING function that works great when the delimiter is present, but doesn't return anything when it isn't i.e. the query below 

SELECT SUBSTRING(sc.location +'/',0, CHARINDEX('/', sc.location)) FROM sc

yields Tower #1 for the value Tower #1/Room #3, but NULL if the input is just Tower #5

Is there a way to return the full string if the delimiter does not exist?

Solution

  • Just use the CASE To the select. You can try as ,

    SELECT 
     CASE WHEN CHARINDEX('/', sc.location) > 0 THEN SUBSTRING(sc.location +'/',0, CHARINDEX('/', sc.location))
          ELSE sc.location END
FROM sc