在SQL Server中扩展的ASCII字符搜索 [英] Extended ascii characters search in SQL Server

问题描述

我有一张表，其中一列可能包含包含扩展名的数据 ASCII字符(如♥,♦,♣....)

I have a table where one column may contain data which includes extended ASCII characters (like ♥,♦,♣....)

当我使用select查询搜索相同内容时，结果集无法完全获取 例如:

When I search for the same using select query the result set doesn't fetch exactly for ex:

create table testasci(id int,name varchar(20))

insert into testasci values(1, 'santosh');
insert into testasci values(2, 'santosh♥');
insert into testasci values(3, 'santosh♦');
insert into testasci values(4, 'santosh2');
insert into testasci values(5, 'santoshσ');
insert into testasci values(6, 'santosh3');

当我搜索具有以下扩展ASCII字符的任何名称时

When I search for any name with extended ASCII character like the following

select * from testasci where name = 'santosh♥'

结果集显示id2，id3.

result set displays id2, id3.

也带有问号(?)符号而不是原始字符.

That too with question mark (?) symbol instead of the original character.

有帮助吗?对于以上搜索查询，我的结果集应仅显示id2

Any help? My result set should display only id2 for the above search query

推荐答案

问题是您输入的字符根本不在ASCII范围内. VARCHAR(20)是数据的错误列数据类型.

The problem is that the characters you are entering are NOT in the ASCII range at all. VARCHAR(20) is the wrong column datatype for your data.

1. 更改表格结构
2. 更改插入方式
3. 最后，更改选择方式

已修复:

create table testasci(id int,name nvarchar(20))

insert into testasci values(1,N'santosh');
insert into testasci values(2,N'santosh♥');
insert into testasci values(3,N'santosh♦');
insert into testasci values(4,N'santosh2');
insert into testasci values(5,N'santoshσ');
insert into testasci values(6,N'santosh3');

select * from testasci where name like N'santosh♥';

看看您对varchar(20)的原始定义，我创建了一个 SQLFiddle 来显示问题.

Looking at your original definition of varchar(20), I have created an SQLFiddle to show the problem.

select id, a.name, number, ascii(substring(a.name,number,1))
from testasci a
join master..spt_values v
  on v.number between 1 and len(a.name) and type='P'
where a.id in (2,3)
order by id, number

您会注意到，ID 2和ID 3的第8个位置包含ASCII字符(63)，这是文字问号(?)，而不是您的特殊Unicode字符.因此，一旦它击中了表格列，您就将其丢失.

You'll notice that the 8th position of each of ID 2 and 3 contain the ASCII character (63), which is the literal question mark (?), not your special Unicode character. So you have lost it as soon as it hit the table column.

这篇关于在SQL Server中扩展的ASCII字符搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！