Extracting an amount from a string of data

1

Hello.

I have been trying to extract the amount out of a string (128.12):

18 PROMO PURCH BALANCE OF 128.12 ROLLED TO STD PURCH 64 23:32:47 0

Using this I can get the first two digits (18):
stuff(stuff(ah.TokenString+'x', patindex('%[0-9][^0-9.]%', ah.TokenString+'x') + 1, len(ah.TokenString), ''
), 1, patindex('%[0-9.]%', ah.TokenString) - 1, '')

Using this I get 28.12 ROLL:
Case When ah.TokenString Like '%[0123456][0-9].[01][0-2]%' Then SUBSTRING(ah.TokenString, PATINDEX('%[0123456][0-9].[01][0-2]%',ah.TokenString),10) Else '' End

Thank you.

2 
use sqlteam
go

declare @juicy table(ducky varchar(150))

insert into @juicy
select '18 PROMO PURCH BALANCE OF 128.12 ROLLED TO STD PURCH 64 23:32:47 0';

;with src
as(
	select *,
	   TRY_CONVERT(decimal(7,2), item) x
	  from @juicy j
	  cross apply DelimitedSplit8K(j.ducky, ' ') c
          where c.Item like '%.%'
)
select * 
 from src
 where x is not null
3 
SELECT 
    string,
    SUBSTRING(string, find1 - left_len + 1, left_len + right_len - 1)
FROM ( VALUES('18 PROMO PURCH BALANCE OF 128.12 ROLLED TO STD PURCH 64 23:32:47 0') ) AS test_data(string)
CROSS APPLY ( SELECT PATINDEX('%[0-9].[0-9]%', string) + 1) AS ca1(find1)
CROSS APPLY ( SELECT CHARINDEX(' ', REVERSE(LEFT(string, ca1.find1))) - 1) AS ca2(left_len)
CROSS APPLY ( SELECT CHARINDEX(' ', string, ca1.find1) - ca1.find1 ) AS ca3(right_len)
4

Thanks for the responses. I apologize, I should have provided more context on what I was trying to accomplish.

I was trying to grab the number between OF and ROLLED. Not just for the example I provided.

I was able to stumble my way through to get what I was looking for:
SUBSTRING(ah.TokenString, CHARINDEX('OF', ah.TokenString)+2,CHARINDEX('ROLLED',ah.TokenString) -2 - CHARINDEX('OF', ah.TokenString) + Len('OF')-2)

image
image1239×56 4.37 KB

5

please provide some more sample data rather than an image? use the sample that I used to provide the data part.

6

That would have been much easier to do.