Truncate text

[Zurich98]

Hello All,

I'm using SQL 2005. I have a text column with the following data:

CourseName
Physic
Physic One - SS retake - Oct
Physic One - SS retake - Nov
Physic One - SS retake - Dec

Chemistry Beg
Chemistry Beg - SS retake - Oct
Chemistry Beg - SS retake - Nov
etc...

How do I truncate all the characters after the first - occurence. so the goal is to get the course name as follow:

CourseName
Physic One
Chemistry Beg

Your help/suggestion is greatly appreciated.

Thanks

 

[snitin78]

Here is an example:

declare @tempCourses table (course_name varchar(55))

insert into @tempCourses values ('Physic One - SS retake - Oct')
insert into @tempCourses values ('Physic One - SS retake - Nov')
insert into @tempCourses values ('Physic One - SS retake - Dec')

insert into @tempCourses values ('Chemistry Beg')
insert into @tempCourses values ('Chemistry Beg - SS retake - Oct')
insert into @tempCourses values ('Chemistry Beg - SS retake - Nov')

select rtrim(SUBSTRING(course_name, 0, CHARINDEX('-', course_name)))
from @tempCourses

Nitin

 

[PetMit]

Select distinct rtrim(SUBSTRING(CourseName, 0, CHARINDEX('-', CourseName)))
from Course

 

[Zurich98]

Thank you for your quick response. However, the code only work when coursename contain '-'. It does not return a value for Physic One and Chemistry Beg (first two rows in the example). Please advise. Thanks

 

[Zurich98]

i use the case statement below and it seems to work but not sure if this is the most effective way to go about it.

case when CourseName like '%-%' then
rtrim(SUBSTRING(CourseName, 0, CHARINDEX('-', CourseName)))

else CourseName
end