How to order Varchar field in numeric sort

[Luigi Siciliano]

Hello,
  I have a varchar field "NUMERO" populated by numbers that can have
literals into it like "1595/A-2023".

If i select the field "NUMERO" it is sort not in numeric sort.

How i can sort it in numeric sort without exception if the field contain
a literals?

I think with a collate but i don't know how.

Thanks

--
Luigi
--------------------------

[Dimitry Sibiryakov]

Luigi Siciliano wrote 17.10.2023 18:37:
> How i can sort it in numeric sort without exception if the field contain a
> literals?
>
> I think with a collate but i don't know how.

https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref40/fblangref40-ddl-collation.html#fblangref40-ddl-collation-exmpl

--
WBR, SD.

[Luigi Siciliano]

Hello,

  I create a collation like example:

CREATE COLLATION nums_coll FOR UTF8
  FROM UNICODE
  CASE INSENSITIVE 'NUMERIC-SORT=1';

but when i try this query:

select
  NUMERO
from
  DOC_TESTA
where
  documento_id = 'DTA'
order by
  numero collate nums_coll

i get exception: SQL Message: -802 "Arithmetical overflow or division by
zero has occurred".

This because field "NUMERO" can contain literals in result set like
"EG003211".

The query works fine if "NUMERO" not contain literals in the result set.

Suggestions?

Thanks

--
Luigi
--------------------------

[Dimitry Sibiryakov]

Luigi Siciliano wrote 18.10.2023 10:14:
>
> This because field "NUMERO" can contain literals in result set like "EG003211".
>
> The query works fine if "NUMERO" not contain literals in the result set.
>
> Suggestions?

In the previous millennium for similar task I had an additional field in
which each literal part and digital part were padded (and uppered). It looks
like no better approach has been invented since then.

--
WBR, SD.

[Mark Rotteveel]

That sounds like a bug, assuming it is not caused by something else. BTW
I assume that with "literals" you mean letters (or non-number characters).

Mark
--
Mark Rotteveel

[Luigi Siciliano]

Hello,

Il 18/10/2023 10:37, 'Mark Rotteveel' via firebird-support ha scritto:
> That sounds like a bug, assuming it is not caused by something else.

I work with FB 2.5.9. I can't update to 3.0 or 4.0 for a while :(

> BTW I assume that with "literals" you mean letters (or non-number
> characters).
>

Yes, I mean letters or non-numeric characters that can also be in the
middle of the field like '0123-A/2023'

Thanks
--
Luigi
--------------------------

[Mark Rotteveel]

On 18-10-2023 10:37, 'Mark Rotteveel' via firebird-support wrote:
> That sounds like a bug, assuming it is not caused by something else. BTW
> I assume that with "literals" you mean letters (or non-number characters).

In any case, I cannot reproduce it: https://dbfiddle.uk/Q2-wztw9

Can you provide a reproducible example?

Mark
--
Mark Rotteveel

[Tomasz Tyrakowski]

On 18.10.2023 at 10:14, Luigi Siciliano wrote:
>   I create a collation like example:
>
> CREATE COLLATION nums_coll FOR UTF8
>   FROM UNICODE
>   CASE INSENSITIVE 'NUMERIC-SORT=1';
>
> but when i try this query:
>
> select
>   NUMERO
> from
>   DOC_TESTA
> where
>   documento_id = 'DTA'
> order by
>   numero collate nums_coll
>
> i get exception: SQL Message: -802 "Arithmetical overflow or division by
> zero has occurred".
>
> This because field "NUMERO" can contain literals in result set like
> "EG003211".
>
> The query works fine if "NUMERO" not contain literals in the result set.

Are you sure that's the cause? To do a quick test, I created the
collation exactly as you did and the query:

select cast('a12a15' as varchar(10) character set utf8) from
rdb$database order by 1 collate nums_coll;

runs without errors (isql, FB 4.0.2).
So it seems non-digits are not a problem here. Maybe some diacritics in
NUMERO? I've tried with Polish national characters and still no error,
but I don't know how exactly NUMERO is defined.
Also, is this the real error message you get?
I think in FB 4.0.2 this error actually is "arithmetic exception,
numeric overflow, or string truncation-Floating-point divide by zero."
(I've just copy-pasted it from isql in my terminal window).

regards
Tomasz

[Mark Rotteveel]

I also tried it locally on a Firebird 2.5.9 (on Windows), and it works
as expected.

--
Mark Rotteveel

[Tomasz Tyrakowski]

On 18.10.2023 at 10:14, Luigi Siciliano wrote:>   I create a collation

like example:
>
> CREATE COLLATION nums_coll FOR UTF8
>   FROM UNICODE
>   CASE INSENSITIVE 'NUMERIC-SORT=1';
>
> but when i try this query:
>
> select
>   NUMERO
> from
>   DOC_TESTA
> where
>   documento_id = 'DTA'
> order by
>   numero collate nums_coll
>
> i get exception: SQL Message: -802 "Arithmetical overflow or division by
> zero has occurred".
>
> This because field "NUMERO" can contain literals in result set like
> "EG003211".
>
> The query works fine if "NUMERO" not contain literals in the result set.

[...]
> I work with FB 2.5.9. I can't update to 3.0 or 4.0 for a while 🙁

I've just did the same test in 2.5.9 and get no errors:

select cast('12-ą15' as varchar(10) character set utf8) from

rdb$database order by 1 collate nums_coll;

(ą is a Polish diacritic).
Perhaps the definition of your NUMERO column is the problem? Is it a
UTF-8 column, or converted on the fly somehow?
Could you try to copy-paste the above query and see what happens?

regards
Tomasz

[Luigi Siciliano]

Hello,

Il 18/10/2023 10:46, 'Mark Rotteveel' via firebird-support ha scritto:
> In any case, I cannot reproduce it: https://dbfiddle.uk/Q2-wztw9

I think I find why in my existing table the collate don't works:

The NUMBER field is defined as "NUMBER Varchar(20) CHARACTER SET NONE".

It is the only field defined as "CHARACTER SET NONE" and I don't know why.

Default character set in database is UTF8. What problem I can have if I
change the definition of field "NUMERO"?

Can I create a nums_coll for NONE character set?

Thanks

--
Luigi
--------------------------

[Mark Rotteveel]

On 18-10-2023 11:26, Luigi Siciliano wrote:

No, there are no collations available for NONE. The best you can try is
cast to UTF8 to try and apply the collation, but if any byte combination
in the column is not valid UTF-8, you'll get an error (which is likely
what is causing your problem right now). It might succeed with
double-casting (first to the actual character set of the values stored
in this column, and then to UTF8).

