Search same field twice

[smartLIZARD]

I am using a Stored Procedure. In my database I have a field 'FullName', Problem is the data is not consistantly entered (there are millions of records.) Sometimes in the FullName field the data is formatted 'Sell, Ron E.' and other times 'Sell Ron E.'.

What I need to do is search by entering "Ron" and "Sell" and searching the field. This is what I am doing now

SELECT *
FROM tbl
WHERE FullName LIKE '%Ron%' AND FullName LIKE '%Sell%'

Is there a better way since there are so many records?

Thanks.

 

[tlbroadbent]

If your name isn't formatted the same way or if you are going to search on part of the name, you probably won't find anything more efficient than what you have written.

If the last name is always at the start of the column then a slight modification may yield improved performance. Remove the leading wildcard (%) in the criteria. SQL may then use an index rather than a table scan.

SELECT *
FROM tbl
WHERE FullName LIKE '%Ron%' AND FullName LIKE 'Sell%'

I suggest updating the table to standardize the format of the data. For example, you could remove the extra spaces very easily.

Update Table
Set Name=Replace(Name, ' ', ' ')
Where Name Like '% %'

You may need to execute this script multiple times to eliminate all extra spaces.

You can also search for rows without commas and insert a comma in the name.

Update Table
Set Name=Stuff(Name, charindex(' ', Name), 1, ', ')
Where name Not Like '%,%' Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[moonshadow]

I think Terry's suggestions are the best but if you can't do those changes for some reason, you may additionally want to consider a Full-Text Index (see BOL)