postgresql - check if a row contains a string without considering spaces

Question

Is it possible to check if a row contains a string without conisdering spaces?

Suppose I have a table like the one above. I want to know if the query column contains a string that may have different consecutive number of space than the one stored or vice versa?

For example: the first row's query is select id, username from postgresql, and the one I want to know if stored in the table is:

select id, username 
      from   postgresql

That is to say the one that I want to know if exists in the table is indented differently and hence has different number of space.

Neville Kuyt · Accepted Answer · 2019-06-21 08:47:39Z

1

You can use REGEXP_REPLACE; this will likely be very slow on large data set.

SELECT * from table 
where REGEXP_REPLACE('select id, username  from   postgresql    ', '\s+$', '') =  REGEXP_REPLACE(query, '\s+$', '')

answered Jun 21, 2019 at 8:47

Neville Kuyt

29.6k2 gold badges40 silver badges52 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2019-06-21 10:57:34Z

0

I think you would phrase this as:

where $str ~ replace('select id, username from postgresql', ' ', '[\s]+')

Note: This assumes that your string does not have other regular expression special characters.

answered Jun 21, 2019 at 10:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

mahan Over a year ago

@ Is this the same function as replace(string text, from text, to text)?

Gordon Linoff Over a year ago

@mahan . . . Yes, replace() is replace().

Collectives™ on Stack Overflow

postgresql - check if a row contains a string without considering spaces

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related