2

Is it possible to query records based on a string column while ignoring spaces in the stored value? For instance, if the value is Hello World, the query would be checking for HelloWorld. If possible, does this slow the query down noticeably?

I know I could store two values when the records are stored, one with the string as entered and one with spaces removed. Then I could query the string value with no spaces. But am wondering if the above is possible as it seems cleaner and easier.

Thanks for looking

Improve this question
1

1 Answer 1

Reset to default
2

You can use the Postgres replace function to remove spaces and then match on that value. 

where("replace(column, ' ', '') = 'value'")

This will have some performance impact for sure, if it becomes a problem you can create an index on this function and that will speed up the search.

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

3 Comments

I've seen this but I was under the impression that it removed the spaces in the string you are telling it to search for i.e. 'value' then queries the db for that. Does it actually remove the spaces from each stored value then compare it to the string you're looking for? If so, can you get it to ignore or replace capitals too?
This is temporary, it just removes the spaces when comparing. The actual data stored in the table is not modified. You can lowercase using the lower function: lower(column)
@AlD You can use an expression index. I suggest writing a sql function to wrap the transformation you want - simplify_string, strip_string or whatever. Then create an expression index on that, and use it in the queries. Details in the PostgreSQL manual.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.