Is there a better way than this to split a string and get the first substring?

Question

I'm trying to cut off everything after a certain character (in this case '&') and if there is no occurence of that character, get the entire string.

select left(@str,isnull(nullif(charindex('&',@str) - 1,-1),len(@str)))

This seems to be a rather needlessly complicated way of doing it. Is there a better/shorter way?

Here is a full test case to indicate what I'm doing...

declare @str as varchar(255) 
set @str = 'abc&def&ghi'

select left(@str,isnull(nullif(charindex('&',@str) - 1,-1),len(@str)))

-- Returns 'abc'

set @str = 'abc'

select left(@str,isnull(nullif(charindex('&',@str) - 1,-1),len(@str)))

-- Returns 'abc'

Answer 1

Well - here is 'another' way...

DECLARE @str AS VARCHAR(255)

SET @str = 'abc&def&ghi'

SELECT CASE 
        WHEN charindex('&', @str) > 0
            THEN substring(@str, 1, charindex('&', @str) - 1)
        ELSE @str
        END AS SelectedString
-------------------------------
SET @str = 'abc'

SELECT CASE 
        WHEN charindex('&', @str) > 0
            THEN substring(@str, 1, charindex('&', @str) - 1)
        ELSE @str
        END AS SelectedString

OR - you could simply ALWAYS concatenate '&' at the end of the string like this:

DECLARE @str AS VARCHAR(255)

SET @str = 'abc&def&ghi'

SELECT substring(@str, 1, charindex('&', @str + '&') - 1)
-------------------------------
SET @str = 'abc'

SELECT substring(@str, 1, charindex('&', @str + '&') - 1)