ПОРЯДОК SQL ПО дилемме с числами

ProgramBox

ПОРЯДОК SQL ПО дилемме с числами

Post author:admin
Запись опубликована:12 марта, 2023
Post category:Вопросы по программированию

#mysql #sql #sql-order-by

Вопрос:

У меня есть таблица, которая содержит тип данных varchar. Он содержит максимум 128 символов.

Я пытаюсь упорядочить его в алфавитном порядке, и все работает нормально, за исключением одной маленькой вещи.

Когда я пытаюсь сохранить смесь цифр и букв, он возвращает «буквальный» алфавитный порядок, что означает, что 11 идет первым перед 2.

Я прочитал почти все ответы в Интернете, но все они являются обходными путями, которые не могут работать конкретно для моей проблемы.

Примеры значений, которые я хочу привести в порядок

 Apartment
House
Dog
Cat
18 years old
2 years old
1 year old

Но я хочу, чтобы это выглядело так.

 1 year old
2 years old
18 years old
Apartment
Cat
Dog
House

Он охватывает большую базу данных, и я не могу просто разделить числовые значения отдельно от текстовых.

Также пользователи, которые могут использовать программу, могут изменять ее с помощью буквенно-цифровых символов.

Есть предложения по моей проблеме? Спасибо.

1. Какую базу данных SQL вы используете? MySQL? SQL Server? Oracle?

2. В каком порядке вы хотите разместить свои данные? В любом случае, начните с чтения этого — en.wikipedia.org/wiki /…

3. -1 для некоторых из худших данных, которые я видел

4. В настоящее время я использую MySQL для этого конкретного проекта. Данные на выходе должны быть в алфавитно-числовом порядке, если это вообще термин. 🙂 Это означает, что сначала идет 1, затем 2 до 11, затем после всех чисел, вот когда следует чистое текстовое значение 🙂

5. Пожалуйста, обновите свой пост, чтобы показать, как вы хотели бы, чтобы ваши данные выборки были отсортированы как. Вы хотите, чтобы «1 год» был до «2 лет», как вы ожидаете, что это сработает?

Ответ №1:

Вот что я попробовал в SQL Server. Это не элегантно и не подходит для производства, но может дать вам представление.

 SELECT StringValue, 
    CAST(SUBSTRING(StringValue, StartPos, EndPos - StartPos) AS INT) AsNumber,
    SUBSTRING(StringValue, StartPos, EndPos - StartPos) NumberToken,
    SUBSTRING(StringValue, EndPos, 1000) Rest,
    StartPos, 
    EndPos
FROM    
    (SELECT 
        StringValue,
        PATINDEX('[0-9]%', StringValue) StartPos,
        PATINDEX('%[^0-9]%', StringValue) EndPos
    FROM 
        (SELECT 'abc123xyz' StringValue
        UNION SELECT '1abc'
        UNION SELECT '11abc'
        UNION SELECT '2abc'
        UNION SELECT '100 zasdfasd') Sub1
    ) Sub2
ORDER BY AsNumber, Rest

Результат:

stringValue AsNumber NumberToken Rest startPos EndPos
abc123xyz 0 abc123xyz 0 1
1abc 1 1 abc 1 2
2abc 2 2 abc 1 2
11abc 11 11 abc 1 3
100 zasdfasd 100 100 zasdfasd 1 4

Ответ №2:

Я бы подошел к этому следующим образом…

Сначала напишите выражение для преобразования числовых данных в целые числа, что-то вроде

 select CAST(SUBSTRING(<field>',1,instr(<field>',' ') as INT),<field>

Затем я бы использовал оператор UNION ALL, что-то вроде этого

 SELECT CAST(SUBSTRING(<field>',1,instr(<field>',' ') as INT),<field>,A.*
FROM <table> A
WHERE <field> LIKE <regular expression to get fields beginning with numbers>
UNION ALL
SELECT 999999,<field>,A.*
FROM <table> A
WHERE <field> NOT LIKE <regular expression to get fields beginning with numbers>
ORDER BY 1,2,3

Числа будут отображаться первыми в числовом порядке. Поскольку все альфа-данные имеют один и тот же цифровой ключ, они будут отсортированы в алфавитном порядке после чисел… Просто убедитесь, что альфа-фиктивный ключ (999999) достаточно велик, чтобы быть после всех числовых…

У меня нет MySQL на этой машине, но, надеюсь, это даст вам достаточное начало для ее решения

1. да. это работает нормально, если числа находятся в первой части стека. как насчет того, есть ли у вас значения Super 8 или Super 16? Super 8 должен быть первым перед super 16. Есть предложения?

2. Вы могли бы использовать анализатор регулярных выражений MySQL для создания регулярного выражения, которое будет возвращать только числовую часть строки, и адаптировать как приведение, так и выражение where, чтобы использовать его вместо этого. Я не настолько знаком с регулярными выражениями MySQL, чтобы помочь вам в этом. Возможно, вы захотите опубликовать вопрос с просьбой использовать регулярное выражение MySQL для удаления всех числовых символов, и вы, вероятно, получите несколько ответов. Удачи

3. @Nathan В этом случае я предлагаю создать определяемую пользователем функцию, которая анализирует заданную строку для ВСЕХ вхождений чисел и заменяет все эти числа представлением фиксированной ширины, например 000000000012. (Остерегайтесь особых случаев, таких как даты.) Используйте эту функцию в вашем предложении order-by.

Ответ №3:

вероятно, вам следует уйти, сделав что-то вроде этого:

 order by right(replicate(' ',30) Column_name,30)

Ответ №4:

Попробуйте этот порядок:

 ORDER BY RIGHT(REPLICATE('0',128) value,128)

Мой тест:

 DECLARE @T TABLE
(
value VARCHAR(128)
)

INSERT INTO @T VALUES('Apartment'),
('House'),
('Dog'),
('Cat'),
('18 years old'),
('2 years old'),
('1 year old'),
('12 horses'),
('1 horse')


SELECT * FROM @T
ORDER BY RIGHT(REPLICATE('0',128) value,128)

Результаты:

 Cat
Dog
House
1 horse
12 horses
Apartment
1 year old
2 years old
18 years old

Если вы обнаружите, что это не работает, пожалуйста, отправьте его вместе с порядком сортировки, который вы хотели бы, и я могу посмотреть, есть ли исправление.