trim - 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)

trim

Author

Topic

jaskiew
Starting Member

4 Posts

Posted - 2010-08-25 : 15:16:09

Hello,
I need to trim the last 4 characters of a string (not trailing spaces).
For example, I have a string

123-0910-001

i want to remove -001. The length of the string changes.
Thanks.

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-08-25 : 15:23:50

declare @temp varchar(1000)
set @temp = '123-0910-001'

select @temp as [original string], substring(@temp, 0, len(@temp)-3) as [trim string]

-- result
original string trim string
123-0910-001 123-0910

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2010-08-25 : 20:25:04

declare @temp varchar(1000)
set @temp = '123-0910-001'

select cast(@temp as varchar) as 'Orginal string',substring(@temp,1,len(@temp) -4)as 'Trimmed string'

Orginal string Trimmed string

123-0910-001 123-0910

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-26 : 11:38:27

or just

LEFT(string,LEN(string)-4)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-26 : 11:48:37

That will cause a problem on strings of less than 4 characters so if any of your data is like that beware.

You can use a CASE statement to make it safe (or a WHERE LEN(string) >= 4
Example:



DECLARE @foo VARCHAR(255)

SET @foo = '1234-123-1231'
SELECT LEFT(@foo, LEN(@foo) - CASE WHEN LEN(@foo) >= 4 THEN 4 ELSE LEN(@foo) END)

SELECT LEFT(@foo,LEN(@foo)-4)

SET @foo = '123'
SELECT LEFT(@foo, LEN(@foo) - CASE WHEN LEN(@foo) >= 4 THEN 4 ELSE LEN(@foo) END)

SELECT LEFT(@foo,LEN(@foo)-4)

The last line gives




Msg 537, Level 16, State 2, Line 11
Invalid length parameter passed to the LEFT or SUBSTRING function.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-08-27 : 02:04:50

Hmmm ... what's with SUBSTRING(xxx, 0, nnn) ?



SET NOCOUNT ON
DECLARE @foo VARCHAR(255)

SET @foo = '1234-123-54321'
SELECT SUBSTRING(@foo, 1, LEN(@foo) - 4)
SELECT SUBSTRING(@foo, 0, LEN(@foo) - 3)

SET @foo = '1234'
SELECT SUBSTRING(@foo, 1, LEN(@foo) - 4)
SELECT SUBSTRING(@foo, 0, LEN(@foo) - 3)

SET @foo = '123'
--SELECT SUBSTRING(@foo, 1, LEN(@foo) - 4)	-- Error
SELECT SUBSTRING(@foo, 0, LEN(@foo) - 3)

SET @foo = '12'
--SELECT SUBSTRING(@foo, 1, LEN(@foo) - 4)	-- Error
--SELECT SUBSTRING(@foo, 0, LEN(@foo) - 3)	-- Error

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources