Extracting numeric value from a string? 1

[Hansje]

Hi there,
Does anybody know of a script to extract a numeric value from within a string? I'm trying to make something myself, but it's taking more then I figured it would. I have a column, where currencyvalues are embedded in a string(don't ask me why!). I need to extract the moneyvalue from these columns.
Example:
In string 'xnl 23.15' the value 23.15 needs to be exteracted. The layout of these strings is not always the same, could be '23.15 xnl' or '-23.15 xdr' or anything.

Tnx,

Hans Brouwer

 

[SQLBill]

Is the number value always 4 digits?
Is there always a decimal?
Are there always two digits after the decimal?

Let's say there is always a decimal and always two digits after the decimal.

You could use PATINDEX to find the position of the first number value by using the string wildcard [0-9], then finding the PATINDEX of the decimal point. Lastly do a SUBSTRING starting at the first PATINDEX and going to the second PATINDEX + 2.

Lookup PATINDEX (also CHARINDEX), SUBSTRING, and the WILDCARDs in the Books OnLine.

-SQLBill

 

[SQLBill]

This will get you started:

USE TempDB
DECLARE @myvar CHAR(10)
SET @myvar = 'ab12.34def'
SELECT SUBSTRING(@myvar, (PATINDEX('%[0-9]%', @myvar)), ((PATINDEX('%.%', @myvar)) - (PATINDEX('%[0-9]%', @myvar))+3))

Run that command and it will return 12.34

This works if there is ALWAYS a decimal point and ALWAYS two digits after the decimal. You will have to include an IF or CASE to deal with negative numbers. If there's not always a decimal point and/or not always two digits after the decimal, you'll have to 'play' with the above script to get it to work.

The first PATINDEX finds the location/position of the first number. The second PATINDEX finds the location/position of the decimal - then subtracts the first position from the second - this gives the LENGTH of the number to the digit. Then adds 3 (the decimal and the two digits after the decimal point).

SUBSTRING requires the expression (@myvar or your column name), the start position (the first PATINDEX value), the length (the final information from the last two PATINDEXes and the +3).

-SQLBill