On 2018-01-18 19:25, tiberiu_horvath@... [firebird-support] wrote:

> Firebird 3, Delphi IBX.
>
> Somehow one of my customers inserted a product code so that at the end
> of the string he added chr(9) and stored this string in the database.

A ASCII_CHAR(9) is a perfectly valid character (horizontal tab in
ASCII), it just doesn't render a glyph.

> If I try to
>
> select
> cod_produs
> from produse
> where
> cod_produs = 'EI20010518'
>
> I get an empty query

Of course, because it doesn't match, to use equality, you'd need
'EI20010518' || x'09' (or 'EI20010518' || ASCII_CHAR(9))

> if I do
>
> select
> cod_produs
> from produse
> where
> cod_produs starting with 'EI20010518'
>
> I get the record
>
> if I do
>
> select
> cod_produs, -> 'EI20010518'
> char_length(trim(cod_produs)) -> 11 (instead of 10)
> from produse
> where
>
> cod_produs starting with 'EI20010518'
>
> I get the record
>
> this query :
>
> select
> cod_produs
> from produse
> where
> cod_produs starting with 'EI20010518' and
> substring(cod_produs from 11 for 1) = ASCII_CHAR(9)
>
> it is true, I get the record.
>
> My question is : Is there a query, a function, for filtering out these
> errors ? Someting like :
>
> update produse
> set cod_produs = AnsiOnly(cod_produs)

No, something like that doesn't exist in Firebird itself. But you can
build something like it yourself.

Rough example:

create or alter function removenonprintables(inputvalue varchar(100))
returns varchar(100)
as
declare currentposition integer = 0;
declare currentchar char(1);
declare sanitizedvalue varchar(100) = '';
begin
if (inputvalue is null) then
return null;
sanitizedvalue = '';
currentposition = 0;
while (currentposition < char_length(inputvalue))
do
begin
currentposition = currentposition + 1;
currentchar = substring(inputvalue from currentposition for 1);
if (currentchar similar to '[[:ALNUM:]]') then
sanitizedvalue = sanitizedvalue || currentchar;
end
return sanitizedvalue;
end

The use of '[[:ALNUM:]]' may not match everything you want to match as
it only matches latin characters (and those with accents depending on
the collation) and digits, check
https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-commons-predicates.html#fblangref25-commons-predsiimilarto
for more information.

Mark