CODEWITHSUNDEEP
sqloracle-databaseunicodeascii
Matt Gibson
Matt Gibson

Reputation: 38238

Finding character values outside ASCII range in an NVARCHAR column

Is there a simple way of finding rows in an Oracle table where a specific NVARCHAR2 column has one or more characters which wouldn't fit into the standard ASCII range?

(I'm building a warehousing and data extraction process which takes the Oracle data, drags it into SQL Server -- UCS-2 NVARCHAR -- and then exports it to a UTF-8 XML file. I'm pretty sure I'm doing all the translation properly, but I'd like to find a bunch of real data to test with that's more likely to cause problems.)

Upvotes: 0

Views: 2247

Answers (2)

Matt Gibson
Matt Gibson

Reputation: 38238

Sparky's example for SQL Server was enough to lead me to a pretty simple Oracle solution, once I'd found the handy ASCIISTR() function.

SELECT
  * 
FROM 
  test_table
WHERE 
  test_column != ASCIISTR(test_column)

...seems to find any data outside the standard 7-bit ASCII range, and appears to work for NVARCHAR2 and VARCHAR2.

Upvotes: 1

Sparky
Sparky

Reputation: 15075

Not sure how to tackle this in Oracle, but here is something I've done in MS-SQL to deal with the same issue...

create table #temp (id int, descr nvarchar(200))

insert into #temp values(1,'Now is a good time')
insert into #temp values(2,'So is yesterday')
insert into #temp values(2,'But not '+NCHAR(2012))

select * 
from #temp 
where CAST(descr as varchar(200)) <> descr

drop table #temp

Upvotes: 1

Related Questions