CODEWITHSUNDEEP
sqlmysqldatabasepostgresql
xRobot
xRobot

Reputation: 26567

Query that ignore the spaces

What's the best way to run a query so that spaces in the fields are ignored? For example, the following queries:

SELECT * FROM mytable WHERE username = "JohnBobJones"    
SELECT * FROM mytable WHERE username = "John Bob Jones"

would find the following entries:

John Bob Jones
JohnBob Jones
JohnBobJones

I am using php or python but I think this doesn't matter.

Upvotes: 39

Views: 90076

Answers (6)

Mquinteiro
Mquinteiro

Reputation: 1082

The proposed solution look very well but is horrible for performance, if it is possible restrict the query with something like:

SELECT * FROM mytable 
    WHERE username like 'John%' and REPLACE(username, ' ', '') = REPLACE("John Bob Jones", ' ', '')

Also you can use REGEXP:

SELECT * FROM mytable 
    WHERE username REGEXP '^John *Bob *Jones'

And remember the performance, operation in the where are in general bad idea.

Take a look to http://dev.mysql.com/doc/refman/5.7/en/pattern-matching.html

Upvotes: 2

Adam111p
Adam111p

Reputation: 3727

We often want to search for text, regardless of the number of spaces, white space and letters.

Just trim , lower case , and replace all multipe Non-word characters for one space. 

SELECT regexp_replace(trim(lower('Here is              a            long               text               , with            many                 white spaces         AND             different                 character              sensitive')),'\W+',' ','g') t

return :here is a long text with many white spaces and different character sensitive

Here is the use for search. Only the order of words is important, nothing more.And this is beautiful.

select * from (
SELECT regexp_replace(trim(lower('Here is              a            long               text               , with            many                 white spaces         AND             different                 character              sensitive')),'\W+',' ','g') t 
) as o
where t= regexp_replace(trim(lower('Here  is a LonG      TEXT , with            mANY white   ^    spaces         AND           different  character              sensiTive')),'\W+',' ','g')

return:here is a long text with many white spaces and different character sensitive

Garbage in data and junk in the query, but it still finds it right.

Upvotes: 0

Mathusuthanan
Mathusuthanan

Reputation: 117

TRY THIS:

SELECT * FROM mytable WHERE username =REPLACE("John Bob Jones", ' ', '')

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 838696

It depends. If you don't care about good performance then there are lots of things you could do but most of them will be slow. Maybe that's OK for you, but I will leave this answer here in case others reading do want a fast solution.

If you want very fast performance you should index the string without spaces in the database. In PostgreSQL you can create an index on a function. You can use this to create an index on the column with spaces replaced with the empty string. The advantage of this method is that it requires no maintenance apart from creating the index.

In MySQL you cannot do this so the simplest way would be to duplicate the data in the database - once with spaces and once without. Use the column without spaces in your WHERE clause, but the original column in your SELECT column list. This requires more maintenance as the columns must be kept in sync. You can do this with application logic or database triggers.

Upvotes: 14

QB.
QB.

Reputation: 15

One way would be to use LIKE and WildCards to build your query citeria. Something like:

SELECT * FROM mytable WHERE username LIKE 'JohnBobJones';

Upvotes: -6

SLaks
SLaks

Reputation: 887777

SELECT * FROM mytable 
    WHERE REPLACE(username, ' ', '') = REPLACE("John Bob Jones", ' ', '')

Upvotes: 78

Related Questions