在select子句中使用replace

Question

尝试使用REPLACE来重命名字符串的开始和结束。我有的字符串是，例如:ABCD - [001]。我只想得到001和计数。

例子:SQLFIDDLE

结果应该是:

Description  Total
        001      4
        002      2
        003      3

Answer 1

您的描述字段(至少在示例中)都有相同位置的数字。因此，获得它们的最简单方法是substring():

SELECT (case when Description LIKE '%/[___/]%' ESCAPE '/' then substring(description, 9, 3)
             else Description
        end) as Description,
        COUNT (*) AS Total
FROM Table1
WHERE Description LIKE '%/[___/]%' ESCAPE '/' OR Description LIKE '___'
GROUP BY (case when Description LIKE '%/[___/]%' ESCAPE '/' then substring(description, 9, 3)
               else Description
          end)
ORDER BY Description ASC;

Answer 2

您需要使用CASE WHEN LEN(Description)=3 THEN Description ELSE SUBSTRING(Description, 9, 3) END来选择描述列。

你的查询应该看起来像

SELECT
    CASE WHEN LEN(Description)=3 THEN Description ELSE SUBSTRING(Description, 9, 3) END as Description,
    COUNT (*) AS Total
FROM
    Table1
WHERE
    Description LIKE '%/[___/]%' ESCAPE '/' OR Description LIKE '___'
GROUP BY
    Description
ORDER BY
    Description ASC

Answer 3

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE Table1 
  (
    Description varchar(20)
  );
INSERT INTO Table1
  (
    Description
  )
VALUES
  ('ABCD - [001]'),('ABCD - [001]'),('XIo9 - [001]'),('001'),
  ('XYZW - [002]'),('002'),('XYZW - [003]'),('XYZW - [003]'),('003');

查询1

:

SELECT
    RIGHT(REPLACE(REPLACE(RIGHT('0000' + Description,4), '[', ''),']',''),3) Description,
    COUNT (*) AS Total
FROM
    Table1
GROUP BY
    RIGHT(REPLACE(REPLACE(RIGHT('0000' + Description,4), '[', ''),']',''),3)
ORDER BY
    Description ASC

结果

:

| DESCRIPTION | TOTAL |
|-------------|-------|
|         001 |     4 |
|         002 |     2 |
|         003 |     3 |