判断字符串含有汉字

需求描述

需求：检索含有汉字的字符串.

解决方法:这里通过函数CHAR_LENGTH对比LENGTH进行对比来判断.

注: 数据库数据集SQL脚本详见如下链接地址

​​员工表结构和数据初始化SQL脚本​​

SQL代码

--Mysql:
SELECT data,LENGTH(data) AS Len_data,CHAR_LENGTH(data) AS CharLen_Data,HEX(data) AS HexData FROM
(
SELECT 'Hello,World,SQL' AS data
    UNION ALL
SELECT 'Data,Arithmetic' AS data
    UNION ALL
SELECT 'Science中国' AS data
    UNION ALL
SELECT '上S海H' AS data   
)A
WHERE LENGTH(data)<> CHAR_LENGTH(data)

执行结果

处理字符串_8_判断字符串含有汉字_Data

/*
注:
LENGTH() returns the length of the string measured in bytes. 
CHAR_LENGTH() returns the length of the string measured in characters.
LENGTH:是计算字节的长度.一个汉字是算三个字符,一个数字或字母算一个字符
CHAR_LENGTH:汉字、数字、字母都算是一个字符*/

-- 或者通过字符串的十六进制并结合REGEXP来判断.
SELECT data,HEX(data) AS HexData FROM
(
SELECT 'Hello,World,SQL' AS data
    UNION ALL 
SELECT 'Data,Arithmetic' AS data
    UNION ALL
SELECT 'Science中国' AS data
    UNION ALL
SELECT '上S海H' AS data    
)A 
WHERE HEX(data) REGEXP '^(..)*(E[4-9])'

处理字符串_8_判断字符串含有汉字_SQL_02

--SQL Server:
SELECT data FROM
(
SELECT 'Hello,World,SQL' AS data
    UNION ALL 
SELECT 'Data,Arithmetic' AS data
    UNION ALL
SELECT 'Science中国' AS data
    UNION ALL
SELECT '上S海H' AS data    
)A 
WHERE data LIKE  '%[吖-座]%' 

--或者利用PATINDEX函数进行判断

SELECT data  FROM
(
SELECT 'Hello,World,SQL' AS data
    UNION ALL 
SELECT 'Data,Arithmetic' AS data
    UNION ALL
SELECT 'Science中国' AS data
    UNION ALL
SELECT '上S海H' AS data    
)A
WHERE PATINDEX('%[吖-座]%',data) > 0

处理字符串_8_判断字符串含有汉字_SQL_03