string query - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

string query

Author

Topic

att
Starting Member

2 Posts

Posted - 2009-04-01 : 10:45:46

Hi Friends,

In my query there is one column called - Description and it has the values like given below:

Summer_2006_zine 7/25/2006 11:31:49 AM
Fall_2006_zine 11/7/2006 3:22:50 PM
Winter_2007_zine 1/31/2007 11:07:59 AM
Winter_2007_zine_FINAL1/31/2007 5:11:40 PM
Spring_2007_zine 5/9/2007 2:23:40 PM
Spring_2007_zine_FINAL

now i want values before first blank space- so i have done like-

select left(description,nullif(charindex(' ',description)-1,-1)) from sales

i m getting the values correct but not for last one. means i m getting -

Summer_2006_zine
Fall_2006_zine
Winter_2007_zine
Winter_2007_zine_FINAL1/31/2007
Spring_2007_zine
NULL

as i m gettng null for last value.
select left('Spring_2007_zine_FINAL',nullif(charindex(' ','Spring_2007_zine_FINAL')-1,-1)) - null

which i don't want that..i want the excat value before space..so i think i have to put one condition but don't know what?

do anyone have any idea?

Thanks for ur help if anyone can solve this..

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-01 : 10:51:39

Try this..

SELECT CASE WHEN charindex(' ',description) > 0 THEN
left(description,nullif(charindex(' ',description)-1,-1))
ELSE
description
END

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-01 : 10:57:02

will the description part before the date always end in zine? for 4th row did you want to include the date Winter_2007_zine_FINAL1/31/2007 ?
Vijay's solution is best
<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

att
Starting Member

2 Posts

Posted - 2009-04-01 : 11:02:19

thanks for ur help Vijay. i appreciate it..i got perfect output.

Thanks

yosiasz: no the description part will not end with zine always as these are sample values of some of 200 records...

thanks for replying.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources