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
;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))
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
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.
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.