CODEWITHSUNDEEP
sqlpostgresql
mahan
mahan

Reputation: 14975

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

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

enter image description here

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.

Upvotes: 0

Views: 644

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

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.

Upvotes: 0

Neville Kuyt
Neville Kuyt

Reputation: 29639

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+$', '')

Upvotes: 1

Related Questions