Solution 1 is the best advice you are going to receive but in this solution I am going to assume that you already have this data and want to convert it to the more sensible format suggested in Solution 1.
Firstly examine the data - at first glance it looks like there are 7 or more different formats but the numbers that you are interested in are in only two different formats … nothing else is in the column e.g. your row 3
0.3583
, OR they are surrounded by brackets (...).
If surrounded by brackets you only have two versions of that - numbers only e.g. your row 1
8.92 mm (0.3510)
or followed by ' in' e.g. your row 5
#77 (0.0180 in)
Everything else in the data is ignored, so what you are trying to do is
1. Remove anything not related to the number required
2. Remove anything in brackets that is not a number
3. Remove the brackets
When approaching problems of this kind it is really important to do this up-front analysis so that you can be sure you are covering every eventuality.
You can do step 1 with sql like this
select id, s.[value]
FROM @test
CROSS APPLY string_split (dat, '(') s
WHERE s.value LIKE '%)%'
UNION
select id, dat
FROM @test
WHERE dat NOT LIKE '%(%'
Points to note:
a) if you are using a version of SQL prior to 2016 you will have to write your own UDF to do the string split. There are hundreds of examples available via your favourite search engine.
b) note that in the first query I am just looking for the numbers in brackets. I ignore anything that wasn't in brackets by checking for the closing bracket
c) the second query looks for the pattern where the column contains our numbers only rule.
So far we get these results:
id [value]
1 0.3510)
2 1.2210)
3 0.3583
4 1.0312)
5 0.0180 in)
6 0.2770)
7 0.1719)
8 0.6562 in)
So we still need to get rid of the closing bracket and " in" which we can do like this
;with cte as
(
select id, REPLACE(REPLACE(s.value, ')',''), ' in','') AS [value]
FROM @test
CROSS APPLY string_split (dat, '(') s
WHERE s.value LIKE '%)%'
UNION
select id, dat
FROM @test
WHERE dat NOT LIKE '%(%'
)
SELECT id, [value]
from cte
A really important thing to note here is that these values are strings - you may need to convert them to numbers before they are used.
Now I am going to refer back to Solution 1 where @OriginalGriff points out
Quote:
you will find inputs that don't meet any of the examples you show, so you will be changing complicated SQL code to detect and add new cases frequently.
Fortunately you are going to follow his advice so this will be a one-off exercise rather than a frequent occurrence. However, you will need to examine your converted data to make sure you have captured everything.
EDIT: I just changed my test data to check for other problems - note the new format in the last value
declare @test table (id int identity(1,1), dat nvarchar(50))
insert into @test (dat) values
('8.92 mm (0.3510)' ),
('31.00 mm (1.2210)'),
('0.3583'),
('1-1/32" (1.0312)'),
('#77 (0.0180 in)' ),
('J (0.2770)'),
('11/64" (0.1719)'),
('21/32 in (0.6562 in)')
,('0.3583 in')
This caused my code above to throw an error
Msg 8114, Level 16, State 5, Line 28
Error converting data type nvarchar to numeric.
This can be avoided (in this instance) by doing the replacements outside of the cte i.e.
;with cte as
(
select id, s.[value]
FROM @test
CROSS APPLY string_split (dat, '(') s
WHERE s.value LIKE '%)%'
UNION
select id, dat
FROM @test
WHERE dat NOT LIKE '%(%'
)
SELECT id, REPLACE(REPLACE([value], ')',''), ' in','')
from cte
But this really does make the point that @OriginalGriff made and which I quoted above!