Extract Float From String/text Sql Server

I have a Data field that is supposed to have floating values(prices), however, the DB designers have messed up and now I have to perform aggregate functions on that field. Whereas

Solution 1:

This will do want you need, tested on (http://sqlfiddle.com/#!6/6ef8e/53)

DECLARE@datavarchar(max) ='$70.23 per m2'SelectLEFT(SubString(@data, PatIndex('%[0-9.-]%', @data), 
                  len(@data) - PatIndex('%[0-9.-]%', @data) +1
                 ), 
        PatIndex('%[^0-9.-]%', SubString(@data, PatIndex('%[0-9.-]%', @data), 
                  len(@data) - PatIndex('%[0-9.-]%', @data) +1))
        )

But as jpw already mentioned a regular expression over a CLR would be better

Solution 2:

This should work too, but it assumes that the float numbers are followed by a white space in case there's text after.

// sample data
DECLARE@tabTABLE (strAlphaNumeric NVARCHAR(30))
INSERT@tabVALUES ('80.50'),('$80.50'),('$80.50 per sqm')

// actual query
SELECT 
  strAlphaNumeric AS Original, 
  CAST (
    SUBSTRING(stralphanumeric, PATINDEX('%[0-9]%', strAlphaNumeric), 
      CASEWHEN PATINDEX('%[ ]%', strAlphaNumeric) =0THEN LEN(stralphanumeric) 
      ELSE 
      PATINDEX('%[ ]%', strAlphaNumeric) - PATINDEX('%[0-9]%', strAlphaNumeric)
      END
    ) 
    ASFLOAT) AS CastToFloat
FROM@tab

From the sample data above it generates:

Original                       CastToFloat
------------------------------ ----------------------
80.50                          80,5
$80.50                         80,5
$80.50 per sqm                 80,5

Sample SQL Fiddle.

If you want something more robust you might want to consider writing an CLR-function to do regex parsing instead like described in this MSDN article: Regular Expressions Make Pattern Matching And Data Extraction Easier

Solution 3:

Inspired on @deterministicFail, I thought a way to extract only the numeric part (although it's not 100% yet):

DECLARE@NUMBERSTABLE (
    Val VARCHAR(20)
)
INSERTINTO@NUMBERSVALUES
('$70.23 per m2'),
('$81.23'),
('181.93 per m2'),
('1211.21'),
(' There are 4 tokens'),
('  No numbers    '),
(''),
('  ')
selectCASEWHEN ISNUMERIC(RTRIM(LEFT(RIGHT(RTRIM(LTRIM(n.Val)), 1+LEN(RTRIM(LTRIM(n.Val)))-PatIndex('%[0-9.-]%', RTRIM(LTRIM(n.Val)))), LEN(RIGHT(RTRIM(LTRIM(n.Val)), 1+LEN(RTRIM(LTRIM(n.Val)))-PatIndex('%[0-9.-]%', RTRIM(LTRIM(n.Val)))))- PATINDEX('%[^0-9.-]%',RIGHT(RTRIM(LTRIM(n.Val)), 1+LEN(RTRIM(LTRIM(n.Val)))-PatIndex('%[0-9.-]%', RTRIM(LTRIM(n.Val))))))))=1THEN
            RTRIM(LEFT(RIGHT(RTRIM(LTRIM(n.Val)), 1+LEN(RTRIM(LTRIM(n.Val)))-PatIndex('%[0-9.-]%', RTRIM(LTRIM(n.Val)))), LEN(RIGHT(RTRIM(LTRIM(n.Val)), 1+LEN(RTRIM(LTRIM(n.Val)))-PatIndex('%[0-9.-]%', RTRIM(LTRIM(n.Val)))))- PATINDEX('%[^0-9.-]%',RIGHT(RTRIM(LTRIM(n.Val)), 1+LEN(RTRIM(LTRIM(n.Val)))-PatIndex('%[0-9.-]%', RTRIM(LTRIM(n.Val)))))))
        ELSE'0.0'ENDFROM@NUMBERS n