Skip to Main Content
  • Questions

  • Query using Regular Expressions- Need Help

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Sriram.

Asked: August 16, 2019 - 1:12 pm UTC

Last updated: August 19, 2019 - 2:18 pm UTC

Version: 10g

Viewed 1000+ times

You Asked

Hi Tom

I have a query that I am trying to replace non white space characters in a given string that may have whole numbers or decimals with a single space (where non spaces are there , examples are below) and there are possible strings that have multiple or one whitespace( the one whitespace doesn't have to be replaced)

Strings that does not have whitespace a single space needs to be inserted also the ones inside the brackets as well for e.g below (can be a combination of whole numbers, decimals and whole string in a single column)

E.g:-

80.0 LBS(50KG) should be 80.0 LBS (50 KG)
102mm Dia) should be 102 mm Dia with the ) removed (typo issue)
610mm should be 610 mm
<= 356mm/14in should be <= 14 Inch(356 mm)

I tried the following but it does not work

 SELECT Value as DisplayValue,
     CASE WHEN (TRIM(Value) IS NOT NULL AND
          REGEXP_LIKE(Value,'[(|[][:space:]*[a-zA-Z.-;,=0-9[:space:]]*\s*(\s*MM|IN|IPM|KG|M|LB|EA|FT|DEG|N|FPM|S|LBS\s*)\s*[:space:]*[]|)]','i'))
          THEN REGEXP_REPLACE(EPE.DISPLAYVALUE, '([0-9](MM|IN|IPM|KG|M|LB|EA|FT|DEG|N|FPM|S|LBS)*[-/]\s*[0-9]*[.0-9]*\s*(MM|IN|IPM|KG|M|LB|EA|FT|DEG|N|FPM|S|LBS))','\1 \2')  -- for whitespace sinhMM|IN|IPM|KG|M|LB|EA|FT|DEG|N|FPM|S Square Brackets
         
    FROM Table;


Also I have another question, Is it possible to replace as for the following example above <= 356mm/14in to <= 14 Inch (356 mm) (I capital letters in "Inch") with single whitespace and the spaces inside the brackets the format output ?

and Chris said...

It sounds like you're trying to fix data entry errors using regular expressions. This is always shaky ground, as the risk of false positives and negatives is high. Trying to fix everything with one giant regular expression is a sure way to madness.

Sure, you could do the replacement with something like:

select regexp_replace (
         '356mm/14in',
         '([0-9]+)mm.([0-9]+)in',
         '\2 Inch (\1 mm)'
       ) regex
from   dual;

REGEX              
14 Inch (356 mm)


But you risk this either being:

- Too specific, so you need to write more regexes to handle other mismatches
- Too broad, so it accidentally changes values which happen to match

Ultimately you're going to have to spend a lot of time double-checking the changes; at some point it's quicker just to change the data manually!

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Sriram B, August 16, 2019 - 4:25 pm UTC

Thank you for your response

With regard to your query I understand this is sure a data mess operation but we are trying to fix legacy code written in the past (DB operations done in the past).Of course it sounds frustrating.. Also i noticed that there are fractional digits and integers whole numbers so for e.g
1 1/8in (28.6mm) or <= 1/2in (12.7mm) the query 

select regexp_replace (
         '356mm/14in',
         '([0-9]+)mm.([0-9]+)in',
         '\2 Inch (\1 mm)'
       ) regex
from   dual;

REGEX              
14 Inch (356 mm)


may not work. I tried with 

select regexp_replace (
         value,'(\d+[\/\d. ]*|\d)in.((\d+[\/\d. ]*|\d))mm',
         '\1 Inch (\2 mm)'
       ) regex
from   dual;


but it didnt work. Am I missing something?Secondaly you have hardcoded in and mm but what if the unit of measure was kg and lbs and we need to switch them ,write in standard format, is it possible to modify the above query given by you to include a "Or" condition?
Chris Saxon
August 19, 2019 - 2:18 pm UTC

Well for starters the examples have mm inside parentheses "1/2in (12.7mm)", and the regex searches for mm outside the brackets. And you need to escape the parenthesis you want to match!

But seriously, you're starting down a rabbit hole here. As soon as you start trying to make this handle every exception for every unit you'll hit exceptions galore.

Fix the problems one-by-one. And accept you'll almost certainly have a bunch to resolve at the end by hand!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.