SQL Select substring if delimiter exists else give full string

Question

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?

Salutary

Answer 1

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

Mineralize

Oh nice yea I also thought after a moment "why can't i Just always have a delimiter?" So this works too, idk if it is as good though SUBSTRING(sc.location +'/',0, CHARINDEX('/', sc.location + '/')) – Salutary

This construct is too long.. are there any simpler form? – Gusgusba

Add "END" before the "FROM sc" in Oracle SQL (details at docs.oracle.com/cd/B19306_01/server.102/b14200/…) – Adherent

Answer 2

Alternate, a little simpler.

replace( left( email, patindex( '%@%', email+'@' ) ), '@', '')

This example will extract the name part of an email address, taking everything up to the ampersand then removing the ampersand. If no ampersand, the appended ampersand means you get it all.

Hourly

OK for email addresses, but won't handle the case where the value starts with the delimiter – Illassorted