Click here to Skip to main content
15,867,991 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a table which has a column which consists of different pattern of values.

I need to extract certain values from that patern.


input
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)


output
0.3510
1.2210
0.3583
1.0312
0.0180
0.2770
0.1719
0.6562


Please help!!

What I have tried:

Charindex and substring but havent been able to succeed
Posted
Updated 23-Apr-19 23:08pm
v2
Comments
CHill60 24-Apr-19 3:24am    
The "What I have tried" section is for you to put the code that you have tried
Edit - also, which version of SQL are you using?

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
SQL
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
SQL
;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
SQL
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.
SQL
;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!
 
Share this answer
 
v2
Comments
Maciej Los 24-Apr-19 5:08am    
5ed!
Basically, don't store it like that at all: SQL string handling is ... ummm ... poor, at best - and you should decide on a storage type (metric or imperial) and convert the input in your presentation software to that, then store it as a consistent number in a floating point column.
Storing numeric values as strings is just a recipe for extremely painful processing later - particularly when the "numeric value" is free form and not in a defined measurement system.

To leave the storage as you have it at the moment is a nightmare - 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.
Unify and check your inputs; store the data in numeric fields; be consistent. Your life will be a whole load easier in future!
 
Share this answer
 
SQL
DECLARE @table TABLE(INputval NVARCHAR(100)); 

INSERT INTO @table(INputval)

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)')


SELECT SUBSTRING(REPLACE(REPLACE(INputval,'in','  '),')',' '),CHARINDEX('(',INputval)+1,LEN(INputval)) AS INputval  FROM @table
 
Share this answer
 
Comments
chints786 24-Apr-19 5:40am    
Thanks Santosh!! Can you please let me know what if I have data in fraction say

Col1
1/2
2/3
4/5
0.123
0.345
0.222

I want only the fraction part i.e

Col1
1/2
2/3
4/5

Col1
1/2
2/3
4/5
CHill60 24-Apr-19 6:01am    
In this instance, solution 1 applies
Santosh kumar Pithani 24-Apr-19 9:49am    
If you need to filter only fraction('/') values then use charindex('/',columnName)<>0

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900