CODEWITHSUNDEEP
sqloracle-database
mosca1337
mosca1337

Reputation: 2429

Searching names with SQL

I am trying to return people from a search string that can consist of any variation of a name, such as: 'john', 'john smith', 'smith, john', 'john jason smith', or any other common variation. I can cleanup the string and format as a regular expression if need be.

I am aiming for something similar to this:

SELECT firstname, 
       middlename, 
       lastname 
FROM   people 
WHERE  firstname LIKE 'search' 
        OR middlename LIKE 'search' 
        OR lastname LIKE 'search';

The problem is that a search string such as 'john smith' will return all johns and all smiths instead of only john smith. Any ideas?

Upvotes: 0

Views: 109

Answers (3)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60272

Given your particular requirements (match on any of 'john', 'john smith', 'smith, john', 'john jason smith'), this is what I'd try:

SELECT firstname, 
   middlename, 
   lastname 
FROM   people 
WHERE  (firstname || ' ' || lastname) LIKE :search
    OR (lastname || ', ' || firstname || ' ' || middlename) LIKE :search
    OR (firstname || ' ' || middlename || ' ' || lastname) LIKE :search;

(I've added parentheses for clarity, although they're not required in this case)

Upvotes: 0

Carth
Carth

Reputation: 2343

If you want to only return rows that meet all of your supplied criteria then you need to add those filter criteria. There are a couple of ways to do it but you could do something as simple as this:

  • Split up your term into the searchable tokens
  • Make your search case insensitive by forcing everything to upper case
  • Use INTERSECT to only return those rows that match all your tokens
  • Be sure to use wildcarding if desired - In this example token1 = '%JOHN%' and token2 = '%SMITH%'

SQL:

SELECT firstname, 
       middlename, 
       lastname 
FROM   people 
WHERE  ( Upper(firstname) LIKE token1 
          OR Upper(middlename) LIKE token1 
          OR Upper(lastname) LIKE token1 ) 
INTERSECT 
SELECT firstname, 
       middlename, 
       lastname 
FROM   people 
WHERE  ( Upper(firstname) LIKE token2 
          OR Upper(middlename) LIKE token2 
          OR Upper(lastname) LIKE token2 );

Upvotes: 1

chris_techno25
chris_techno25

Reputation: 2477

Try this dude. I tested this against SQL server and it works. Make the necessary change for it to work against Oracle. I've only tested this with 1 field name. Just edit the code so it suits your needs. It's basically @Carth 's solution implemented only using SQL.

 DECLARE @stringsearch varchar(255)='stringvalue'
    while CHARINDEX('  ',@stringsearch) > 1            
      begin            
        SET @stringsearch=REPLACE(@stringsearch,'  ',' ')            
      end   
 SET @stringsearch=REPLACE(@stringsearch,' ','%'' AND fieldname LIKE ''%')+'%'''
 DECLARE @sql varchar(max)=''
 SET @sql='SELECT fieldname FROM TableName
 WHERE fieldname Like ''%'+ @stringsearch
 EXEC (@sql)

Upvotes: 0

Related Questions