SQL Server喜欢语句仅在插入一个Unicode字符时起作用（无多个字符起作用）

发布于 2025-02-13 03:34:05 字数 221 浏览 0 评论 0原文

我有一些SQL查询，这样

SELECT ...
FROM ...
WHERE FIELD LIKE N'%ב%'

可以工作。但是，如果我插入更多字符，它甚至没有任何内容（该字段包含'בצע'）

SELECT ...
FROM ...
WHERE FIELD LIKE N'%בו%'

和想法吗？谢谢！

原文

I have some SQL query, like this:

SELECT ...
FROM ...
WHERE FIELD LIKE N'%ב%'

which works fine. but if I insert more characters, it doesn't return anything even it should (the field contains 'בוצע')

SELECT ...
FROM ...
WHERE FIELD LIKE N'%בו%'

And ideas? thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

玻璃人 2025-02-20 03:34:05

作为绝对 last-restort 选项：如果您需要在Unicode-unsafe端到端方案中插入Unicode文本（即某物 in键盘之间目标数据库是处理正确的Unicode编码，或使用其他编码），那么您应该始终能够倒下来使用concat（nchar（），...）仅使用 7-bit ascii-safe t-sql source .. 。

href =“ https://learn.microsoft.com/en-us/sql/t-sql/functions/nchar-transact-sql?view=sql-sql-server-ver16” nchar（）函数 - 无论您如何保存或编码SQL rtl/ltr处理也...）：（

DECLARE @hebrewLikePattern nvarchar(50) = CONCAT( N'%', NCHAR( 1489 ), NCHAR( 1493 ), NCHAR( 1510 ), NCHAR( 1506 ), N'%' );

SELECT
    *
FROM
    tbl
WHERE
    someColumn LIKE @hebrewLikePattern;

由于某种原因，我似乎无法将希伯来文字发布到stackoverflow的情况下，而无需rtl/ltr弄乱 - 所以这是我使用linqpad所获得的char值的屏幕截图）：

As an absolute last-resort option: if you need to insert Unicode text in a Unicode-unsafe end-to-end scenario (i.e. where something in-between your keyboard and the target database is mangling correct Unicode encoding, or using some other encoding) then you should always be able to fall-back to using CONCAT( NCHAR(), ... ) to build a string using only 7-bit ASCII-safe T-SQL source...

You'd replace the literal Hebrew characters in the string-literal with an NCHAR() function - this will work regardless of how your SQL is saved or encoded (don't forget RTL/LTR handling too...):

DECLARE @hebrewLikePattern nvarchar(50) = CONCAT( N'%', NCHAR( 1489 ), NCHAR( 1493 ), NCHAR( 1510 ), NCHAR( 1506 ), N'%' );

SELECT
    *
FROM
    tbl
WHERE
    someColumn LIKE @hebrewLikePattern;

(For some reason I can't seem to post Hebrew text to StackOverflow without RTL/LTR getting messed-up - so here's a screenshot of the char values I got using Linqpad):

回复收藏 0 原文

眼藏柔 2025-02-20 03:34:05

请检查以下内容。

SQL

-- SQL Server 2017 and earlier
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, hebrew_col NVARCHAR(100));
INSERT INTO @tbl (hebrew_col) VALUES
(N'שָׁלוֹם');

SELECT * FROM @tbl;

SELECT * FROM @tbl
WHERE hebrew_col LIKE N'%ל%';

-- SQL Server 2019 onwards
DECLARE @tbl2 TABLE (
    ID INT IDENTITY PRIMARY KEY, 
    hebrew_col VARCHAR(100) COLLATE Latin1_General_100_CI_AI_SC_UTF8);
INSERT INTO @tbl2 (hebrew_col) VALUES
(N'שָׁלוֹם');

SELECT * FROM @tbl2;

SELECT * FROM @tbl2
WHERE hebrew_col LIKE N'%ל%';

Please check the following.

SQL

-- SQL Server 2017 and earlier
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, hebrew_col NVARCHAR(100));
INSERT INTO @tbl (hebrew_col) VALUES
(N'שָׁלוֹם');

SELECT * FROM @tbl;

SELECT * FROM @tbl
WHERE hebrew_col LIKE N'%ל%';

-- SQL Server 2019 onwards
DECLARE @tbl2 TABLE (
    ID INT IDENTITY PRIMARY KEY, 
    hebrew_col VARCHAR(100) COLLATE Latin1_General_100_CI_AI_SC_UTF8);
INSERT INTO @tbl2 (hebrew_col) VALUES
(N'שָׁלוֹם');

SELECT * FROM @tbl2;

SELECT * FROM @tbl2
WHERE hebrew_col LIKE N'%ל%';

回复收藏 0 原文

~没有更多了~