Splitting results from a colon

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

General SQL Server Forums

New to SQL Server Programming

Splitting results from a colon

Author

Topic

joshuag
Starting Member

2 Posts

Posted - 2011-03-23 : 00:56:55

Hi. I am fairly new to SQL and I am working on a report using SQL Server 2005.

I am trying to create a query that displays the results from a table, but only up until the first colon. For instance, the data will be like below:

bookings services : insurance
bookings services : insurance
bookings services : phone and wireless
bookings services : phone and wireless
bookings services : travel gear : altrec : Columbia Boston University Men's Collegiate Fastbreak Fu
bookings services : travel gear : altrec : Hiking Shoes
bookings services : travel gear : altrec : Kelty Falcon 2850 Backpack

but want it to display like:

bookings services
bookings services
bookings services
bookings services
bookings services
bookings services
bookings services

I've tried substrings and trimming and looked on the web but have had no luck. Any help appreciated.

Thanks

Josh

matty
Posting Yak Master

161 Posts

Posted - 2011-03-23 : 01:49:28

SELECT SUBSTRING(Col,1,CHARINDEX(':',Col)-1 ) FROM YourTable

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-23 : 08:04:03

If there is a possibility that some rows would not have a colon, you should add a check to matty's code:

SELECT case when Col like '%:%' then SUBSTRING(Col,1,CHARINDEX(':',Col)-1 ) else Col end FROM YourTable

joshuag
Starting Member

2 Posts

Posted - 2011-03-23 : 20:11:13

Thanks both. The secon option worked great. I think the first one failed because there are some entries without the colon.

Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources