Problem running SQL

Question

Problem running SQL

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

Good afternoon.

I have a simple query in SQL

Select * from Tabela Order By Campo

Where this field is a string, and this string has data such as "xyz001" , "xyz002" , "xyz0011" , so "xyz0011" is displayed first as "xyz002" , how to solve it? p>

sql

asked by anonymous 12.09.2018 / 17:11

2 answers

Ajax doubts in updates Kanade Optical Flow Algorithm Lucas Tomasi (KLT) in Python [closed]

score 1 · Answer 1

If the Campo column always has the same format (alphanumeric + later numeric characters) then you can do the following:

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

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

If it does not always have the same format, then you would have to find a way to separate the alpha from the numeric and sort the same.

score 1 · Answer 2

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 there.

    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));