Subject Re: [firebird-support] Transliterating - finding non ASCII characters
Author Milan Babuskov
Chad Z. Hower aka Kudzu wrote:
> I have a field that was marked as Unicode FSS that should have been ASCII. I
> have a script which creates a new field, copies, drops old, and renames it.
> This works fine on my debug DB, but somehow the live DB has some non ASCII
> data in this field and when I try to apply this to the live DB is says
> cannot tranlisterate.
> Im not sure how this field got non ASCII data, prob some stupid user. But
> the question is - there are 25,000 rows, how can I find out what row(s) have
> this non ASCII data so I can look at them and maybe manually fix them up?

Write a stored procedure that copies row by row and outputs pk or something
else that identifies rows see where the error pops up:

create procedure doit
returns (pk ...)
declare variable pk ...;
for select pk, old_field
from table
order by pk
into :pk, :value
update table set new_field = :value
where pk = :pk;

Then just do:
select * from doit and look what PK is output before error pops up.

Alternative idea would be to read row by row and check if char fits in ASCII

Milan Babuskov