Combine SQL queries for same column.

[markyodo]

Hello all this is my first post.
I am trying to strip some unwanted text from a column containing department names.

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.

[Rabbit]

Please use code tags when posting code or formatted data.

The problem with your combined query is that you never give the expression you created an alias. You also didn't alias your subquery.

[landrew21]

With what Rabbit said try something like

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