Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Truncate text

Status
Not open for further replies.

Zurich98

Programmer
Apr 8, 2006
64
US
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
 
Here is an example:

Code:
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
 
Select distinct rtrim(SUBSTRING(CourseName, 0, CHARINDEX('-', CourseName)))
from Course
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top