SQL Search query ideas

[silentmelodies]

I am developing an SQL Search system for a website. I need some ideas.

The idea is to take the name from the item from the SQL database that looks like this:

"Awesome title of something"

Then strip the spaces.

"Awesometitleofsomething"

And then convert to all lowercase.

"awesometitleofsomething"

And then do a search query for example. "Awesome Title"

 

And do the process again for the query, so it would be:

"awesometitle"

Then compare the database and my search input, and show matches.

"awesometitleofsomething"

Got any more ideas?

[cynexit]

Levenshtein distance is commonly used to find similar strings (or other faster, but similar implementations, but levenshtein is easy to understand and gets the point across). There are also ways to get it into your sql query but of course this is very costly.

 

Also, the way you proposed will be very costly too since you need to perform string operations on each table you want to search. No problem if you are doing a "hobby project", but just know that this will reach it's limitation at some point.

 

If you don't care about performance at all I would highly suggest to have a look at levenshtein in combination with like and %.

[Blade of Grass]

Not sure if you're looking more for a solution or more for something to program, but if not the latter, I would suggest looking into MySQL full text search.