CODEWITHSUNDEEP
sqlsql-server
ANR
ANR

Reputation: 167

Extract value in between string in SQL

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[')) )

Upvotes: 2

Views: 414

Answers (3)

Chris Schaller
Chris Schaller

Reputation: 16554

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%'

Upvotes: 1

Marko Ivkovic
Marko Ivkovic

Reputation: 1290

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)

Upvotes: 1

Joe
Joe

Reputation: 626

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)

Upvotes: 4

Related Questions