Special Character in SQL

Question

I have a problem with a special character inserted in a table of SQL Server 2008 R2. The point is that when i'm trying to insert a string with the character º (e.g. 3 ELBOW 90º LONG RADIUS) in the table this appears like this: 3 ELBOW 90� LONG RADIUS, and when i'm trying to select all the rows that contains the character � the result is null.

I tried to make the select with ASCII by making this: select * from itemcode where description like '%'+char(63)+'%'

and make this to know that the ASCII of that symbol is 63:

select ASCII('�')

But that doesn't work. What i must do to select all the rows that have that character and what should i do to make that SQL recognize the character º?

Thanks

Answer 1

The degree symbol

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

is not an ASCII character and generally requires an NVARCHAR column and a N'' string literal. (except for codepages etc that support the symbol)

63 is the code of the question mark, which is the fallback for your inverse 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 ascii code value, so try:

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

to see all the ascii codes run this:

;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 stated in a comment that they are using nvarchar columns.

forger about ascii, use NCHAR (Transact-SQL) to output a degree symbol:

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 function NCHAR(65533) will return the character your're looking for.