CODEWITHSUNDEEP
sql
Sofia Chaves
Sofia Chaves

Reputation: 37

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%'

Upvotes: 0

Views: 157

Answers (3)

Pouya Kamyar
Pouya Kamyar

Reputation: 133

Remember to cast your column or parameter to nvarchar:

like this:

SELECT * FROM [Table] WHERE [nome] LIKE '%' + CAST(@YourFieldOrParameter AS NVARCHAR(MAX)) + '%'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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 <> ''

Upvotes: 2

Joe C
Joe C

Reputation: 3993

This will return rows where the field nome is empty if the search string is empty. If no rows have an empty [nome] then no rows are returned.

SELECT * FROM [Table] WHERE [nome] Like Case When @search = '' Then '' Else '%' + @search + '%' end

Upvotes: 0

Related Questions