concatenate ' and % in like estatement

Question

Post reply

concatenate ' and % in like estatement

masoudk1990

SSCommitted

Points: 1651
More actions
August 5, 2013 at 3:41 am

#275854

Hi, I have a stored procedure for search data.
I want add % to it, so we are not have to search with exact values.
I already tried something like N'' + % + @name + % + '' but it throw exception.
This code works fine with exact values.
PROCEDURE search(@name nvarchar(20)=null)
select * from TableName
where FirstName like N'' + @name + ''
Thank you for help.
___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

kapil_kk SSC-Insane Points: 21316 More actions · Answer 1

Try in this manner-

DECLARE @name VARCHAR(20) = 'S'

SELECT * FROM Person.Person WHERE LastName LIKE '%'+@name+'%'

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

masoudk1990 SSCommitted Points: 1651 More actions · Answer 2

Thank you very much but 'N' character in condition is critical because i search none English.

I need something like:

DECLARE @name VARCHAR(20) = '?????'

SELECT * FROM Person.Person WHERE LastName LIKE N'%'+@name+'%'

Result must look like N'%?????%'

___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

masoudk1990 (8/5/2013)
Thank you very much but 'N' character in condition is critical because i search none English.
I need something like:
DECLARE @name VARCHAR(20) = '?????'
SELECT * FROM Person.Person WHERE LastName LIKE N'%'+@name+'%'
Result must look like N'%?????%'

Then change the datatype VARCHAR to NVARCHAR

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

masoudk1990 SSCommitted Points: 1651 More actions · Answer 4

kapil_kk (8/5/2013)
Then change the datatype VARCHAR to NVARCHAR

I changed my datatype from VARCHAR To NVARCHAR

But N character is critical in none English statements.

I need a query to generate this:

N'%?????%'

Your query generate '%'?????'%' and it don't work proper.

___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.

masoudk1990 SSCommitted Points: 1651 More actions · Answer 5

Solved!

The trick is generate % with char function: 😀

PROCEDURE search(@name nvarchar(20)=null)

select * from TableName

where FirstName like N'' + char(37) + @name + char(37) + ''

note: char(37) = %

___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.