Postgresql pattern match string columns for a number

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 num开发者_JAVA百科ber 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

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.

How about :

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

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

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.