CODEWITHSUNDEEP
sql-serverstringt-sql
lbrrrr
lbrrrr

Reputation: 75

Return words in between specific phrases in string in T-SQL

My column Details would return a big message such as and the only thing I want to extract is the number 874659.29. This number varies among rows but it will always comes after ,"CashAmount": and a coma (,).

There will be only one ,"CashAmount": but several comas after.

dhfgdh&%^&%,"CashAmount":874659.29,"Hasdjhf"&^%^%

Therefore, I was wondering if I could use anything to only show the number in my output column.

Thanks in advance!

Upvotes: 0

Views: 56

Answers (2)

Sean Lange
Sean Lange

Reputation: 33571

Here is another option for this just using some string manipulation. 

declare @Details varchar(100) = 'dhfgdh&%^&%,"CashAmount":874659.29,"Hasdjhf"&^%^%'

select left(substring(@Details, CHARINDEX('CashAmount":', @Details) + 12 /*12 is the length of CashAmount":*/, LEN(@Details))
    , charindex(',', substring(@Details, CHARINDEX('CashAmount":', @Details) + 12, LEN(@Details))) - 1)

Upvotes: 3

TheGameiswar
TheGameiswar

Reputation: 28890

You could use one of the split string functions as described here..

declare @string varchar(max)
set @string='dhfgdh&%^&%,"CashAmount":874659.29,"Hasdjhf"&^%^%'

select b.val from 
[dbo].[SplitStrings_Numbers](@string,',')a
cross apply
(
select isnumeric(replace(a.item,'"CashAmount":',1)),replace(a.item,'"CashAmount":',1)
) b(chk,val)
where b.chk=1

Output:
874659.29

The above will work only if number comes after cashamount and before , and if it doesn't have any special characters..

if your number has special characters,you can use TRY_PARSE and check for NULL..

Upvotes: 2

Related Questions