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 togethe
r?
For instance: UPPER(RTRIM(LTRIM(sFirstName)))
I would appreciate if anybody has any comment.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
--
"Justin Doh" <JustinDoh@.discussions.microsoft.com> wrote in message
news:27733494-6FD6-41A9-B531-F6798FE04582@.microsoft.com...
>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.|||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 s.
Andrew J. Kelly SQL MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:u3hjt6J7FHA.2036@.TK2MSFTNGP14.phx.gbl...
> 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
> --
>
> "Justin Doh" <JustinDoh@.discussions.microsoft.com> wrote in message
> news:27733494-6FD6-41A9-B531-F6798FE04582@.microsoft.com...
>|||> 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 s.
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|||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
--
"ML" <ML@.discussions.microsoft.com> wrote in message
news:7ECEDBF4-B806-4C37-8FA8-885D8FE7DDC6@.microsoft.com...
> 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|||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|||Adam Machanic wrote:
> I often see people using RTRIM on VARCHAR columns and variables, which is
> totally unnecessary. VARCHAR automatically trims white space to the right.[/color
]
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|||Still in 6.5 compat mode, Adam? ;-)
SET ANSI_PADDINGS determines whether trailing spaces are to be trimmed for v
archars. ANSI dictates
that trailing spaces should be preserved, so nowadays ANSI_PADDINGS are on b
y 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/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:O4UFMeK7FHA.1420@.TK2MSFTNGP09.phx.gbl...
> 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
> --
>
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:7ECEDBF4-B806-4C37-8FA8-885D8FE7DDC6@.microsoft.com...
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eAytBtO7FHA.1944@.TK2MSFTNGP14.phx.gbl...
> 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
--|||> I was recently on a project in which I had to go through and remove a bunch ofarkred">
> LTRIM(RTRIM(Col)) from all of the WHERE clauses of around 400 stored procedures,[/
color]
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/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OVrmvee7FHA.2040@.TK2MSFTNGP14.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eAytBtO7FHA.1944@.TK2MSFTNGP14.phx.gbl...
> Apparently so ;)
> I was recently on a project in which I had to go through and remove a b
unch of
> LTRIM(RTRIM(Col)) from all of the WHERE clauses of around 400 stored proce
dures, 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
> --
>