How can I do wildcard searching with underscores with POSTGRES?

Question

I'm trying to find all rows that have multiple underlines in them separated by some characters, like: The boys ____ to the store to ___ up some groceries

I tried SELECT * FROM "Questions" WHERE "questionText" LIKE '%_%_%' but this seems to return everything.

Can you be more specific? Would 'x__x' qualify? How about '______ x' or 'x _'? — mu is too short
– mu is too short, Commented Jun 30, 2016 at 20:39
Hi _, this is _. would qualify More than one ____ would not qualify Just one _ would not qualify — Shamoon
– Shamoon, Commented Jun 30, 2016 at 20:43

dmitryro · Accepted Answer · 2016-06-30 21:05:54Z

2

You can use :

  SELECT * FROM questions WHERE questiontext ~ '[ $][_][$ ]';

for the case of 2 elements containing space character and underscore:

  SELECT * FROM questions where questiontext ~ '^[^_]+_([^_]+)_[^_]+$';

edited Jun 30, 2016 at 21:05

answered Jun 30, 2016 at 19:58

dmitryro

3,5162 gold badges23 silver badges30 bronze badges

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

7 Comments

Shamoon Over a year ago

What does the ~ do? and the [?

dmitryro Over a year ago

It's a regular expression - a class that ~ passes to evaluate. See regular expressions in postgresql.

Shamoon Over a year ago

This returns things like She was ____ because she had only gotten three hours of sleep.. I only want to return things that have an underline separated by non-underlined character, such as: She was ____ because she had only gotten three hours of __.

dmitryro Over a year ago

You add more classes to the regexp [^$][___][^$] - ^$ means empty string

Shamoon Over a year ago

Well.. to be clear, I want ONE and only one _. So... Jack went to the _ to pick up some _.

|

Collectives™ on Stack Overflow

How can I do wildcard searching with underscores with POSTGRES?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related