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 ']' ?
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)
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));
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))
