[SQL] 열이 시작하거나 문자로 끝날 수있을 때 부호로 캐스트 수치 MySQL의 정렬 VARCHAR 열을 사용하여

열이 시작하거나 문자로 끝날 수있을 때 부호로 캐스트 수치 MySQL의 정렬 VARCHAR 열을 사용하여

난 정말 확실 여기에 처리하는 방법을 모르겠어요 무언가로 실행했습니다. 나는 스포츠 카드에 대한 정보를 저장하는 데이터베이스를 구축하고있어, 나는 특정 카드를 표시 할 때 약간의 정렬에 문제의 비트를 보내고 있습니다.

배경, 각 카드 (데이터베이스 행), 년에 대한 정보를 가지고있는 카드가 카드 플레이어, 카드 번호, 출신 설정 (즉,보다 많은 정보가있다, 그러나 이것은 여기에 관련이있는 모든이다). 나는 결과를 볼 때, 나는이 일이 년으로 정렬하려면, 다음 설정 한 다음 플레이어, 다음 카드 번호. 올해는 그 정렬 쉬운, 그래서 모두가, VARCHAR에 그냥 정수 및 세트 및 플레이어로 모든하지만, 카드 번호, 벌금을 노력하고 있습니다. 그러나, 카드 번호는 내가 몇 가지 문제로 실행 해요 것입니다.

카드 번호는 문자, 숫자 및 대시를 포함 할 수 있기 때문에 카드 번호 열은 VARCHAR입니다. 가장 통상적으로, 카드 수가 직선 수있을 것이다 (즉, 1, 2, 3, 4, 5), 직선 자 (EX-A, 전 B, 전 C)는 다수의 편지 (1A, 1B 이어 2, 3A, 3B, 3C), 또는 다수의 (A1, A2, A3, A4, A5) 다음 편지. 이것은 내가 현재 설정 내 SQL 문자열의 정렬 부분을 가지고하는 방법입니다 :

order by year desc, cardset asc, subset asc, cast(cardNum as unsigned) asc;

이것은 대부분의 것들의 벌금을 처리한다. 그러나 내가 함께 문제가 있어요 카드의 그룹이 자신의 카드 번호에 같은 문자가있을 때, 그리고 다음 수 있습니다. 나는 정렬이 본질적으로 숫자로 그저 다음 선두 문자를 무시하고 싶다. 그러나, 때로는 더 5ish 카드보다이와이 때 특히, 정렬, 올바르게하지 않습니다.

특히, 잘못 다음과 같은 순서에 다음 카드 번호와 일부 카드를 정렬하는 것 :

그것의 결과를해야하는 경우 :

그것은 현재 직선 번호 정렬되고, 또는 숫자 문자가 올바르게 하였다 (즉, 1, 2, 3, 4, 5, 1, 2, 3A, 3B, 4, 5A, 5B). 나는 그것이 잘못 바로 문자를 정렬에 문제의 인식 아니지만, 나는 확실히 그것을 100 %입니다 아닌지 모르겠어요 그래서 나는 또한 현재이 매우 몇 가지 테스트 케이스를 가지고있다.

다른 종류를 엉망으로하지 않고 내 SQL 정렬 문을 수정하는 방법을 모르고뿐만 아니라, 정말 그것이 위의 BCP 예입니다 순서와 함께오고 방법을 모르겠어요. 그것을 해결하는 방법에 대한 어떤 생각? 나는 우리가 숫자에 도달 할 때까지 카드 번호에 문자를 무시하려고 생각하지만, 그 카드 번호 만 문자 카드에 대한 주요 문제를 일으킬 것입니다. 난 그래서 조금 붙어.

절대 최악의 최악에, 나는 아마이 개 다른 컬럼, 더 자세한 설명되는 부분에 대한 하나, 나는별로 정렬 할 부분 하나에 카드 번호 열을 분할 할 수 온다. 그건 아마 잘 작동 결국,하지만 일을 다시 얻기 위해 많은 작업을 요구한다!

편집 - 여기 내 DB에서 데이터를 포함하여 좀 더 많은 정보는 (죄송합니다 여기에 테이블을 수행하는 방법 서식, 어떤 아이디어)

| year | cardSet | subset                     | cardNum |
| 2016 | Bowman  |                            | 52      |
| 2016 | Bowman  |                            | 54      |
| 2016 | Bowman  |                            | 147     |
| 2016 | Bowman  | Chrome Prospects           | BCP32   |
| 2016 | Bowman  | Chrome Prospects           | BCP61   |
| 2016 | Bowman  | Chrome Prospects           | BCP97   |
| 2016 | Bowman  | Chrome Prospects           | BCP135  |
| 2016 | Topps   |                            | 1       |
| 2016 | Topps   |                            | 2a      |
| 2016 | Topps   |                            | 2b      |
| 2016 | Topps   |                            | 3       |

나는 다음과 같은 순서로 결과를 뱉어 내 일종의 기대 :

