1

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

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');

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

select * from testasci where name = 'santosh♥'

result set displays id2, id3.

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

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Santosh
  • 2,355
  • 10
  • 41
  • 64

2 Answers2

4

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. Change your table structure
  2. Change the way you insert
  3. Finally, change the way you SELECT

Fixed:

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♥';

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

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.

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
3

N is used for search extended ascii characters

select * from testasci where name like  N'santosh♥'
Buzz
  • 6,030
  • 4
  • 33
  • 47
  • Now my frontend is sending ♥ symbol in url encode format.So this ♥ shape comes to sql as %e2%99%a5.Now my sql search will have this encoded value to search for ♥.Can i decode this string(%e2%99%a5) back to ♥ and search or any other way?Plz Help – Santosh Oct 19 '12 at 12:51
  • if you are using C# ,use system.text.encoding.ascii – Buzz Oct 19 '12 at 13:01
  • If i use System.text.encoding.ascii in c# i can convert ♥ to %e2%99%a5 and vice versa. But how i can tell SQL to search for ♥ when my search string is %e2%99%a5,as iam going to pass this string only as my search parameter.(select * from table where name='%e2%99%a5') – Santosh Oct 19 '12 at 14:02