如何识别sql中的unicode文本?

2022-01-03 00:00:00 sql sql-server sql-server-2012

Table1 有名为 umsg 的 nvarchar 列，其中包含 unicode 文本和一些时间的英语.

Table1 has nvarchar column called umsg which contains unicode text and some time english also.

我想找出 umsg 列中的英文文本.

I want to find out English text present in umsg column.

select * 
from table1 
where 
    RDate >='01/01/2014' and RDate < '09/26/2017' 
    and umsg = convert(varchar(max), umsg)

我使用了上面的查询，在区域语言中可以正常工作，但有时会失败.假设 col 包含类似 'ref no été' 的文本我认为上面的消息是 unicode，如果我使用上面的查询，它/sql 显示我是英语而不是 unicode.如何处理这个.

I used above query that work fine in regional language but some time fail. Suppose col contain text like 'ref no été' I think above message is unicode, if I used above query, it/sql is showing me as English not unicode.How to handle this.

Table :
Id  Date                      Umsg
1   2017-09-12 00:00:00.000   The livers detoxification processes.
2   2017-09-11 00:00:00.000   Purposely added 1 
3   2017-09-10 00:00:00.000   फेंगशुई के छोटे-छोटे टिप्स से आप जीवन की विषमताओं से                       स्वयं को बचा सकते
4   2017-09-17 00:00:00.000    तनाव एक लाइलाज बीमारी कतई नहीं है। कुछ लोग तनाव को                                     आसानी से झेल लेते ह
5   2017-09-17 00:00:00.000    ref no été

以上是我的表格中的数据.但我想要像这样的数据/输出:

Above is data present in my table. But I want data/Output like :

    Id      Date                      Umsg
    1   2017-09-12 00:00:00.000   The livers detoxification processes.
    2   2017-09-11 00:00:00.000   Purposely added 1

推荐答案

检查下面:

;WITH CTE
 AS (
 SELECT ID,
        DATE,
        umsg,
        CASE
            WHEN(CAST(umsg AS VARCHAR(MAX)) COLLATE SQL_Latin1_General_Cp1251_CS_AS) = umsg
            THEN 0
            ELSE 1
        END HasSpecialChars
 FROM <table_name>)
 SELECT ID,
        DATE,
        umsg
 FROM CTE
 WHERE Date >= '01/01/2014'
       AND Date < '09/26/2017'
       AND HasSpecialChars = 0;

期望输出:

ID  DATE                     umsg
1   2017-09-12 00:00:00.000  The livers detoxification processes.                                                                     
2   2017-09-11 00:00:00.000  Purposely added 1

希望能帮到你.

相关文章