sql server - Using PATINDEX to find varying length patterns in T-SQL

Question

I'm looking to pull floats out of some varchars, using PATINDEX() to spot them. I know in each varchar string, I'm only interested in the first float that exists, but they might have different lengths.

e.g.

'some text 456.09 other text'
'even more text 98273.453 la la la'

I would normally match these with a regex

  "[0-9]+[.][0-9]+"

However, I can't find an equivalent for the + operator, which PATINDEX accepts. So they would need to be matched (respectively) with:

'[0-9][0-9][0-9].[0-9][0-9]' and '[0-9][0-9][0-9][0-9][0-9].[0-9][0-9][0-9]' 

Is there any way to match both of these example varchars with one single valid PATINDEX pattern?

Answer 1

I blogged about this a while ago. Extracting numbers with SQL server

Declare @Temp Table(Data VarChar(100))

Insert Into @Temp Values('some text 456.09 other text')
Insert Into @Temp Values('even more text 98273.453 la la la')
Insert Into @Temp Values('There are no numbers in this one')

Select Left(
             SubString(Data, PatIndex('%[0-9.-]%', Data), 8000),
             PatIndex('%[^0-9.-]%', SubString(Data, PatIndex('%[0-9.-]%', Data), 8000) + 'X')-1)
From   @Temp