Ignoring spaces between words - an SQL question

Question

I want to grab from a db a record with name='John Doe'.

I'd like my query to also grab 'John(4 spaces between)Doe','John(2 spaces betwewen)Doe' etc. (at least one space however).

I'd also like that the case won't matter, so I can also get 'John Doe' by typing 'john doe' etc.

Answer 1

Try this:

SELECT * FROM table WHERE lower(NAME) like 'john%doe%'

use like with wildcards (e.g. %) to get around the spaces and the lower (orlcase) to be case insensitive.

EDIT:
As the commenters pointed out, there are two shortcomings within this solution.

First: you will select "johnny doe" or worse "john Eldoe", or worse, "john Macadoelt" with this query, so you'll need extra filtering on the application side.

Second: using a function can lead to table scans instead of index scans. This may be avoided, if your dbms supports function based indexes. See this Oracle example

Answer 2

In sql you can use wildcards, that is a character that stands in place of other characters. for example:

select * from table where name like 'john%doe'

will select all names that start with john and end with doe no matter how many characters in between.

This article explains more and gives some options.

Answer 3

If your database has Replace function

Select * From Table
Where Upper(Replace(name, ' ', '')) = 'JOHNDOE'

The rest of these will return rows where the middle part between John and Doe is anything, not just spaces...

if your database has left function and Reverse, Try either

Select * From Table
Where left(Upper(name), 4) = 'JOHN' 
   And Left(Reverse(Upper(name), 3)) = 'EOD'

else use substring and reverse

Select * From Table
Where substring(Upper(name), 4) = 'JOHN' 
   And substring(Reverse(Upper(name), 3)) = 'EOD'

or Like operator

Select * From Table
Where Upper(name) Like 'JOHN%DOE'

Answer 4

The wildcard will match on zero characters, so if you want at least one space then you should do

select * from table where lower(name) like 'john %doe'

Answer 5

In Oracle you can do:

SELECT * FROM table
WHERE UPPER(REGEXP_REPLACE(NAME,'( ){2,}', ' ') = 'JOHN DOE';

No false positives like 'john mordoe' etc.