그러나, 여기 내 위에 정렬 문으로 제공하고있는 결과는 다음과 같습니다

그것은 단지 숫자, 또는 잘 문자 다음에 숫자 카드 번호를 처리하지만, 카드 번호가 문자로 시작하고 숫자 뒤에 때까지 엉망 일에 경향이있다.

내 SQL의 정렬 일부가되도록 내가 코멘트에 길이 () 트릭을 사용하여 시도했다 :

order by year desc, cardset asc, subset asc, length(cardNum), cardNum asc

그건 제가 위에서 설명 된 문제를 해결하지,하지만 내 예 -에 'Topps에'부분까지 망쳐 놨 그것은 지난 상관없이 번호를 다음없는 문자로 카드를 넣을 수 있습니다. 여기에 내가 그런 종류로 얻을 순서는 다음과 같습니다

해결법

1. ==============================

   1.MariaDB (10)와 MySQL 8 지원 REGEXP_REPLACE. 그 사용하여 사용자 정의 함수를 정의 할 수 있습니다 :

   MariaDB (10)와 MySQL 8 지원 REGEXP_REPLACE. 그 사용하여 사용자 정의 함수를 정의 할 수 있습니다 :

   DROP FUNCTION IF EXISTS zerofill_numbers;
   CREATE FUNCTION zerofill_numbers(str TEXT, len TINYINT)
       RETURNS text
       NO SQL
       DETERMINISTIC
       RETURN REGEXP_REPLACE(
           REGEXP_REPLACE(str, '(\\d+)', LPAD('\\1', len+2, 0)),
           REPLACE('0*(\\d{$len})', '$len', len),
           '\\1'
       );
   

   이제 다음 테스트 데이터를 제공 :

   DROP TABLE IF EXISTS `strings`;
   CREATE TABLE IF NOT EXISTS `strings` (`str` text);
   INSERT INTO `strings` (`str`) VALUES
       ('Bowman 52'),
       ('Bowman 54'),
       ('Bowman 147'),
       ('Bowman Chrome Prospects BCP32'),
       ('Bowman Chrome Prospects BCP61'),
       ('Bowman Chrome Prospects BCP97'),
       ('Bowman Chrome Prospects BCP125'),
       ('Topps 1'),
       ('Topps 3'),
       ('Topps 2a'),
       ('Topps 2b'),
       ('v9.9.3'),
       ('v9.10.3'),
       ('v11.3.4'),
       ('v9.9.11'),
       ('v11.3'),
       ('0.9'),
       ('0.11'),
       ('s09'),
       ('s11'),
       ('s0'),
       ('v9.0.1');
   

   우리는 그것을으로 정렬 할 수 있습니다 :

   SELECT s.str
   FROM strings s
   ORDER BY zerofill_numbers(s.str, 10)
   

   여기에 결과는 다음과 같습니다

   0.9
   0.11
   Bowman 52
   Bowman 54
   Bowman 147
   Bowman Chrome Prospects BCP32
   Bowman Chrome Prospects BCP61
   Bowman Chrome Prospects BCP97
   Bowman Chrome Prospects BCP125
   s0
   s09
   s11
   Topps 1
   Topps 2a
   Topps 2b
   Topps 3
   v9.0.1
   v9.9.3
   v9.9.11
   v9.10.3
   v11.3
   v11.3.4
   

   이 정의 된 길이를 가질 때까지이 함수는 0으로 문자열에 숫자를 입력합니다.

   주 1 :이하지 않습니다 종류의 진수를 올바르게 (0.9 및 0.11 참조). 또한 서명 숫자를 사용하려고해서는 안된다.

   주 2 :이 기능은 다음과 같은 대답을 기반으로합니다 https://stackoverflow.com/a/46099386/5563083 - 당신이 응답 helpfull을 찾아 가서 소스를 upvote에 따라서합니다.

   주 3 : 사용자 정의 함수를 정의하지 않으려면, 당신은 같은 방법으로 인라인을 사용할 수 있습니다 :

   SELECT *
   FROM strings
   ORDER BY
     REGEXP_REPLACE(REGEXP_REPLACE(str, '(\\d+)', LPAD('\\1', 10+2, 0)), '0*(\\d{10})', '\\1')
   

2. ==============================

   2.샘플 데이터의 경우, 길이 () 트릭을 사용할 수 있습니다 :

   샘플 데이터의 경우, 길이 () 트릭을 사용할 수 있습니다 :

   order by year desc, cardset asc, subset asc,
            length(cardNum),
            cardNum asc;
   

   당신은 많은 샘플 데이터를 제공하지 않습니다. 이것은 "집합"내 경우 일반적인 솔루션이 될 것입니다, 모든 카드 번호가 동일한 형태 (그리고 숫자 부분에 앞에 0이없는)을 가지고있다.

from https://stackoverflow.com/questions/48600059/using-mysql-sort-varchar-column-numerically-with-cast-as-unsigned-when-the-colum by cc-by-sa and MIT license