Postgresql pattern match string columns for a number

Question

Given a table of measurements as strings (i.e. "4.6 Inches, 3mm", "Length: 4.66in", etc), I'd like to retrieve rows with essentially anything left or right of the number, so long as the number matches.

In other words, if I'm searching for "6":

match: "6 in", "6in", "asdf 6 in", etc
don't match: "16 in", "6.6 in", "with 66 in", etc

I have the following query thus far:

select distinct length from items where length ~ '[[:<:]]6[[:>:]]';

But it's not quite what I'm looking for, as this is the result:

     length      
---------------
 6.25 Inches
 4.6666 Inches
 6.5 Inches
 1.66 Inches
 6 Inches
 6.75 Inches
 6.71 Inches
 24.6 Inches
 3.6666 Inches

I'm using PostgreSQL 9. Any help would be appreciated. Thanks

Draco Ater · Accepted Answer · 2011-05-25 20:13:55Z

1

As I understood, you want exact number match in string. So the number (6 in your case) cannot be surrounded by numerics or decimal sign. So the regex should look like that:

[^0-9\.]6[^0-9\.]

You can change the 6, to any number you are looking for.

answered May 25, 2011 at 20:13

Draco Ater

21.3k8 gold badges66 silver badges88 bronze badges

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

Comments

Toto · Accepted Answer · 2011-05-26 09:23:08Z

0

How about :

'^[^0-9]*6[^0-9]*$'

answered May 26, 2011 at 9:23

Toto

91.7k63 gold badges97 silver badges135 bronze badges

Comments

Seth Robertson · Accepted Answer · 2011-05-25 20:11:09Z

0

create table foo (length text);
insert into foo values ('6 in'), ('asdf 6 in'), ('6in'), ('16 in'), ('6.6 in'), ('6.6'), ('with 66 in');
select distinct length from foo where length ~ '[^0-9 .][0-5.7-9 ]*6|6*[0-5.7-9 ]*[^0-9 .]';

Edited to work correctly with ' 6 ' and '6.6'.

answered May 25, 2011 at 20:11

Seth Robertson

31.7k8 gold badges67 silver badges63 bronze badges

Comments

Denis de Bernardy · Accepted Answer · 2011-05-25 20:22:17Z

0

No offense, but storing this as a varchar is not a good idea.

This should be stored as two separate fields (quantity / units), or (better yet) a single field (quantity with a predefined unit, e.g. mm), and queried accordingly.

answered May 25, 2011 at 20:22

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

1 Comment

Lock Reck Over a year ago

Absolutely agree. Problem is the data came from an outside source in this format and it would need to be split, thus the need for this pattern matcher.

Collectives™ on Stack Overflow

Postgresql pattern match string columns for a number

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest