如何忽略特定词（如Company）按字母序对公司名称排序？

Absolutely! You can absolutely achieve this custom sorting logic by extracting the core alphabetic part of each company name (ignoring the "Company " prefix when it exists) and sorting based on that extracted value.

The Problem with Your Current Query #

Your current ORDER BY comp_name sorts using the full string's lexicographical order. This means entries like "B" would come before "Company A" (since "B" < "C" in ASCII), and standalone "Company" entries would cluster near the top (since they start with "C")—which doesn't match your desired order.

Solution: Sort by Extracted Core Value #

The key is to isolate the meaningful part of each comp_name:

• For entries like "Company A", extract "A"
• For standalone letters like "E", use the letter itself
• For standalone "Company" entries, define a sorting key that places them where you want (e.g., after all lettered entries, or in a specific position)

Here are examples for common databases:

MySQL/MariaDB

SELECT comp_name
FROM company_detail
ORDER BY
  -- First, prioritize entries with a core letter; push standalone "Company" to the end
  CASE WHEN REGEXP_REPLACE(comp_name, '^Company( |$)', '') = '' THEN 1 ELSE 0 END,
  -- Sort by the extracted core value
  REGEXP_REPLACE(comp_name, '^Company( |$)', '') ASC;

The REGEXP_REPLACE removes the prefix "Company " or standalone "Company", leaving just the core letter (or empty for standalone "Company"). The first CASE ensures lettered entries come first, followed by standalone "Company".

If you specifically want standalone "Company" entries to sit between "F" and "G" (as in your example), adjust the sorting key for those entries:

SELECT comp_name
FROM company_detail
ORDER BY
  CASE
    WHEN comp_name LIKE 'Company %' THEN TRIM(SUBSTRING(comp_name, 9))
    WHEN comp_name = 'Company' THEN 'Fz' -- A value between "F" and "G"
    ELSE comp_name
  END ASC;

PostgreSQL

PostgreSQL uses similar regex logic with REGEXP_REPLACE:

SELECT comp_name
FROM company_detail
ORDER BY
  CASE WHEN REGEXP_REPLACE(comp_name, '^Company( |$)', '', 'g') = '' THEN 1 ELSE 0 END,
  REGEXP_REPLACE(comp_name, '^Company( |$)', '', 'g') ASC;

SQL Server

Use STUFF and CHARINDEX to remove the prefix:

SELECT comp_name
FROM company_detail
ORDER BY
  CASE
    WHEN comp_name LIKE 'Company %' THEN STUFF(comp_name, 1, 9, '')
    WHEN comp_name = 'Company' THEN 'zzz'
    ELSE comp_name
  END ASC;

Result Matching Your Expectation #

With the first MySQL example, your data would sort to:

1. Company A
2. B
3. Company C
4. Company D
5. E
6. Company F
7. Company G
8. Company H
9. Company
10. Company

If you need the standalone "Company" entries between "F" and "G", use the adjusted query with 'Fz' as the sorting key for those entries.

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