Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

LTRIM,RTRIM Expand / Collapse
Author
Message
Posted Friday, October 21, 2011 7:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 11, 2011 11:22 AM
Points: 100, Visits: 236
hi

i have this code in where clause RTRIM(LTRIM(ODER_ID))= ' ',WHAT THis do
Post #1194372
Posted Friday, October 21, 2011 7:43 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 11, 2013 3:13 PM
Points: 477, Visits: 3,671

i have this code in where clause RTRIM(LTRIM(ODER_ID))= ' ',WHAT THis do


It filters out rows with non-blank oder_id values. RTRIM and LTRIM simply remove spaces from a string on the right and left.

As an example;

with SampleTable as (
select ' hi ' as oder_id, 1 as Col2 union all
select ' ', 2 union all
select '', 3
)
select * from SampleTable where rtrim(ltrim(oder_id))=''



______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Post #1194384
Posted Friday, October 21, 2011 7:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 11, 2011 11:22 AM
Points: 100, Visits: 236
so it gives orderid values with no spaces on left or right ?


or it just says orderid should be blank.plz confirm me
Post #1194388
Posted Friday, October 21, 2011 7:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 7,120, Visits: 13,491
daveriya (10/21/2011)
so it gives orderid values with no spaces on left or right ?


or it just says orderid should be blank.plz confirm me


Try experimenting with the sample code which Todd set up for you - you're far more likely to remember the lesson if you get involved.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1194392
Posted Friday, October 21, 2011 7:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 11, 2011 11:22 AM
Points: 100, Visits: 236
i tried it it gives me blank,not the valus,but i think rtrim and ltrim just remove blank spaces ,not the values
Post #1194401
Posted Sunday, October 23, 2011 8:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 5, 2013 10:31 AM
Points: 44, Visits: 263
daveriya (10/21/2011)
hi

i have this code in where clause RTRIM(LTRIM(ODER_ID))= ' ',WHAT THis do


It's just removing extra blank spaces, but this is un-necessary as SQL views empty strings as empty strings regardless how many spaces there are.

So if your query is trying to return only empty strings, then you dont need to trim.

The code below highlights this, if SQL considered

'' != '         '

the above to be true, then the penultimate select query would only return 1 row, but it returns 2.

CREATE TABLE trimTest (
value varchar(50)
)
INSERT INTO trimTest
VALUES (''),
(' ')

SELECT *
FROM trimTest

SELECT *
FROM trimTest
WHERE value = ''

SELECT *
FROM trimTest
WHERE LTRIM(RTRIM(value)) = ''



http://sqlvince.blogspot.com/
Post #1194957
Posted Sunday, October 23, 2011 9:43 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:14 AM
Points: 36,735, Visits: 31,186
vince_sql (10/23/2011)
daveriya (10/21/2011)
hi

i have this code in where clause RTRIM(LTRIM(ODER_ID))= ' ',WHAT THis do


It's just removing extra blank spaces, but this is un-necessary as SQL views empty strings as empty strings regardless how many spaces there are.

So if your query is trying to return only empty strings, then you dont need to trim.

The code below highlights this, if SQL considered

'' != '         '

the above to be true, then the penultimate select query would only return 1 row, but it returns 2.

CREATE TABLE trimTest (
value varchar(50)
)
INSERT INTO trimTest
VALUES (''),
(' ')

SELECT *
FROM trimTest

SELECT *
FROM trimTest
WHERE value = ''

SELECT *
FROM trimTest
WHERE LTRIM(RTRIM(value)) = ''



AND, Vince's query uses a SARGable WHERE clause, as well!


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1194969
Posted Monday, October 24, 2011 12:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 4:17 AM
Points: 235, Visits: 435
jeff can u tell us what is SARGable WHERE clause??
Post #1195033
Posted Monday, October 24, 2011 4:31 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:14 AM
Points: 36,735, Visits: 31,186
SARG stands for "Search ARGument" and "SARGable" has come to mean that the search arguments in a WHERE clause, ORDER BY, and/or ON clause are capable of doing an INDEX SEEK if the appropriate index is available and used.

Search arguments that modify a column will only allow for either a table scan (includes Clustered Index Scan) or a non-Clustered Index Scan.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1195103
Posted Monday, October 24, 2011 5:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 4:17 AM
Points: 235, Visits: 435
Thanks jeff
Post #1195128
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse