Special character in SQL

Question

I have a problem with a special character inserted in a SQL Server 2008 R2 table. The fact is that when I try to insert a row with the character º (for example, 3 ELBOW 90º LONG RADIUS) into the table, it looks like this: 3 ELBOW 90 LONG RADIUS, and when I try to select all the rows containing the character, the result is zero.

I tried to make a selection using ASCII by doing this: select * from itemcode, where the type description is '%' + char (63) + '%'

and make sure the ASCII of this character is 63:

select ASCII ('')

But that does not work. What should I do to select all rows that have this character, and what should I do to make this SQL recognize the character º?

thanks

Answer 1

degree symbol

U+00B0 ° degree sign (HTML: ° °) 

is not an ASCII character and usually requires an NVARCHAR column and a string literal N'' . (excluding code pages, etc. that support the character)

63 is the question mark code that is reserved for your reverse question mark in ASCII:

 select UNICODE(' ') => 63 select UNICODE(N' ') => 65533 

where 65533 is the Unicode Replacement Character used to display characters that could not be converted or displayed.

Answer 2

when i run this:

 print ascii('º') 

I get 186 as the value of the ascii code, so try:

 select * from YourTable Where Description like '%'+char(186)+'%' 

to see all ascii codes:

 ;WITH AllNumbers AS ( SELECT 1 AS Number UNION ALL SELECT Number+1 FROM AllNumbers WHERE Number<255 ) SELECT Number,CHAR(Number) FROM AllNumbers OPTION (MAXRECURSION 255) 

EDIT op indicated in the comment that they use nvarchar columns.

forger about ascii, use NCHAR (Transact-SQL) to output the degree character:

 print '32'+NCHAR(176)+'F' --to display it select * from YourTable Where Description like '%'+NCHAR(176)+'%' --to select based on it 

and use UNICODE (Transact-SQL) to get the value:

 print UNICODE('°') 

returns:

 176 

Answer 3

 select top 10 * from table_name where tbl_colmn like N'%'+ NCHAR(65533) + N'%' 

the NCHAR function (65533) will return the character you are looking for.

Answer 4

In addition to being NVARCHAR, I would use something like this

select (N``)

How to display special characters in SQL Server 2008?