Sofia Chaves Sofia Chaves - 1 month ago 12
SQL Question

SQL - How create a select that makes search possible

I was trying to create a search where you could search by typing some part of the name. But when the search input is empty it shows everyone in the table.
I tried to write the SQL to select * when the search input is = to " "(empty) and to select * that has at least part of the sentence when the search as 1 character or more, but everytime if i write something it works, but when the search input is empty any row is selected. My code is:

SELECT * FROM [Table] WHERE [nome] LIKE '%@search%'

Answer

Assuming you are using SQL Server:

WHERE [nome] LIKE '%' + coalesce(@search, '') + '%'

However, you might consider full text search using contains().

EDIT:

Ooops, I thought you wanted the query to return everything when the search string is empty. How about:

WHERE [nome] LIKE '%' + @search + '%' AND @search <> ''
Comments