CODEWITHSUNDEEP
sql-serversubstringcharindex
Sreedhar Danturthi
Sreedhar Danturthi

Reputation: 7571

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.

Upvotes: 1

Views: 2228

Answers (3)

Aniruddha Mukherjee
Aniruddha Mukherjee

Reputation: 1

please use select REPLACE('SH1684|32I5', '|', '') . This must work. Replace Function Example

Upvotes: 0

Tanner
Tanner

Reputation: 22733

Just use a CASE statement to determine if the | is present. CHARINDEX will return 0 if it's not present:

DECLARE @val VARCHAR(15) = 'SH1684|32I5'

-- show the value
SELECT @val

-- returns the section before the pipe
SELECT  CASE WHEN CHARINDEX('|', @val) > 0
             THEN SUBSTRING('SH1684|32I5', 1,
                            CHARINDEX('|', 'SH1684|32I5') - 1)
             ELSE @val
        END AS ID

-- set value without pipe
SET @val = 'SH168432I5'
-- same query returns entire value as there is no pipe
SELECT  CASE WHEN CHARINDEX('|', @val) > 0
             THEN SUBSTRING('SH1684|32I5', 1,
                            CHARINDEX('|', 'SH1684|32I5') - 1)
             ELSE @val
        END AS ID

So it will return the portion before the | if it's present, otherwise it will return the entire value.

Upvotes: 2

t-clausen.dk
t-clausen.dk

Reputation: 44316

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)

Upvotes: 5

Related Questions