Extract value in between string in SQL

Question

I have a sample string like below and this string always contains the word "Inventory Charge[0.00068]"

 DECLARE @Text NVARCHAR(MAX) = 'Materials Discount[0] ) + Inventory Charge[0.00068] Second gfggfdgfd gfgfgfgf'

would like to get value in between brackets for Inventory Charge.

Required output: 0.00068

I have tried with substring but not able to get the desired result. Please help.

 DECLARE @Text NVARCHAR(MAX) = 'Materials Discount[0] ) + Inventory Charge[0.00068]    Second'

SELECT SUBSTRING(@Text, CHARINDEX('Inventory Charge[', @Text) + 
LEN('Inventory Charge[') + 1, CHARINDEX(']',@Text) 
- (CHARINDEX('Inventory Charge[', @Text) + 2 + LEN('Inventory Charge[')) )

You wouldn't have these problems if whoever designed your systems stopped storing delimited (or worse) attributes in a string. You have quite a few similar questions. — SMor
– SMor, Commented Mar 22, 2021 at 11:55

Joe · Accepted Answer · 2021-03-22 10:32:34Z

4

I find this type of thing easier to work out when breaking it down. It also helps avoid repeating the initial search for the starting substring location:

declare @text NVARCHAR(MAX) = 'Materials Discount[0] ) + Inventory Charge[0.00068]    Second'
declare @leftSearchStr nvarchar(20) = 'Inventory Charge['
declare @rightSearchStr nvarchar(20) = ']'
declare @startPos int = charindex(@leftSearchStr, @text, 1) + LEN(@leftSearchStr)
declare @endPos int = charindex(@rightSearchStr, @text, @startPos)

SELECT SUBSTRING(@text, @startPos, @endPos - @startPos)

answered Mar 22, 2021 at 10:32

Joe

6362 gold badges12 silver badges31 bronze badges

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

Comments

Marko Ivkovic · Accepted Answer · 2021-03-22 10:39:27Z

1

I solved it like this. Surely there is a better way to solve this, but this works.

DECLARE @Text NVARCHAR(MAX) = 'Materials Discount[0] ) + Inventory Charge[0.00068]    Second', 
        @Trim1 NVARCHAR(MAX),
        @Trim2 NVARCHAR(MAX)

SET @Trim1 = SUBSTRING(@Text, CHARINDEX('[', @Text) + 1,LEN(@Text))

SET @Trim2 = SUBSTRING(@Trim1, CHARINDEX('[', @Trim1) + 1, LEN(@Trim1))

SELECT LEFT(@Trim2, LEN(@Trim2) - CHARINDEX(']', @Trim2) - 3)

answered Mar 22, 2021 at 10:39

Marko Ivkovic

1,2981 gold badge11 silver badges14 bronze badges

Comments

Chris Schaller · Accepted Answer · 2021-03-22 11:05:25Z

If you know that the string you want to select is the Last bracketed selection, then we can simply use REVERSE and then find the first bracketed value.

If you have to do this in a single operation, I find it easier to incorporate a CROSS APPLY to calculate the steps in between:

DECLARE @Text NVARCHAR(MAX) = 'Materials Discount[0] ) + Inventory Charge[0.00068]    Second'

SELECT Reverse(SubString(RevText, [RLeft], [RRight]-[RLeft]))
FROM ( SELECT REVERSE(@Text) as RevText) text
OUTER APPLY (SELECT CHARINDEX(']', RevText) + 1 as [RLeft], 
                    CHARINDEX('[', revText) as [RRight]) Calcs

If you don't know for sure that the search term is the first or last bracket, then we just need to search on the prefix first, and use that as the start location for the CharIndex function:

SELECT SubString([Text], [Left], [Right]-[Left])
FROM ( SELECT @Text as [Text], 'Inventory Charge[' as prefix ) inputs
OUTER APPLY (SELECT CHARINDEX(prefix, [Text]) + LEN(prefix) as [Left]) Calcs1
OUTER APPLY (SELECT CHARINDEX(']', [Text], [Left]) as [Right]) Calcs2

You can get a little bit fancy using STRING_SPLIT to tokenize the input for you too, note that you need to split by the close bracket so that the value and the field prefix are in the same result:

This solution uses REVERSE again because we know the token is at the end of the line, so we can use LEFT logic to save an index lookup

SELECT Reverse(LEFT(REVERSE(value), CHARINDEX('[', REVERSE(value))-1))
FROM String_Split(@Text, ']') s
WHERE s.value LIKE '%Inventory Charge%'

Or you can use SUBSTRING again:

SELECT SUBSTRING(value,[LEFT],[Length] - [Left] + 1)
FROM String_Split(@Text, ']') s
CROSS APPLY (SELECT CHARINDEX('[', value) + 1 as [LEFT], LEN(value) as [Length]) calcs
WHERE s.value LIKE '%Inventory Charge%'

Collectives™ on Stack Overflow

Extract value in between string in SQL

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