Risk Factors with using LTRIM or RTRIM?  
Author Message
spamkiller





PostPosted: Fri Nov 18 18:17:42 CST 2005 Top

SQL Server Developer >> Risk Factors with using LTRIM or RTRIM?

I am wondering if there is any risk involved using LTRIM or RTRIM that cut
off data. What I mean by "cut off data" is that by using LTRIM (or RTRIM),
data (other than empty space) is lost or deleted.

And why should we not just use TRIM instead of using RTRIM and LTRIM together?
For instance: UPPER(RTRIM(LTRIM(sFirstName)))

I would appreciate if anybody has any comment.

SQL Server268  
 
 
Adam





PostPosted: Fri Nov 18 18:17:42 CST 2005 Top

SQL Server Developer >> Risk Factors with using LTRIM or RTRIM? A) No.

B) Because there is no TRIM function in T-SQL.


--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--




>I am wondering if there is any risk involved using LTRIM or RTRIM that cut
> off data. What I mean by "cut off data" is that by using LTRIM (or RTRIM),
> data (other than empty space) is lost or deleted.
>
> And why should we not just use TRIM instead of using RTRIM and LTRIM
> together?
> For instance: UPPER(RTRIM(LTRIM(sFirstName)))
>
> I would appreciate if anybody has any comment.


 
 
Andrew





PostPosted: Fri Nov 18 18:26:03 CST 2005 Top

SQL Server Developer >> Risk Factors with using LTRIM or RTRIM? In addition to Adam's correct remarks make sure this is only used in the
selected column list and not in the WHERE clause. The use of Trim, Upper
etc. will negate the use of an index seek.

--
Andrew J. Kelly SQL MVP




> A) No.
>
> B) Because there is no TRIM function in T-SQL.
>
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>


>>I am wondering if there is any risk involved using LTRIM or RTRIM that cut
>> off data. What I mean by "cut off data" is that by using LTRIM (or
>> RTRIM),
>> data (other than empty space) is lost or deleted.
>>
>> And why should we not just use TRIM instead of using RTRIM and LTRIM
>> together?
>> For instance: UPPER(RTRIM(LTRIM(sFirstName)))
>>
>> I would appreciate if anybody has any comment.
>
>


 
 
ML





PostPosted: Fri Nov 18 18:47:11 CST 2005 Top

SQL Server Developer >> Risk Factors with using LTRIM or RTRIM? > In addition to Adam's correct remarks make sure this is only used in the
> selected column list and not in the WHERE clause. The use of Trim, Upper
> etc. will negate the use of an index seek.

Plus: if the use of LTRIM/RTRIM ever were needed in the where clause, then
the table (and/or the database) might benefit greatly from better
normalization - i.e. trim the blanks before the values are inserted into the
table.


ML
 
 
Adam





PostPosted: Fri Nov 18 19:21:12 CST 2005 Top

SQL Server Developer >> Risk Factors with using LTRIM or RTRIM? PLUS... :)

I often see people using RTRIM on VARCHAR columns and variables, which is
totally unnecessary. VARCHAR automatically trims white space to the right.


--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--




>> In addition to Adam's correct remarks make sure this is only used in the
>> selected column list and not in the WHERE clause. The use of Trim, Upper
>> etc. will negate the use of an index seek.
>
> Plus: if the use of LTRIM/RTRIM ever were needed in the where clause, then
> the table (and/or the database) might benefit greatly from better
> normalization - i.e. trim the blanks before the values are inserted into
> the
> table.
>
>
> ML


 
 
ML





PostPosted: Fri Nov 18 20:56:03 CST 2005 Top

SQL Server Developer >> Risk Factors with using LTRIM or RTRIM? Must be one of them good ol' better-safe-than-sorry deals I recon. Don't get
caught in yer own sling there, partner. No, siree Bob.

:)


ML
 
 
Razvan





PostPosted: Sat Nov 19 01:12:56 CST 2005 Top

SQL Server Developer >> Risk Factors with using LTRIM or RTRIM?
> I often see people using RTRIM on VARCHAR columns and variables, which is
> totally unnecessary. VARCHAR automatically trims white space to the right.

This is not entirely correct. If a space is explicitely inserted at the
end of a varchar column, it is not trimmed automatically, so a RTRIM
might be useful. For example:

CREATE TABLE #T (X varchar(10) PRIMARY KEY)

INSERT INTO #T VALUES ('A')
INSERT INTO #T VALUES ('B ')

SELECT X, X+'X', RTRIM(X)+'X' FROM #T

DROP TABLE #T

Razvan

 
 
Tibor





PostPosted: Sat Nov 19 03:26:05 CST 2005 Top

SQL Server Developer >> Risk Factors with using LTRIM or RTRIM? Still in 6.5 compat mode, Adam? ;-)

SET ANSI_PADDINGS determines whether trailing spaces are to be trimmed for varchars. ANSI dictates
that trailing spaces should be preserved, so nowadays ANSI_PADDINGS are on by default. As much as I
hate it...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/




