Is there a better way than this to split a string and get the first substring? I'm trying to cut off everything after a certain character (in this case '&amp;') and if there is no occurence of that character, get the entire string.

select left(@str,isnull(nullif(charindex('&amp;',@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&amp;def&amp;ghi'

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

-- Returns 'abc'

set @str = 'abc'

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

-- Returns 'abc'
Well - here is 'another' way...

DECLARE @str AS VARCHAR(255)

SET @str = 'abc&amp;def&amp;ghi'

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

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


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

DECLARE @str AS VARCHAR(255)

SET @str = 'abc&amp;def&amp;ghi'

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

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