SELECT
  CASE
    WHEN CHARINDEX('  ', prd_name) > 0
     AND CHARINDEX('  ', prd_name, CHARINDEX('  ', prd_name) + 2) > 0
    THEN SUBSTRING(
           prd_name,
           CHARINDEX('  ', prd_name) + 2,
           CHARINDEX('  ', prd_name, CHARINDEX('  ', prd_name) + 2)
             - CHARINDEX('  ', prd_name) - 2
         )
    ELSE prd_name
  END AS color
FROM tf_pss
WHERE ps_no = :ps_no AND itm = :itm;

新版本如下：

SELECT
  CASE
    WHEN c.color IS NOT NULL THEN c.color
    WHEN CHARINDEX(' ', r.rest) > 0
      THEN LEFT(r.rest, CHARINDEX(' ', r.rest) - 1)
    WHEN CHARINDEX('（', r.rest) > 0
      THEN LEFT(r.rest, CHARINDEX('（', r.rest) - 1)
    ELSE r.rest
  END AS color
FROM tf_pss
CROSS APPLY (
  SELECT LTRIM(SUBSTRING(prd_name, CHARINDEX(' ', prd_name) + 1, LEN(prd_name))) AS rest
) r
OUTER APPLY (
  SELECT TOP 1 color
  FROM (VALUES
    ('白色'),('黑色'),('红色'),('橙色'),('黄色'),('绿色'),('蓝色'),('紫色'),
    ('粉色'),('灰色'),('银色'),('金色'),('棕色'),('青色'),('玫红'),('酒红'),
    ('深空灰'),('浅灰'),('米色'),('杏色'),('卡其'),('军绿'),('藏青'),('香槟'),
    ('透明'),('彩色'),('花色'),('古铜'),('玫瑰金'),('天空蓝'),('湖蓝'),
    ('薄荷绿'),('咖啡'),('驼色'),('姜黄'),('宝蓝'),('天蓝'),('墨绿'),('荧光绿')
  ) AS colors(color)
  WHERE r.rest LIKE color + '%'
  ORDER BY LEN(color) DESC
) c
WHERE ps_no = :ps_no and itm=:itm