SSMS SQL Return only a few characters after a specific string is found the first time

Question

I'm trying to return only 9 characters (or the total $ amount) after a certain string is found the first time in a column.

Example Input 1: "The amount you currently owe is $5,000.00 The amount you do not owe is $2,000.00"

Example Input 2: "The amount you currently owe is $0.00 The amount you do not owe is $0.00"

Example Data

Example Output 1: $5,000.00 Example Output 2: $0.00

Example Output

Thanks!

Answer 1

With minimal information provided I guess this is what you might be looking at.

DECLARE @Desc VARCHAR(100) = 'The amount you currently owe is $1.00 The amount you do not owe is $2,000.00'

 SELECT SUBSTRING(@Desc, PATINDEX('%$%', @Desc), ABS(CHARINDEX(N'.', @Desc, CHARINDEX(N'$', @Desc, 1)) - CHARINDEX(N'$', @Desc, 1)) + 3)

DECLARE @Desc VARCHAR(100) = 'The amount you currently owe is $10.00 The amount you do not owe is $2,000.00'

 SELECT SUBSTRING(@Desc, PATINDEX('%$%', @Desc), ABS(CHARINDEX(N'.', @Desc, CHARINDEX(N'$', @Desc, 1)) - CHARINDEX(N'$', @Desc, 1)) + 3)

DECLARE @Desc VARCHAR(100) = 'The amount you currently owe is $100.00 The amount you do not owe is $2,000.00'

 SELECT SUBSTRING(@Desc, PATINDEX('%$%', @Desc), ABS(CHARINDEX(N'.', @Desc, CHARINDEX(N'$', @Desc, 1)) - CHARINDEX(N'$', @Desc, 1)) + 3)

DECLARE @Desc VARCHAR(100) = 'The amount you currently owe is $1,000.00 The amount you do not owe is $2,000.00'

 SELECT SUBSTRING(@Desc, PATINDEX('%$%', @Desc), ABS(CHARINDEX(N'.', @Desc, CHARINDEX(N'$', @Desc, 1)) - CHARINDEX(N'$', @Desc, 1)) + 3)

DECLARE @Desc VARCHAR(100) = 'The amount you currently owe is $10,000.00 The amount you do not owe is $2,000.00'

 SELECT SUBSTRING(@Desc, PATINDEX('%$%', @Desc), ABS(CHARINDEX(N'.', @Desc, CHARINDEX(N'$', @Desc, 1)) - CHARINDEX(N'$', @Desc, 1)) + 3)

DECLARE @Desc VARCHAR(100) = 'The amount you currently owe is $100,000.00 The amount you do not owe is $2,000.00'

 SELECT SUBSTRING(@Desc, PATINDEX('%$%', @Desc), ABS(CHARINDEX(N'.', @Desc, CHARINDEX(N'$', @Desc, 1)) - CHARINDEX(N'$', @Desc, 1)) + 3)

DECLARE @Desc VARCHAR(100) = 'The amount you currently owe is $1,000,000.00 The amount you do not owe is $2,000.00'

 SELECT SUBSTRING(@Desc, PATINDEX('%$%', @Desc), ABS(CHARINDEX(N'.', @Desc, CHARINDEX(N'$', @Desc, 1)) - CHARINDEX(N'$', @Desc, 1)) + 3)