Stripping of the last 10 characters

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)

Stripping of the last 10 characters

Author

Topic

shebert
Yak Posting Veteran

85 Posts

Posted - 2010-11-04 : 15:19:41

hello can anyone tell me how to strip the last ten characters of a string?

part= CST-90-W57S-C12-GN201

want to return: cst-90-w57s

Thanks Steve

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-04 : 15:24:16

select left(column,len(column)-10)

No, you're never too old to Yak'n'Roll if you're too young to die.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-11-04 : 15:33:31

This will work even the string isn't >= 10 characters

declare @part varchar(25)
SET @part = 'CST-90-W57S'
select substring(@part,1,10)

Jim

Everyday I learn something that somebody else already knew

shebert
Yak Posting Veteran

85 Posts

Posted - 2010-11-04 : 15:35:00

reverse(substring((reverse(IMA_NoRevisionItemID)),11,20))

this works too....Thanks all

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-04 : 15:35:57

Ok then
select left(column,len(column)-10)
from table
where len(column)>= 10

No, you're never too old to Yak'n'Roll if you're too young to die.

shebert
Yak Posting Veteran

85 Posts

Posted - 2010-11-05 : 10:48:47

so
some of my part numbers where not the same lenth.

here is the code i used to strip off the last 10 char of parts.

the last 10 chars all had the same format -XXX-XXXXX

,reverse(substring((substring(reverse(IMA_NoRevisionItemID),charindex('-',reverse(IMA_NoRevisionItemID))+1,20)),
charindex('-',(substring(reverse(IMA_NoRevisionItemID),charindex('-',reverse(IMA_NoRevisionItemID))+1,20)))+1
,20))

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-05 : 11:14:23

left(IMA_NoRevisionItemID,len(IMA_NoRevisionItemID)-10)

will handle that and looks less complicated...

No, you're never too old to Yak'n'Roll if you're too young to die.

shebert
Yak Posting Veteran

85 Posts

Posted - 2010-11-05 : 12:38:18

get this error from your code Fred

Invalid length parameter passed to the substring function.

shebert
Yak Posting Veteran

85 Posts

Posted - 2010-11-05 : 12:43:30