CODEWITHSUNDEEP
sqlsql-server
markyodo
markyodo

Reputation: 5

How to combine SQL queries for same column?

I have searched but not found any examples for my particular problem.

I am trying to strip some unwanted text from a column containing department names. I am trying to combine 2 queries to do this.

This first query strips all characters after the colon in the name:

SELECT
CASE WHEN CHARINDEX(':', DB.Table.DEPT)>0 
THEN
LEFT(DB.Table.DEPT, CHARINDEX(':', DB.Table.DEPT)-1) 
ELSE 
DB.Table.DEPT
END
FROM
DB.Table

The second query strips the prefix from the name:

SELECT
REPLACE(
REPLACE(
REPLACE (DB.Table.DEPT,'[NA1] ','') 
,'[NA2] ', '')
,'[NA3] ', '')
FROM
DB.Table

Both of these work great independent of each other, but when I try to combine them it fails.

SELECT
CASE WHEN CHARINDEX(':', DB.Table.DEPT)>0 
THEN
LEFT(DB.Table.DEPT, CHARINDEX(':', DB.Table.DEPT)-1) 
ELSE 
DB.Table.DEPT
END
FROM
(SELECT
REPLACE(
REPLACE(
REPLACE (DB.Table.DEPT,'[NA1] ','') 
,'[NA2] ', '')
,'[NA3] ', '')
FROM
DB.Table)

I could really use some guidance with this. Thanks in advance.

Upvotes: 0

Views: 55

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

Your query is syntactically incorrect, because you need an alias for the subquery and for the expression result:

SELECT (CASE WHEN CHARINDEX(':', DEPT)>0 
             THEN LEFT(DEPT, CHARINDEX(':', DEPT)-1) 
             ELSE DEPT
        END)
FROM (SELECT REPLACE(REPLACE(REPLACE(t.DEPT,'[NA1] ',''
                                    ), '[NA2] ', ''
                            ), '[NA3] ', ''
                    ) as DEPT
      FROM DB.Table t
     ) t;

EDIT:

To see both the original and new department:

SELECT (CASE WHEN CHARINDEX(':', new_DEPT) > 0 
             THEN LEFT(new_DEPT, CHARINDEX(':', newj_DEPT)-1) 
             ELSE new_DEPT
        END),
        Orig_DEPT
FROM (SELECT REPLACE(REPLACE(REPLACE(t.DEPT,'[NA1] ',''
                                    ), '[NA2] ', ''
                            ), '[NA3] ', ''
                    ) as new_DEPT,
             t.DEPT as orig_DEPT
      FROM DB.Table t
     ) t

Upvotes: 1

bastos.sergio
bastos.sergio

Reputation: 6764

You should always name your subquerys.

Try this:

SELECT
    CASE WHEN CHARINDEX(':', x.DEPT)>0 
    THEN
        LEFT(x.DEPT, CHARINDEX(':', x.DEPT)-1) 
    ELSE 
        x.DEPT
    END AS DEPT
FROM
(
    SELECT
        REPLACE(REPLACE(REPLACE (DEPT,'[NA1] ','') ,'[NA2] ', ''),'[NA3] ', '') AS DEPT
    FROM
    DB.Table
) x

Upvotes: 0

Related Questions