Extract float from String/Text SQL Server

Question

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 80% of the time data is in correct format,eg. '80.50', sometime it is saved as '$80.50' or '$80.50 per sqm'.

The data field is nvarchar. What I need to do is extract the floating point number from the nvarchar. I came accross this: Article on SQL Authority

This, however, solves half my problem, or compound it, some might say. That function just returns the numbers in a string. That is '$80.50 per m2'will return 80502. Obviously that wont work. I tried to change the Regex from => PATINDEX('%[^0-9]%', @strAlphaNumeric) to=> PATINDEX('%[^0-9].[^0-9]%', @strAlphaNumeric) doesnt work. Any help would be appreciated.

what you expect from "$80.50 per m2" in answer??

Dhaval
– Dhaval

2013-11-21 09:32:38 +00:00
Commented Nov 21, 2013 at 9:32 — Dhaval
– Dhaval, Commented Nov 21, 2013 at 9:32

deterministicFail · Accepted Answer · 2013-11-21 10:03:28Z

3

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

DECLARE @data varchar(max) = '$70.23 per m2'
Select LEFT(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

answered Nov 21, 2013 at 10:03

deterministicFail

1,2819 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jpw Over a year ago

This will miss values like 80.50 and $80.50 and return blanks.

deterministicFail Over a year ago

I tested it with your values, but couldnt find a value that would work except 123. 55 but a RegEx would fail this one too

jpw · Accepted Answer · 2013-11-21 10:16:29Z

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 @tab TABLE (strAlphaNumeric NVARCHAR(30))
INSERT @tab VALUES ('80.50'),('$80.50'),('$80.50 per sqm')

// actual query
SELECT 
  strAlphaNumeric AS Original, 
  CAST (
    SUBSTRING(stralphanumeric, PATINDEX('%[0-9]%', strAlphaNumeric), 
      CASE WHEN PATINDEX('%[ ]%', strAlphaNumeric) = 0 
      THEN LEN(stralphanumeric) 
      ELSE 
      PATINDEX('%[ ]%', strAlphaNumeric) - PATINDEX('%[0-9]%', strAlphaNumeric)
      END
    ) 
    AS FLOAT) 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

Erick · Accepted Answer · 2013-11-21 11:21:36Z

0

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

DECLARE @NUMBERS TABLE (
    Val VARCHAR(20)
)
INSERT INTO @NUMBERS VALUES
('$70.23 per m2'),
('$81.23'),
('181.93 per m2'),
('1211.21'),
(' There are 4 tokens'),
('  No numbers    '),
(''),
('  ')
select
    CASE
        WHEN 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))))))))=1 THEN
            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'
    END
FROM @NUMBERS n

answered Nov 21, 2013 at 11:21

Erick

1,0227 silver badges6 bronze badges

3 Comments

OGHaza Over a year ago

Produces nice output but dear god getting there is messy. 46 x LEFT/RIGHT/LTRIM/RTRIM

Erick Over a year ago

@OGHaza, I agree. Depending on the input string, you could remove ltrim and rtrim. I've used these functions when I inserted the blanked string ' ' as a testing case.

OGHaza Over a year ago

good point. jpw's answer falls over if there is leading whitespace - though of course he can also trim the input.

Collectives™ on Stack Overflow

Extract float from String/Text SQL Server

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related