> PLUS... :)
>
> I often see people using RTRIM on VARCHAR columns and variables, which is totally unnecessary.
> VARCHAR automatically trims white space to the right.
>
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>


>>> In addition to Adam's correct remarks make sure this is only used in the
>>> selected column list and not in the WHERE clause. The use of Trim, Upper
>>> etc. will negate the use of an index seek.
>>
>> Plus: if the use of LTRIM/RTRIM ever were needed in the where clause, then
>> the table (and/or the database) might benefit greatly from better
>> normalization - i.e. trim the blanks before the values are inserted into the
>> table.
>>
>>
>> ML
>
>

 
 
Adam





PostPosted: Sun Nov 20 09:32:48 CST 2005 Top

SQL Server Developer >> Risk Factors with using LTRIM or RTRIM?

> Still in 6.5 compat mode, Adam? ;-)

Apparently so ;)

I was recently on a project in which I had to go through and remove a
bunch of LTRIM(RTRIM(Col)) from all of the WHERE clauses of around 400
stored procedures, and I think it fried my brain, thereby inducing a
flashback.

Thanks Razvan and Tibor for setting me on the road to recovery!


--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--


 
 
Tibor





PostPosted: Mon Nov 21 07:27:25 CST 2005 Top

SQL Server Developer >> Risk Factors with using LTRIM or RTRIM? > I was recently on a project in which I had to go through and remove a bunch of
> LTRIM(RTRIM(Col)) from all of the WHERE clauses of around 400 stored procedures,

Sounds like great fun! <g>


> and I think it fried my brain,

No kidding... :-)

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/






>> Still in 6.5 compat mode, Adam? ;-)
>
> Apparently so ;)
>
> I was recently on a project in which I had to go through and remove a bunch of
> LTRIM(RTRIM(Col)) from all of the WHERE clauses of around 400 stored procedures, and I think it
> fried my brain, thereby inducing a flashback.
>
> Thanks Razvan and Tibor for setting me on the road to recovery!
>
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>


 
 
JustinDoh





PostPosted: Mon Nov 21 12:06:07 CST 2005 Top

SQL Server Developer >> Risk Factors with using LTRIM or RTRIM? Thank you for your help.



> In addition to Adam's correct remarks make sure this is only used in the
> selected column list and not in the WHERE clause. The use of Trim, Upper
> etc. will negate the use of an index seek.
>
> --
> Andrew J. Kelly SQL MVP
>
>


> > A) No.
> >
> > B) Because there is no TRIM function in T-SQL.
> >
> >
> > --
> > Adam Machanic
> > Pro SQL Server 2005, available now
> > http://www.apress.com/book/bookDisplay.html?bID=457
> > --
> >
> >


> >>I am wondering if there is any risk involved using LTRIM or RTRIM that cut
> >> off data. What I mean by "cut off data" is that by using LTRIM (or
> >> RTRIM),
> >> data (other than empty space) is lost or deleted.
> >>
> >> And why should we not just use TRIM instead of using RTRIM and LTRIM
> >> together?
> >> For instance: UPPER(RTRIM(LTRIM(sFirstName)))
> >>
> >> I would appreciate if anybody has any comment.
> >
> >
>
>
>
 
 
JustinDoh





PostPosted: Mon Nov 21 12:06:07 CST 2005 Top

SQL Server Developer >> Risk Factors with using LTRIM or RTRIM? Thank you for your help.



> A) No.
>
> B) Because there is no TRIM function in T-SQL.
>
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>


> >I am wondering if there is any risk involved using LTRIM or RTRIM that cut
> > off data. What I mean by "cut off data" is that by using LTRIM (or RTRIM),
> > data (other than empty space) is lost or deleted.
> >
> > And why should we not just use TRIM instead of using RTRIM and LTRIM
> > together?
> > For instance: UPPER(RTRIM(LTRIM(sFirstName)))
> >
> > I would appreciate if anybody has any comment.
>
>
>
 
 
JustinDoh





PostPosted: Mon Nov 21 12:08:04 CST 2005 Top

SQL Server Developer >> Risk Factors with using LTRIM or RTRIM? Thank you all for your help.



> > I was recently on a project in which I had to go through and remove a bunch of
> > LTRIM(RTRIM(Col)) from all of the WHERE clauses of around 400 stored procedures,
>
> Sounds like great fun! <g>
>
>
> > and I think it fried my brain,
>
> No kidding... :-)
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
>




> >> Still in 6.5 compat mode, Adam? ;-)
> >
> > Apparently so ;)
> >
> > I was recently on a project in which I had to go through and remove a bunch of
> > LTRIM(RTRIM(Col)) from all of the WHERE clauses of around 400 stored procedures, and I think it
> > fried my brain, thereby inducing a flashback.
> >
> > Thanks Razvan and Tibor for setting me on the road to recovery!
> >
> >
> > --
> > Adam Machanic
> > Pro SQL Server 2005, available now
> > http://www.apress.com/book/bookDisplay.html?bID=457
> > --
> >
> >
>
>
>