Problem running SQL

Asked

Viewed 59 times

0

Good afternoon.

I have a simple SQL query

Select * from Tabela Order By Campo

Where this field is string, and this string has data like "xyz001", "xyz002", "xyz0011", thus, the "xyz0011" is displayed before the "xyz002", how to solve this?

sql

  • You want to sort as another list, would that?

  • Solve what? That’s right. The first 5 characters are equal, the 5th. is different one of them is "1", the other is "2", the 2 comes after the 1, is normal. If you wanted it to be numeric you should use number or keep the number of digits equal. If one of them was " xyz0002" it would be right.

  • Yes, the ordering is correct because it is dealing with alphanumerics. If the column Campo always has this format, can solve it. Has?

  • It will always have this format. Actually it’s more like xyz1,,, xyz11

  • @Maniero the easiest solution for me was to equal the same fields

2 answers

1

If the column Campo always the same format (alphanumeric characters + later numeric characters) so you can do it as follows:

SELECT      Campo
FROM        Tabela
ORDER BY    CAST(SUBSTRING(Campo, 4, LEN(Campo)) AS INT)

In this example I used the format you indicated in the question (3 alphanumeric characters), but it can always be adapted to other scenarios.

If it doesn’t always have the same format, then it would have to find a way to separate the alpha from the numeric and sort in the same way.

  • Thanks for the reply, but I preferred to match the amount of fields in the same select

1

Well if you use Oracle I see at least two alternatives to your question. You use Regular Expression and another solution would be to use the "substr" function to fetch the numbers and then sort them. Here’s an example.

    CREATE TABLE tab_notas(identificador VARCHAR(10) NOT NULL) -- Tabela exemplo

        -- Registros de exemplo
        BEGIN
          INSERT INTO tab_notas(identificador) VALUES('XYNDY00001');
          INSERT INTO tab_notas(identificador) VALUES('ZYNDY00011');
          INSERT INTO tab_notas(identificador) VALUES('XYXDY00104');
          INSERT INTO tab_notas(identificador) VALUES('VYNDY00206');
          INSERT INTO tab_notas(identificador) VALUES('PYNDY00020');
          INSERT INTO tab_notas(identificador) VALUES('MYODY02301');
          INSERT INTO tab_notas(identificador) VALUES('HYNDZ00701');
          INSERT INTO tab_notas(identificador) VALUES('KINDY00801');
          INSERT INTO tab_notas(identificador) VALUES('NYNDY00301');
          INSERT INTO tab_notas(identificador) VALUES('KYNDY00111');
          INSERT INTO tab_notas(identificador) VALUES('BYMDP00211');
          INSERT INTO tab_notas(identificador) VALUES('PYNDY00806');
          INSERT INTO tab_notas(identificador) VALUES('TYNDE00653');
          INSERT INTO tab_notas(identificador) VALUES('AINDY00207');
          INSERT INTO tab_notas(identificador) VALUES('RYNDM00511');
        END;

-- 1: Primeira Solução
select regexp_replace(n.identificador, '[^0-9]') identificador_numero_1,
       to_number(regexp_replace(n.identificador, '[^0-9]')) identificador_numero_2,
       n.identificador
  from tab_notas n
 order by to_number(regexp_replace(n.identificador, '[^0-9]'));

-- 2: Segunda Solução
select substr(n.identificador, 6, 5) identificador_numero_1,
       to_number(substr(n.identificador, 6, 5)) identificador_numero_2,
       n.identificador
  from tab_notas n
 order by to_number(substr(n.identificador, 6, 5));

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.