written in Delphi, this selects only numerical characters from a 20 char field (a barcode). Test this also for

substring(op.cod_de_bare from 1 for 1) between '+QuotedStr('a')+' and '+QuotedStr('Z')

a UDF would be much more elegant ... but this works ...



my_query.Close;
my_query.SQL.Clear;
my_query.SQL.Add('with query as ');
my_query.SQL.Add('( ');
my_query.SQL.Add('select ');
my_query.SQL.Add(' coalesce( ');
my_query.SQL.Add(' trim( ');
my_query.SQL.Add(' iif(substring(op.cod_de_bare from 1 for 1) between '+QuotedStr('0')+' and '+QuotedStr('9')+' ,substring(op.cod_de_bare from 1 for 1),'+QuotedStr('')+')|| ');
my_query.SQL.Add(' iif(substring(op.cod_de_bare from 2 for 1) between '+QuotedStr('0')+' and '+QuotedStr('9')+' ,substring(op.cod_de_bare from 2 for 1),'+QuotedStr('')+')|| ');
my_query.SQL.Add(' iif(substring(op.cod_de_bare from 3 for 1) between '+QuotedStr('0')+' and '+QuotedStr('9')+' ,substring(op.cod_de_bare from 3 for 1),'+QuotedStr('')+')|| ');
my_query.SQL.Add(' iif(substring(op.cod_de_bare from 4 for 1) between '+QuotedStr('0')+' and '+QuotedStr('9')+' ,substring(op.cod_de_bare from 4 for 1),'+QuotedStr('')+')|| ');
my_query.SQL.Add(' iif(substring(op.cod_de_bare from 5 for 1) between '+QuotedStr('0')+' and '+QuotedStr('9')+' ,substring(op.cod_de_bare from 5 for 1),'+QuotedStr('')+')|| ');
my_query.SQL.Add(' iif(substring(op.cod_de_bare from 6 for 1) between '+QuotedStr('0')+' and '+QuotedStr('9')+' ,substring(op.cod_de_bare from 6 for 1),'+QuotedStr('')+')|| ');
my_query.SQL.Add(' iif(substring(op.cod_de_bare from 7 for 1) between '+QuotedStr('0')+' and '+QuotedStr('9')+' ,substring(op.cod_de_bare from 7 for 1),'+QuotedStr('')+')|| ');
my_query.SQL.Add(' iif(substring(op.cod_de_bare from 8 for 1) between '+QuotedStr('0')+' and '+QuotedStr('9')+' ,substring(op.cod_de_bare from 8 for 1),'+QuotedStr('')+')|| ');
my_query.SQL.Add(' iif(substring(op.cod_de_bare from 9 for 1) between '+QuotedStr('0')+' and '+QuotedStr('9')+' ,substring(op.cod_de_bare from 9 for 1),'+QuotedStr('')+')|| ');
my_query.SQL.Add(' iif(substring(op.cod_de_bare from 10 for 1) between '+QuotedStr('0')+' and '+QuotedStr('9')+' ,substring(op.cod_de_bare from 10 for 1),'+QuotedStr('')+')|| ');
my_query.SQL.Add(' iif(substring(op.cod_de_bare from 11 for 1) between '+QuotedStr('0')+' and '+QuotedStr('9')+' ,substring(op.cod_de_bare from 11 for 1),'+QuotedStr('')+')|| ');
my_query.SQL.Add(' iif(substring(op.cod_de_bare from 12 for 1) between '+QuotedStr('0')+' and '+QuotedStr('9')+' ,substring(op.cod_de_bare from 12 for 1),'+QuotedStr('')+')|| ');
my_query.SQL.Add(' iif(substring(op.cod_de_bare from 13 for 1) between '+QuotedStr('0')+' and '+QuotedStr('9')+' ,substring(op.cod_de_bare from 13 for 1),'+QuotedStr('')+')|| ');
my_query.SQL.Add(' iif(substring(op.cod_de_bare from 14 for 1) between '+QuotedStr('0')+' and '+QuotedStr('9')+' ,substring(op.cod_de_bare from 14 for 1),'+QuotedStr('')+')|| ');
my_query.SQL.Add(' iif(substring(op.cod_de_bare from 15 for 1) between '+QuotedStr('0')+' and '+QuotedStr('9')+' ,substring(op.cod_de_bare from 15 for 1),'+QuotedStr('')+')|| ');
my_query.SQL.Add(' iif(substring(op.cod_de_bare from 16 for 1) between '+QuotedStr('0')+' and '+QuotedStr('9')+' ,substring(op.cod_de_bare from 16 for 1),'+QuotedStr('')+')|| ');
my_query.SQL.Add(' iif(substring(op.cod_de_bare from 17 for 1) between '+QuotedStr('0')+' and '+QuotedStr('9')+' ,substring(op.cod_de_bare from 17 for 1),'+QuotedStr('')+')|| ');
my_query.SQL.Add(' iif(substring(op.cod_de_bare from 18 for 1) between '+QuotedStr('0')+' and '+QuotedStr('9')+' ,substring(op.cod_de_bare from 18 for 1),'+QuotedStr('')+')|| ');
my_query.SQL.Add(' iif(substring(op.cod_de_bare from 19 for 1) between '+QuotedStr('0')+' and '+QuotedStr('9')+' ,substring(op.cod_de_bare from 19 for 1),'+QuotedStr('')+')|| ');
my_query.SQL.Add(' iif(substring(op.cod_de_bare from 20 for 1) between '+QuotedStr('0')+' and '+QuotedStr('9')+' ,substring(op.cod_de_bare from 20 for 1),'+QuotedStr('')+') ');
my_query.SQL.Add(' ) ');
my_query.SQL.Add(' ,'+QuotedStr('0')+') ');
my_query.SQL.Add(' as cod_de_bare ');
my_query.SQL.Add('from observatii_produse op ');
my_query.SQL.Add('where ');
my_query.SQL.Add(' (trim(op.cod_de_bare)<>'+QuotedStr('')+') and ');
my_query.SQL.Add(' (op.cod_de_bare is not null) and ');
my_query.SQL.Add(' (trim(op.cod_de_bare)<>'+QuotedStr('- -')+')' );
my_query.SQL.Add(') ');
my_query.SQL.Add('select ');
my_query.SQL.Add(' max( ');
my_query.SQL.Add(' cast(query.cod_de_bare as decimal(18,0)) ');
my_query.SQL.Add(' ) as rezultat ');
my_query.SQL.Add('from query ');
my_query.SQL.Add('where ');
my_query.SQL.Add(' (trim(query.cod_de_bare)<>'+QuotedStr('')+') ');



Tiberiu

[Non-text portions of this message have been removed]