Search code examples
sql-serversubstringcharindex

Using Charindex with in Substring is resulting in error

I have CHARINDEX function with in SUBSTRING which has to identify delimiter (|) value and return the value as parameter to a substring

When delimiter is present it works fine

select SUBSTRING('SH1684|32I5', 1, CHARINDEX('|', 'SH1684|32I5') -1) AS ID

where the ID is SH1684

When it is not present

This results in error as

Msg 536, Level 16, State 1, Line 1 Invalid length parameter passed to the substring function.

But my intention is to get value in ID as SH168432I5.

How can I circumvent this problem.

Solution

  • Try this instead, by adding the delimiter, you can make sure it is in the correct logical position in cases where it is missing:

    SELECT LEFT('SH1684|32I5', CHARINDEX('|', 'SH1684|32I5' + '|') - 1)