LIKE operator, N and % SQL Server doesn't work on nvarchar column

Question

Is there any way to make following query Work?

declare @t nvarchar(20)
set @t='حس'
SELECT  [perno] ,[pName]
  FROM  [dbo].[People]
  Where [pName] like N''+@t +'%'

I cann't use like this:

 Where [pName] like N'حس%'

Or using an stored procedure :

  ALTER PROCEDURE [dbo].[aTest]
  (@t  nvarchar(20))
  AS
    BEGIN

     SELECT     [perno] ,[pName]
       FROM     [dbo].[People]
       WHERE   ([People].[pName] LIKE N'' +@t + '%')
END

`N'` is part of the *string literal* syntax. It changes what type of string literal you're writing. It doesn't magically apply changes to strings obtained through computation or assigned values from completely different literals. — Damien_The_Unbeliever, Jan 09 '19 at 08:58
two words, [injection attack](https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work). Imagine the name your are searching for is `N'haha'');GO TRUNCATE [dbo].[People]; --'` — Jodrell, Jan 09 '19 at 09:48
@Jodrell Where is the opportunity for SQL injection? There is no dynamic SQL present, just string concatenation to create a pattern for `like`. — HABO, Jul 15 '19 at 02:43
@HABO, you may be right, I'd have to try it out but, the dynamic SQL is there `LIKE N'' +@t + '%'` — Jodrell, Jul 15 '19 at 07:59
@Jodrell Concatenating strings is not dynamic SQL. Dynamic SQL uses [`execute`](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/execute-transact-sql?view=sql-server-2017) or [`sp_executesql`](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017) to execute an arbitrary string containing SQL statements. (Or an external method like [`ExecuteSqlCommand`](https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.database.executesqlcommand?view=entity-framework-6.2.0).) — HABO, Jul 15 '19 at 12:03
@HABO, good reference here http://www.sommarskog.se/dynamic_sql.html, but before you read it I think you are probably safe but, I'd still check. — Jodrell, Jul 15 '19 at 12:15

Ilyes · Accepted Answer · 2019-01-09T09:36:29.607

You don't need to use N prefix in the WHERE clause since your variable is already nvarchar, and you are passing a variable not a literal string.

Here is an example:

CREATE TABLE People
(
  ID INT,
  Name NVARCHAR(45)
);

INSERT INTO People VALUES
(1, N'حسام'),
(2, N'حسان'),
(3, N'حليم');

DECLARE @Name NVARCHAR(45) = N'حس';--You need to use N prefix when you pass the string literal

SELECT *
FROM People
WHERE Name LIKE @Name + '%'; --You can use it here when you pass string literal, but since you are passing a variable, you don't need N here

Live demo

You may have seen Transact-SQL code that passes strings around using an N prefix. This denotes that the subsequent string is in Unicode (the N actually stands for National language character set). Which means that you are passing an NCHAR, NVARCHAR or NTEXT value, as opposed to CHAR, VARCHAR or TEXT.

From docs

Prefix Unicode character string constants with the letter N. Without the N prefix, the string is converted to the default code page of the database. This default code page may not recognize certain characters.

To answer your question in the comment with a simple answer, you are using the wrong datatype, so ALTER the stored procedure and change the datatype of your parameter from VARCHAR to NVARCHAR.

UPDATE:

Since you are using an SP, you can create your SP (according to your comment) as

CREATE PROCEDURE MyProc
(
  @Var NVARCHAR(45)
)
AS 
BEGIN
  SELECT *
  FROM People
  WHERE Name LIKE ISNULL(@Var, Name) + '%';
  --Using ISNULL() will return all rows if you pass NULL to the stored procedure
END

and call it as

EXEC MyProc N'حس'; --If you don't use N prefix then you are pass a varchar string

If you see, you need to use the N prefix when you pass literal string to your SP not inside the SP or the WHERE clause neither.

Demo for the SP

correct i don't need N When declare variable but When using an stored procedure with parameter? — jaleel, Jan 09 '19 at 08:59
@jaleel So why you declare your variable with the wrong datatype? — Ilyes, Jan 09 '19 at 09:01
wrong datatype? I see your using of N, think it is the correct place but still have problem on stored procedure — jaleel, Jan 09 '19 at 09:05
@jaleel I think I did answer this in the first quote, if you don't use `N` then SQL Server won't deal with the string as non-unicode _Without the N prefix, the string is converted to the default code page of the database_. Plus I don't see a stored procedure in your question so if this is the case then please edit your question — Ilyes, Jan 09 '19 at 09:07

score 1 · Answer 2 · answered Jan 09 '19 at 09:27

in these lines

declare @t nvarchar(20)
set @t='حس'

the 'حس' is a varchar constant that you then assign to an nvarchar variable. But you already lost data with the original conversion to that varchar constant and you cannot get that back.

The solution is to use an nvarchar constant:

set @t=N'حس'

score 1 · Answer 3 · answered Jan 09 '19 at 09:29

It might be much simpler:

Try this

declare @t nvarchar(20)
set @t='حس';

SELECT @t; --the result is "??"

You are declaring the variable as NVARCHAR correctly. But the literal does not know its target. Without the N it is taken as a VARCHAR with the default collation.

The following line

Where [pName] like N''+@t +'%'

will search for a pName LIKE '??%'.

The solution should be

set @t=N'حس'; --<-- N-prefix

LIKE operator, N and % SQL Server doesn't work on nvarchar column

3 Answers3

Linked