Extract numeric values with digits from string

Transact-SQL
1

I have a column that have numeric values in a string. The numeric values are not always whole number. some have digits. some numbers are at the begining of string , some are in the middle and some are at end. See example below. I want to extract the data as shown on the Cleaned Test Result column. I tried PATINDEX and got the numeric value but removed the digits (change 5.3 to 53). I don't want the digits to be removed

example

Test Result
94 mg/dl
18
106
5.3% mm
5.9%
28
Neg 11.2
14 MG/DL
Pos 4.5 Val

Cleaned Test Result
94
18
106
5.3
5.9
28
11.2
14
4.5

2

Basically, something like this will do it:

DECLARE @s VARCHAR(20) = 'Pos 4.5 Val'

SELECT s.s, e.e
FROM (
    VALUES (NULL)
    ) v(n)
CROSS APPLY (
    SELECT substring(@s, PATINDEX('%[0-9]%', @s), LEN(@s))
    ) s(s)
CROSS APPLY (
    SELECT substring(s.s, 1, PATINDEX('%[^0-9.]%', s.s))
    ) e(e)
4

Another way:

DECLARE @A VARCHAR(100)= 'Pos 4.5 Val' --'A12B45M1'

;WITH CTE
AS
(
SELECT PATINDEX('%[0-9]%',@A) AS POS, CAST(STUFF(@A,1, 0,'')AS VARCHAR) AS STR1

UNION ALL

SELECT PATINDEX('%[0-9]%',STUFF(STR1,1, POS,'')), CAST(STUFF(STR1,1, POS,'') AS VARCHAR) AS STR1
FROM CTE	
WHERE PATINDEX('%[0-9]%',CAST(STUFF(STR1,1, POS,'') AS VARCHAR)) > 0

),
CTE2 AS
(
SELECT *,
SUM(POS)OVER(ORDER BY (SELECT 1) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)as finalPOS
FROM CTE
)

--SELECT * FROM CTE2

SELECT CAST(
(SELECT SUBSTRING(@A,finalPOS,1)
FROM CTE2
FOR XML PATH('')
)
AS VARCHAR(50))

5

Thank you for the reply.

Your response seems to address only one of the data ('Pos 4.5 Val'). That is just one row data. I am looking for a SQL statement that can handle the different strings (as expressed in my example- Test Result) and give me a clean data (as expressed in my example- Cleaned Test Result) above

6

Quite right! We've shown you how to do it in general. Now you need to take those examples and implement them for your specific case.

7

If you provide a CREATE TABLE statement and some INSERT statements that put a few rows of sample data into the table, (and provide an example of what the results should look like, as you ahve done) then people here can use your Test Table and just add the statement(s) to get the output.

Otherwise each and every person here has to do all that work ... and most won't bother.

8

I missed working out the decimal part..the solution should work for all numbers (without decimal).
You can either extend gbritton's solution or modify mine to include the decimal.