CODEWITHSUNDEEP
sql-serverascii
Misayel Espinoza
Misayel Espinoza

Reputation: 101

Special Character in SQL

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

Upvotes: 10

Views: 45791

Answers (5)

armen
armen

Reputation: 1283

I know this is old, but recently faced the same problem, and found solution here

"The best way I know of to find it or get rid of it in SQL is to check for it using a binary collation. For example"

Declare @Foo Table(PK int primary key identity, MyData nvarchar(20));
Insert @Foo(MyData) Values (N'abc'), (N'ab�c'), (N'abc�')
Select * From @Foo Where MyData Like N'%�%'

-- Find rows with the character
Select * From @Foo
Where CharIndex(nchar(65533) COLLATE Latin1_General_BIN2, MyData) > 0

-- Update rows replacing character with a !
Update @Foo
set MyData = Replace(MyData, nchar(65533) COLLATE Latin1_General_BIN2, '!')

Select * From @Foo

Upvotes: 1

christos_g
christos_g

Reputation: 11

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.

Upvotes: 1

KM.
KM.

Reputation: 103587

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

Upvotes: 4

devio
devio

Reputation: 37215

The degree symbol 

U+00B0 ° degree sign (HTML: &#176; &deg;)

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.

Upvotes: 9

Neil
Neil

Reputation: 919

In addition to making sure that it is an NVARCHAR, I would use something like this

select (N'�')

How to display special characters in SQL server 2008?

Upvotes: 0

Related Questions