LIKE 运算符、N 和 % SQL Server 不适用于 nvarchar 列 [英] LIKE operator, N and % SQL Server doesn't work on nvarchar column

问题描述

有什么方法可以使以下查询有效吗?

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

我不能这样使用:

 Where [pName] like N'حس%'

或者使用存储过程:

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

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

推荐答案

您不需要在 WHERE 子句中使用 N 前缀，因为您的变量已经 nvarchar，并且您传递的是一个变量而不是文字字符串.

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.

这是一个例子:

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

现场演示

您可能已经看到 Transact-SQL 代码使用 N 前缀传递字符串.这表示后续字符串是 Unicode(N 实际上代表国家语言字符集).这意味着您传递的是 NCHAR、NVARCHAR 或 NTEXT 值，而不是 CHAR、VARCHAR 或 TEXT.

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.

来自 文档

用字母 N 前缀 Unicode 字符串常量.如果没有 N 前缀，字符串将转换为数据库的默认代码页.此默认代码页可能无法识别某些字符.

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.

<小时>

要在评论中用简单的答案回答您的问题，您使用了错误的数据类型，因此 ALTER 存储过程并从 VARCHAR 更改参数的数据类型到 NVARCHAR.

---

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.

更新:

由于您使用的是 SP，因此您可以创建您的 SP(根据您的 评论) as

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

并称之为

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

如果您看到，当您将文字字符串传递给不在 SP 内的 SP 或 WHERE 子句时，您需要使用 N 前缀.

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.

SP 演示

这篇关于LIKE 运算符、N 和 % SQL Server 不适用于 nvarchar 列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！