Proper regular expression in PostgreSQL

Question

I have a table called regions and column region which is not empty. There is a text in it, which is 'Tashkent'. I am trying to access it with expression input. Here is my code:

select * from regions where lower(region) ~ '[t]{1,}[k]{1,}';

As you can see, I am trying to get the word which contains letters t and k consecutively. According to my expression: [t]{1,}[k]{1,} above it should return text 'Tashkent', but not returning. Is my code correct. Can you suggest an alternative code to get word 'Tashkent' knowing that I have letters t and k?

Clodoaldo Neto · Accepted Answer · 2016-06-27 14:39:30Z

1

You need to specify the characters between t and k

select lower('Tashkent') ~ 't{1,}.*k{1,}';
 ?column? 
----------
 t

answered Jun 27, 2016 at 14:39

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

3 Comments

Shohruh Abduakhatov Over a year ago

Yes, working. Thank you so much. I looked for much sources to know more about POSIX and expressions separately; even documentation of PSQL, but not much there. Can you please suggest any source to lean? Thank you one more time.

Clodoaldo Neto Over a year ago

@ShohruhAbduakhatov: The term to search for is regular expression

Clodoaldo Neto Over a year ago

@ShohruhAbduakhatov: Postgresql manual

Rémy Baron · Accepted Answer · 2016-06-27 15:06:12Z

1

1) If i understand what you want regex is not necessary :

     select * from regions where region ilike '%t%k%'

2) if you want absolutely do this with regex :

your request : 't{1,}.*k{1,}' return true only if you have "tk" in your string for example "azerty ttk qwerty".
A good solution is Clodoaldo Neto's solution : lower(region) ~ 't{1,}.*k{1,}'
You can also remove lower function with region ~ '[tT]{1,}.*[kK]{1,}'
and finally {1,} can be replaced by + : region ~ '[tT]+.*[kK]+'

answered Jun 27, 2016 at 15:06

Rémy Baron

1,4099 silver badges15 bronze badges

1 Comment

Shohruh Abduakhatov Over a year ago

Nice. Good answer. Thanx, much useful things to learn in a single answer. Thanx a lot

Collectives™ on Stack Overflow

Proper regular expression in PostgreSQL

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related