Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 235
xRobot Avatar asked Apr 25 '10 20:04

xRobot


People also ask

How do I ignore a space in SQL query?

The TRIM() function removes the space character OR other specified characters from the start or end of a string. By default, the TRIM() function removes leading and trailing spaces from a string.

Does SQL ignore trailing spaces?

Takeaway: According to SQL Server, an identifier with trailing spaces is considered equivalent to the same identifier with those spaces removed.

Does SQL care about spacing?

Whitespace is optional in pretty much any language where it is not absolutely necessary to preserve boundaries between keywords and/or identifiers. You could write code in C# that looked similar to your SQL, and as long as the compiler can still parse the identifiers and keywords, it doesn't care.


4 Answers

SELECT * FROM mytable      WHERE REPLACE(username, ' ', '') = REPLACE("John Bob Jones", ' ', '') 
like image 181
SLaks Avatar answered Oct 03 '22 00:10

SLaks


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.

like image 20
Mark Byers Avatar answered Oct 03 '22 01:10

Mark Byers


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

like image 30
Mquinteiro Avatar answered Oct 03 '22 02:10

Mquinteiro


TRY THIS:

SELECT * FROM mytable WHERE username =REPLACE("John Bob Jones", ' ', '')
like image 27
Mathusuthanan Avatar answered Oct 03 '22 00:10

Mathusuthanan