SQL SERVER LIKE statement only works when inserting one Unicode character (doesn't work with mu-CodePudding

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!

CodePudding user response：

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

CodePudding user response：

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'%ל%';