Search code examples
sqlunicodesql-likenvarchar

Convert sql Non-Unicode parameter to Unicode

I have two records in my person table in my database, their last names are "تحصیلداری" and "موقر".
when I use 

select * from Person where Lastname like N'%ی%'

the record containing "ی" character in last name returns, which is exactly what I need, but I need a little change. I need to use the word between % and % as a parameter.
Something like : 

create procedure usp_GetPersonsWhoseNameContains(@LN nvarchar(50))
as
begin
    select * from Person where Lastname like N'%'+@LN+'%'
end

declare @LastName nvarchar(50) = 'ی'
exec usp_GetPersonsWhoseNameContains(@LastName)

but it does not work this way, and in my searches I couldn't find the appropriate solution. Does anyone know how to

Solution

  • Try 

    create procedure usp_GetPersonsWhoseNameContains(@LN nvarchar(50))
as
begin
    select * from Person where Lastname like N'%'+@LN+'%'
end

declare @LastName nvarchar(50) = N'ی'
exec usp_GetPersonsWhoseNameContains(@LastName)

    Maybe you noticed that SQL Server uses N to any quoted string input when generating DDL scripts for you.