Mark
--
Mark Rotteveel

[Joaozinho]

 You can try "ORDER BY LPAD(NUMERO, 20)".  

[Karol Bieniaszewski]

Hi

 

i do not know how many dependencies you have on that field, but i will create second field with proper collation, poulate it from old field, delete old and rename new.

 

But i see you can use my below function i have used it to different purposes but it should fit your needs.

 

SET TERM ^ ;

RECREATE FUNCTION REFORMAT_SN(S VARCHAR(100), PAD_NUM_CHARS INTEGER) RETURNS VARCHAR(500)

AS

DECLARE VARIABLE VAR_RESULT VARCHAR(500);

DECLARE VARIABLE VAR_I INTEGER;

DECLARE VARIABLE VAR_SUB_NR VARCHAR(100);

DECLARE VARIABLE VAR_SUB_A VARCHAR(100);

DECLARE VARIABLE VAR_LEN INTEGER;

DECLARE VARIABLE VAR_C CHAR(1);

BEGIN

  IF (S IS NULL) THEN

    RETURN NULL;

 

  VAR_LEN = CHAR_LENGTH(S);

 

  VAR_I=1;

  VAR_SUB_NR = '';

  VAR_SUB_A = '';

  VAR_RESULT = '';

  WHILE (VAR_I<=VAR_LEN) DO

    BEGIN

      VAR_C = SUBSTRING(S FROM :VAR_I FOR 1);

     

      IF ('0123456789' CONTAINING :VAR_C) THEN

        BEGIN

          VAR_SUB_NR = VAR_SUB_NR || VAR_C;

          IF (VAR_SUB_A<>'') THEN

            VAR_RESULT = VAR_RESULT || LPAD(VAR_SUB_A, PAD_NUM_CHARS, '_');

          VAR_SUB_A = '';         

        END ELSE

        BEGIN

          VAR_SUB_A = VAR_SUB_A || VAR_C;

          IF (VAR_SUB_NR<>'') THEN

            VAR_RESULT = VAR_RESULT || LPAD(VAR_SUB_NR, PAD_NUM_CHARS, '0');

          VAR_SUB_NR = ''; 

        END      

      

      VAR_I = VAR_I + 1;

    END

   

  IF (VAR_SUB_A<>'') THEN

    VAR_RESULT = VAR_RESULT || LPAD(VAR_SUB_A, PAD_NUM_CHARS, '_');

  IF (VAR_SUB_NR<>'') THEN

    VAR_RESULT = VAR_RESULT || LPAD(VAR_SUB_NR, PAD_NUM_CHARS, '0'); 

 

  RETURN VAR_RESULT;

END

^

SET TERM ; ^

 

 

 

Create it and then simply

 

SELECT

NUMERO

FROM

DOC_TESTA

ORDER BY REFORMAT_SN(NUMERO, 20)

 

 

Regards,

Karol Bieniaszewski

--

You received this message because you are subscribed to the Google Groups "firebird-support" group.

To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/adc6d9ab-41ed-5003-93c3-e01fba12c34b%40tiscalinet.it.

 

[Joaozinho]

You can try "ORDER BY LPAD(NUMERO, 20)".

[Luigi Siciliano]

Hello,

Il 17/10/2023 22:53, 'Joaozinho' via firebird-support ha scritto:
> You can try "ORDER BY LPAD(NUMERO, 20)".

Don't get right sort but is best of nothing

Thanks
--
Luigi
--------------------------

[Luigi Siciliano]

Il 18/10/2023 22:33, Karol Bieniaszewski ha scritto:

Hi

 

i do not know how many dependencies you have on that field, but i will create second field with proper collation, poulate it from old field, delete old and rename new.

No dependencies.

 

But i see you can use my below function i have used it to different purposes but it should fit your needs.

Thank You

--
Luigi
--------------------------

[Luigi Siciliano]

Hello,

If I understand correctly the collate I create is not saved on the
database but on the server. Is this correct?
Since for each reinstallation of the server I have to recreate the
collate: How can I check if the collate exists to recreate it?

Thank you

--
Luigi
--------------------------

[Mark Rotteveel]

On 19-10-2023 09:51, Luigi Siciliano wrote:
> If I understand correctly the collate I create is not saved on the
> database but on the server. Is this correct?
> Since for each reinstallation of the server I have to recreate the
> collate: How can I check if the collate exists to recreate it?

No, the collation is created in the database, and is specific to that
database. Reinstalling the server doesn't (or shouldn't) require
recreating the collation.

You can check collations by querying the RDB$COLLATIONS table.

Mark
--
Mark Rotteveel