By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,441 Members | 1,261 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,441 IT Pros & Developers. It's quick & easy.

Combine SQL queries for same column.

P: 1
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2. CASE WHEN CHARINDEX(':', DB.Table.DEPT)>0 
  3. THEN
  4. LEFT(DB.Table.DEPT, CHARINDEX(':', DB.Table.DEPT)-1) 
  5. ELSE 
  6. DB.Table.DEPT
  7. END
  8. FROM
  9. DB.Table
The second query strips the prefix from the name:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2. REPLACE(
  3. REPLACE(
  4. REPLACE (DB.Table.DEPT,'[NA1] ','') 
  5. ,'[NA2] ', '')
  6. ,'[NA3] ', '')
  7. FROM
  8. DB.Table
Both of these work great independent of each other, but when I try to combine them it fails.
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2. CASE WHEN CHARINDEX(':', DB.Table.DEPT)>0 
  3. THEN
  4. LEFT(DB.Table.DEPT, CHARINDEX(':', DB.Table.DEPT)-1) 
  5. ELSE 
  6. DB.Table.DEPT
  7. END
  8. FROM
  9. (SELECT
  10. REPLACE(
  11. REPLACE(
  12. REPLACE (DB.Table.DEPT,'[NA1] ','') 
  13. ,'[NA2] ', '')
  14. ,'[NA3] ', '')
  15. FROM
  16. DB.Table)
I could really use some guidance with this.
Thanks in advance.
May 13 '14 #1
Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,430
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.
May 13 '14 #2

P: 2
With what Rabbit said try something like
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.     CASE WHEN CHARINDEX(':', A.DEPT)>0 
  3.     THEN
  4.     LEFT(A.DEPT, CHARINDEX(':', A.DEPT)-1) 
  5.     ELSE 
  6.     A.DEPT
  7.     END
  8. FROM
  9.     (SELECT
  10.     REPLACE( REPLACE( REPLACE (DB.Table.DEPT,'[NA1] ','') ,'[NA2] ', '')    ,'[NA3] ', '') as DEPT
  11.     FROM
  12.     DB.Table) A
  13.  
May 13 '14 #3

Post your reply

Sign in to post your reply or Sign up for a free account.