¿Cómo ordeno una columna VARCHAR en SQL server que contiene números?
Tengo una columna VARCHAR
en una base de datos SQL Server 2000
que puede contener letras o números. Depende de cómo se configure la aplicación en el front-end para el cliente.
Cuando no contiene números, quiero ser ordenados numéricamente, por ejemplo, como "1", "2", "10" en lugar de "1", "10", "2". Los campos que contienen solo letras, o letras y números (como 'A1') se pueden ordenar alfabéticamente como es normal. Por ejemplo, este sería un orden de clasificación aceptable.
1
2
10
A
B
B1
¿Qué es el ¿la mejor manera de lograrlo?
11 answers
Una posible solución es rellenar los valores numéricos con un carácter delante para que todos tengan la misma longitud de cadena.
Aquí hay un ejemplo usando ese enfoque:
select MyColumn
from MyTable
order by
case IsNumeric(MyColumn)
when 1 then Replicate('0', 100 - Len(MyColumn)) + MyColumn
else MyColumn
end
El 100
debe sustituirse por la longitud real de esa columna.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-02-25 07:58:09
Hay algunas maneras posibles de hacer esto.
Uno sería
SELECT
...
ORDER BY
CASE
WHEN ISNUMERIC(value) = 1 THEN CONVERT(INT, value)
ELSE 9999999 -- or something huge
END,
value
La primera parte del ORDEN POR convierte todo a un int (con un valor enorme para no numéricos, para ordenar último) entonces la última parte se encarga de alfabéticos.
Tenga en cuenta que el rendimiento de esta consulta es probablemente al menos moderadamente espantoso en grandes cantidades de datos.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2008-09-23 08:13:26
select
Field1, Field2...
from
Table1
order by
isnumeric(Field1) desc,
case when isnumeric(Field1) = 1 then cast(Field1 as int) else null end,
Field1
Esto devolverá valores en el orden que dio en su pregunta.
El rendimiento no será demasiado grande con todo ese casting, por lo que otro enfoque es agregar otra columna a la tabla en la que almacena una copia entera de los datos y luego ordenar por esa primera y luego la columna en cuestión. Obviamente, esto requerirá algunos cambios en la lógica que inserta o actualiza los datos en la tabla, para rellenar ambas columnas. O eso, o poner un disparador en la mesa para poblar la segunda columna cada vez que se insertan o actualizan datos.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2008-09-23 13:46:37
SELECT *, CONVERT(int, your_column) AS your_column_int
FROM your_table
ORDER BY your_column_int
O
SELECT *, CAST(your_column AS int) AS your_column_int
FROM your_table
ORDER BY your_column_int
Ambos son bastante portátiles creo.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-01-13 20:55:28
Lo resolví de una manera muy simple escribiendo esto en la parte" orden "
ORDER BY (
sr.codice +0
)
ASC
Esto parece funcionar muy bien, de hecho tuve la siguiente clasificación:
16079 Customer X
016082 Customer Y
16413 Customer Z
Así que el 0
delante de 16082
se considera correctamente.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-07-22 08:42:48
Siempre puede convertir su varchar-column a bigint como entero podría ser demasiado corto...
select cast([yourvarchar] as BIGINT)
Pero siempre debes cuidar los caracteres alfa
where ISNUMERIC([yourvarchar] +'e0') = 1
El +'e0' viene de http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/isnumeric-isint-isnumber
Esto llevaría a su declaración
SELECT
*
FROM
Table
ORDER BY
ISNUMERIC([yourvarchar] +'e0') DESC
, LEN([yourvarchar]) ASC
La primera columna de ordenación pondrá numérica en la parte superior. la segunda clasifica por longitud, por lo que 10 precederá 0001 (que es estúpido?!)
Esto conduce a la segunda versión:
SELECT
*
FROM
Table
ORDER BY
ISNUMERIC([yourvarchar] +'e0') DESC
, RIGHT('00000000000000000000'+[yourvarchar], 20) ASC
La segunda columna ahora se rellena con '0', por lo que la ordenación natural pone enteros con ceros a la izquierda (0,01,10,0100...) en el orden correcto (correcto!)- pero todos los alfas serían mejorados con'0' -caracteres (rendimiento)
Así que tercera versión:
SELECT
*
FROM
Table
ORDER BY
ISNUMERIC([yourvarchar] +'e0') DESC
, CASE WHEN ISNUMERIC([yourvarchar] +'e0') = 1
THEN RIGHT('00000000000000000000' + [yourvarchar], 20) ASC
ELSE LTRIM(RTRIM([yourvarchar]))
END ASC
Ahora los números primero se rellenan con ' 0 ' - caracteres (por supuesto, se podría mejorar la longitud 20) - que ordena los números correctamente-y los alfas solo se recortan
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-04-04 09:56:29
Esto parece funcionar:
select your_column
from your_table
order by
case when isnumeric(your_column) = 1 then your_column else 999999999 end,
your_column
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-04-03 08:00:59
SELECT FIELD FROM TABLE
ORDER BY
isnumeric(FIELD) desc,
CASE ISNUMERIC(test)
WHEN 1 THEN CAST(CAST(test AS MONEY) AS INT)
ELSE NULL
END,
FIELD
De acuerdo con este enlace necesitas lanzar a MONEY then INT para evitar ordenar '$' como un número.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2008-09-23 08:18:09
Esta consulta es útil para usted. En esta consulta, una columna tiene el tipo de datos varchar está organizado por buen orden.Por ejemplo-En esta columna los datos son: - G1, G34, G10, G3. Por lo tanto, después de ejecutar esta consulta,se ven los resultados: - G1,G10,G3, G34.
SELECT *,
(CASE WHEN ISNUMERIC(column_name) = 1 THEN 0 ELSE 1 END) IsNum
FROM table_name
ORDER BY IsNum, LEN(column_name), column_name;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-08-31 17:11:42
Esto puede ayudarte, he intentado esto cuando tengo el mismo problema.
SELECT *
FROM tab
ORDER BY IIF(TRY_CAST(val AS INT) IS NULL, 1, 0),TRY_CAST(val AS INT);
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-05-28 08:19:28
SELECT *,
ROW_NUMBER()OVER(ORDER BY CASE WHEN ISNUMERIC (ID)=1 THEN CONVERT(NUMERIC(20,2),SUBSTRING(Id, PATINDEX('%[0-9]%', Id), LEN(Id)))END DESC)Rn ---- numerical
FROM
(
SELECT '1'Id UNION ALL
SELECT '25.20' Id UNION ALL
SELECT 'A115' Id UNION ALL
SELECT '2541' Id UNION ALL
SELECT '571.50' Id UNION ALL
SELECT '67' Id UNION ALL
SELECT 'B48' Id UNION ALL
SELECT '500' Id UNION ALL
SELECT '147.54' Id UNION ALL
SELECT 'A-100' Id
)A
ORDER BY
CASE WHEN ISNUMERIC (ID)=0 /* alphabetical sort */
THEN CASE WHEN PATINDEX('%[0-9]%', Id)=0
THEN LEFT(Id,PATINDEX('%[0-9]%',Id))
ELSE LEFT(Id,PATINDEX('%[0-9]%',Id)-1)
END
END DESC
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-05-10 16:38:44