Because you need to query some data in the database, the field content can only contain Chinese characters, letters, and numbers, and cannot contain special symbols (except for special data)
Let's take a look at the effects first, as follows:
Resources:
How database sql queries a field with only numbers and letters or Chinese characters
- Contains only numbers and letters
select column name from table name where REGEXP_LIKE(column name,'^([\u4e00-\u9fa5]+)$')
--Only contains Chinese characters
select column name from table name where length(column name)=length(ASCIISTR(column name))-length(replace(ASCIISTR(column name),'\',''))
Note: The asciistr() function will convert the string into an ASCII value, and if the character is in the ASCII code table, it will be converted into the corresponding character of ASCII; If there are none, such as Chinese and full-width characters, it will be converted to \xxxx format.
The length() function counts that the Chinese length is 1, and the Chinese character corresponding to lengthb() is 2 bytes. The above statement is to compare the number of Chinese characters with the number of \ after converting to ASCII values, if they are equal, it means that they are all Chinese characters. (If you want to exclude the influence of full-width symbols, you need to first change the field to half width, and the function is to_single_byte()).
sqlserver2008r2 looks for the first position where non-Chinese alphanumeric numbers appear
Environment:
The test database sqlserver2008r2 has a sorting method of Chinese_PRC_CI_AS, and I have not tried it in other environments
Statement
Return value: 4
My solution
(End)
|
Posted on 12/7/2018 1:24:31 PM
|
|
|
|
