CODEWITHSUNDEEP
sqlsql-serversubstringcharindex
Ash
Ash

Reputation: 21

SQL Server substring with optional character

I wanted to take the substring of COLUMN_A after '#' character.

I used: 

RIGHT(COLUMN_A, (LEN(COLUMN_A) - CHARINDEX('#', COLUMN_A)))

This works fine when COLUMN_A has a '#' character. But in some rows, if the '#' is not there, then I get empty result. I tried using ISNULL() to handle this, but no luck!

Any advice on how this can be handled?

Upvotes: 2

Views: 808

Answers (1)

Malk
Malk

Reputation: 12003

Use a case statement to check if there is a pound sign and return something else:

CASE WHEN CHARINDEX('#',COLUMN_A) = -1 
  THEN COLUMN_A
  ELSE RIGHT(COLUMN_A, (len(COLUMN_A) - CHARINDEX('#',COLUMN_A)))
END

Upvotes: 1

Related Questions