CODEWITHSUNDEEP
regexpostgresql
Lock Reck
Lock Reck

Reputation: 23

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 number matches.

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

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

Upvotes: 2

Views: 6549

Answers (4)

Toto
Toto

Reputation: 91430

How about :

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

Upvotes: 0

Denis de Bernardy
Denis de Bernardy

Reputation: 78463

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.

Upvotes: 0

Draco Ater
Draco Ater

Reputation: 21226

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.

Upvotes: 1

Seth Robertson
Seth Robertson

Reputation: 31451

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

Upvotes: 0

Related Questions