Oracle数据库含文本与数字的数据排序异常问题咨询

I get it—your current ORDER BY with LPAD is causing sorting chaos because it’s treating your values as strings instead of honor the numeric logic you need. Lexicographical string sorting sees "LEGO 15" as "smaller" than "LEGO 2" (since '1' comes before '2' character-wise), which is why LEGO 15 is landing in the wrong spot.

To get your desired order (1-11 first, then letter-suffixed entries grouped by their base number, and LEGO 15 at the very end), we need to split LINENAME into two distinct sorting components: the numeric part (treated as an actual number) and any trailing letter suffix.

Here’s the corrected SQL query:

SELECT * 
FROM WA_GA_TBL_LINES 
WHERE LINENAME LIKE 'LEGO%' 
  AND SECTIONID_FK = 'SC0013' 
  AND LINENAME != 'LEGO 16' 
ORDER BY
  -- Extract the numeric portion and sort as a number (not string)
  TO_NUMBER(REGEXP_SUBSTR(LINENAME, '\d+')) ASC,
  -- Sort by trailing letter suffix; convert NULLs (no suffix) to empty strings for consistency
  NVL(REGEXP_SUBSTR(LINENAME, '[A-Z]+$'), '') ASC;

Breakdown of how this works: #

• REGEXP_SUBSTR(LINENAME, '\d+') grabs all consecutive digits from your LINENAME (e.g., "12" from "LEGO 12A"). Wrapping this in TO_NUMBER() ensures we sort numerically (1, 2, ..., 12, 13, ..., 15) instead of using string order.
• REGEXP_SUBSTR(LINENAME, '[A-Z]+$') pulls any trailing uppercase letters (e.g., "A" from "LEGO 12A"). NVL() turns NULL values (for entries like "LEGO 15" with no suffix) into empty strings, so they sort consistently relative to suffixed entries.

This will output exactly the order you want:
LEGO 1 → LEGO 11 → LEGO 12A → LEGO 12B → LEGO 13A → LEGO 13B → LEGO 14A → LEGO 14B → LEGO 15

内容的提问来源于stack exchange，提问作者HiDayurie Dave