sql string extraction

Question

I have a column in sql server 2008 which is a varchar(255). The column is full of 'measurements' and there are around 15,000 records. As the column is of type string and has been manually inputted by human it has become messy e.g.

100x200cm
200x400mm
3" by 9"
30 x 20
12cm long

Basically I need to extract all measurements (that I can) into usable dimensions and store each unit into a new column (height/width/depth).

Can anyone suggest the best way to go about doing this?

I was looking at regular expression but not sure on the best way forward

Good luck on that one. I wouldn't do it in SQL anyway: way too many combinations to parse. — gbn
– gbn, Commented Jul 31, 2011 at 21:59

Shi · Accepted Answer · 2011-07-31 22:10:09Z

1

I recommend a very strict parser which throws an exception whenever it encounters something it cannot handle explicitly. This way you are save from surprises.

First of, you should use a scripting language for that.

Fetch the records and iterate over them and for each record you try to parse it:

^([1-9][0-9]*)x([1-9][0-9]*)([a-z]+)$ - if it matches, you match $3 against a list of valid units, for example "cm", "mm", "m", etc.
^([1-9][0-9]*)([^ ]+) by ([1-9][0-9]*)(.+)$ - if it matches, compare the units at match $2 and $4. Match them against a list of valid units again, and if that's successful, but they are not the same, convert one into the other.

You keep adding strict checks for each item which throws an exception. This way, your list of expressions expands with the cases you handle.

edited Jul 31, 2011 at 22:10

answered Jul 31, 2011 at 22:04

Shi

4,2581 gold badge28 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

gbn Over a year ago

Note: SQL Server doesn't support regex in T-SQL

Shi Over a year ago

That's why I noted to use a scripting language.

gbn Over a year ago

Sorry it was for OP, not for you. Oracle has regex and some folk get mixed up... :-)

Collectives™ on Stack Overflow

sql string extraction

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related