Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bdp000
Starting Member
1 Post |
 Posted - 2008-04-29 : 03:32:47
|
| Hello, I am new here and to sql so please bare with me.I am trying to create 2 select statements from the same field. The field is a job_description field that sometimes contains PO#'s. One select statement needs to extract the PO# if it has been written in the field and the other needs to extract all data that is not the PO#Here is some example of the data:New shirts for 2007Magazine cover for may editionWay to go postcards PO# 45687 PO#879For the first select statement I need to capture just the PO#. the result for the above examples would be:NULLNULL45687879For the second select statement I need everything but the PO#. The result would be:New shirts for 2007Magazine cover for may editionWay to go postcards NULLSo far I have put together a basic idea for the PO# part:(SELECT SUBSTRING (job_description , (SELECT patindex('%PO#%', job_description)+3),10)WHERE job_description LIKE '%po#%')If i could figure out a way to only return numeric data from the above i think it will work.As for the second select statement i am at a loss on how to select everything but the PO#.Any help at all is very much appreciated.Thanks |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2008-04-29 : 04:04:49
|
| use CASE.. it's an if statement you can embed within your query.. cool huh?--------------------keeping it simple... |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-29 : 04:05:41
|
| SELECT CASE WHEN patindex('%PO#%',field) >0 THEN NULLIF(LEFT(field,patindex('%PO#%',field)-1),'') ELSE field END FROm @Temp |
|
|
|
|
|
|
Pulling out PO# from the rest of the job descrip.