CODEWITHSUNDEEP
sqlsql-servert-sqlsubstringcharindex
Prezes Łukasz
Prezes Łukasz

Reputation: 968

Issue with using charindex and substring

I have below code:

declare @error varchar(max)
set @error = 'Invalid content. Error ids=[3001]'

select substring(@error, charindex('[', @error)+1, charindex(']', @error)-1)

I want to get number: 3001, but current result is

3001]

Anyone can explain, why still have bracket ']' ?

Upvotes: 0

Views: 2547

Answers (3)

Chris Albert
Chris Albert

Reputation: 2507

The third parameter of SUBSTRING is length. You are passing charindex(']', @error)-1 which equals 32. So therefore it will gather 32 characters from the indicated starting position.

What it looks like you are trying to do is determine the length of the string between the brackets. To do this you need to subtract the index of the left bracket from the index of the right bracket. See below example.

declare @error varchar(max)
set @error = 'Invalid content. Error ids=[3001]'

select substring(@error, charindex('[', @error)+1, (CHARINDEX(']', @error)-1) - charindex('[', @error))

Upvotes: 1

Thom A
Thom A

Reputation: 95554

This'll more easily be explained by splitting out the query into parts.

Firstly:

SELECT charindex('[', @error)+1;

This returns 29. Seems correct. Next:

SELECT charindex(']', @error) -1;

This returns 32. This means you're therefore doing:

SELECT SUBSTRING(@error, 29, 32);

See where you're going wrong now? The 3rd parameter is how many characters you want. You don't want 32, you want 4. Thus, instead try:

SELECT SUBSTRING(@error, CHARINDEX('[', @error)+1, CHARINDEX(']', @error) - (CHARINDEX('[', @error)+1));

Upvotes: 2

TriV
TriV

Reputation: 5148

Because the third parameter of substring means length of substring, not from index to index

Reference: https://learn.microsoft.com/en-us/sql/t-sql/functions/substring-transact-sql

You can try this

select substring(@error, charindex('[', @error)+1, charindex(']', @error) - charindex('[', @error) - 1)

Upvotes: 3

Related Questions