Returning the column value even if the special character isn’t present using SUBSTRING & CHARINDEX

I’m using SQL and trying to show all the data in a column before a special character like <.

I’ve used this SQL:

SUBSTRING(ACTIVITY.Name, 0, CHARINDEX('<', ACTIVITY.Name, 2)) AS ActivityIdentifier

It works absolutely fine when there is a < in the column, but when one isn’t present I get no result. I need to be able to return the column value even if the character isn’t present.

I looked at RTRIM, LEFT and LEN functions but as my Activity Name can be different lengths, they didn’t seem to fit.

I’d appreciate any advice.

Answer

I think the simplest fix is to append a '<' to the string:

SUBSTRING(ACTIVITY.Name, 1, CHARINDEX('<', ACTIVITY.Name + '<', 2)) as ActivityIdentifier

If you don’t want the '<' in the result, then:

SUBSTRING(ACTIVITY.Name, 1, CHARINDEX('<', ACTIVITY.Name + '<', 2) - 1) as ActivityIdentifier