CODEWITHSUNDEEP
sqlsql-server-2008
Khrys
Khrys

Reputation: 2774

Using replace in select clause

Trying to use the REPLACE to rename the start and end of the string. The string that I have is, for example: ABCD - [001]. I wanto to get just the 001 and count.

Example: SQLFIDDLE

The result should be:

Description  Total
        001      4
        002      2
        003      3

Upvotes: 0

Views: 48

Answers (3)

M.Ali
M.Ali

Reputation: 69494

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');

Query 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

Results:

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

Upvotes: 1

Spaceman Spiff
Spaceman Spiff

Reputation: 934

You need to use CASE WHEN LEN(Description)=3 THEN Description ELSE SUBSTRING(Description, 9, 3) END to select the description column.

Your query should look like

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Your description fields (at least in the example) all have the numbers in the same position. So, the easiest way to get them is 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;

Upvotes: 1

Related Questions