Using Charindex with in Substring is resulting in error

Question

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.

t-clausen.dk · Accepted Answer · 2015-06-08 13:00:29Z

5

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)

answered Jun 8, 2015 at 13:00

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tanner · Accepted Answer · 2015-06-08 13:01:01Z

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.

Aniruddha Mukherjee · Accepted Answer · 2018-10-18 04:17:24Z

0

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

answered Oct 18, 2018 at 4:17

Aniruddha Mukherjee

11 bronze badge

Collectives™ on Stack Overflow

Using Charindex with in Substring is resulting in error

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related