LTRIM,RTRIM

Question

LTRIM,RTRIM

daveriya

SSCrazy

Points: 2352
More actions
October 21, 2011 at 7:29 am

#249808

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

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

toddasd SSCertifiable Points: 6298 More actions · Answer 1

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.

daveriya SSCrazy Points: 2352 More actions · Answer 2

so it gives orderid values with no spaces on left or right ?

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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

daveriya SSCrazy Points: 2352 More actions · Answer 4

i tried it it gives me blank,not the valus,but i think rtrim and ltrim just remove blank spaces ,not the values

vince_sql Right there with Babe Points: 722 More actions · Answer 5

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/[/url]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ghanshyam.kundu Hall of Fame Points: 3337 More actions · Answer 7

ghanshyam.kundu

Hall of Fame

Points: 3337

October 24, 2011 at 12:31 am

#1398514

jeff can u tell us what is SARGable WHERE clause??

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ghanshyam.kundu Hall of Fame Points: 3337 More actions · Answer 9

ghanshyam.kundu

Hall of Fame

Points: 3337

October 24, 2011 at 5:04 am

#1398601

Thanks jeff

mantoniosouza1 Valued Member Points: 59 More actions · Answer 10

|0000|005|0|01012012|31012012|CARLOS FERNANDES TEXTIL LTDA-ME|04806213000166||GO|103474781|5208707|||A|0|

|0001|0|

|0005|LIMA LIMÃO TEXTIL|74520040|AV MARECHAL DEODORO DA FONSECA|78|QD 110 LT 14|SETOR CAMPINAS|6232813125| ||

|0100|LAERTE DE BORTOLO JOSE|67883249800|000000|08528315000118|74335104|AV T63|||PARQUE ANHAGUERA|6232813125||cesar1787@terra.com.br|5208707|

|0150|000000000000001624|SACRIS TEXTIL LTDA|1058|04903143000164||254347690|4208906||RUA GUILHERME WEEGE,240,|||CENTRO|

|0150|000000000000001625|MALHAS MENEGOTTI INDUSTRIA TEXTIL LTDA.|1058|10474553000130||255735898|4208906||R.Joaquim Francisco de Paula, 4850 PD.1, 0,|||CHICO DE PAULA|

|0190|KG|KG.|

|0200|000000001449|VISCOLYCRA 96% VISCOSE e 4%ELASTANO|||KG|00|60064200||||0,00|

|0200|000000001450|CAPRI 40%VISCOSE 37%ALGODAO 7%POLIESTER 7%LINHO 6%ACRILICO 3%ELASTANOLARG:1,85M|||KG|00|60064300||||7,00|

|0200|000000001451|CAPRI 40%VISCOSE 37%ALGODAO 7%POLIESTER 7%LINHO 6%ACRILICO 3%ELASTANOLARG:1,85M|||KG|00|60064300||||7,00|

|0200|000000001452|CAPRI 40%VISCOSE 37%ALGODAO 7%POLIESTER 7%LINHO 6%ACRILICO 3%ELASTANOLARG:1,85M|||KG|00|60064300||||7,00|

|0200|000000001453|CAPRI 40%VISCOSE 37%ALGODAO 7%POLIESTER 7%LINHO 6%ACRILICO 3%ELASTANOLARG:1,85M|||KG|00|60064300||||7,00|

|0200|000000001454|CAPRI 40%VISCOSE 37%ALGODAO 7%POLIESTER 7%LINHO 6%ACRILICO 3%ELASTANOLARG:1,85M|||KG|00|60064300||||7,00|

|0200|000000001455|CAPRI 40%VISCOSE 37%ALGODAO 7%POLIESTER 7%LINHO 6%ACRILICO 3%ELASTANOLARG:1,85M|||KG|00|60064300||||7,00|

|0460|027370|EMPRESA OPTANTE PELO SIMPLES NACIONAL NAO GERA DIREITO A CREDITO DE IPI, ISS E ICMS PERMITE O APROVEITAMENTO DO CREDITO DE ICMS NO VALOR DE R$ 145,99 CORRESPONDENTE A ALIQUOTA DE 1,25% NOS TERMOS DO ART. 23 DA LC 123|

|0460|026342|EMPRESA OPTANTE PELO SIMPLES NACIONAL NAO GERA DIREITO A CREDITO DE IPI, ISS E ICMS PERMITE O APROVEITAMENTO DO CREDITO DE ICMS NO VALOR DE R$ 139,33 CORRESPONDENTE A ALIQUOTA DE 1,25% NOS TERMOS DO ART. 23 DA LC 123|

|0460|027371|Pedido: 317668 Romaneio: 182121|

|0990|18|

|C001|0|

|C100|0|1|000000000000001624|55|00|1|623|42120104903143000164550010000006231000006238|18012012|18012012|11679,30|1|0,00||11679,30|1|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|

|C170|1|000000001449||2595,400|KG|11679,30|0,00|0|090|2102||0,00|0,00|0,00|0,00|0,00|0,00||||||||||||||||||||

|C190|090|2102|0,00|11679,30|0,00|0,00|0,00|0,00|0,00|0,00|027370|

|C195|027370|EMPRESA OPTANTE PELO SIMPLES NACIONAL NAO GERA DIREITO A CREDITO DE IPI, ISS E ICMS PERMITE O APROVEITAMENTO DO CREDITO DE ICMS NO VALOR DE R$ 145,99 CORRESPONDENTE A ALIQUOTA DE 1,25% NOS TERMOS DO ART. 23 DA LC 123|

|C100|0|1|000000000000001624|55|00|1|629|42120104903143000164550010000006291000006290|23012012|23012012|11146,50|1|0,00||11146,50|1|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|

|C170|1|000000001449||2477,000|KG|11146,50|0,00|0|090|2102||0,00|0,00|0,00|0,00|0,00|0,00||||||||||||||||||||

|C190|090|2102|0,00|11146,50|0,00|0,00|0,00|0,00|0,00|0,00|026342|

|C195|026342|EMPRESA OPTANTE PELO SIMPLES NACIONAL NAO GERA DIREITO A CREDITO DE IPI, ISS E ICMS PERMITE O APROVEITAMENTO DO CREDITO DE ICMS NO VALOR DE R$ 139,33 CORRESPONDENTE A ALIQUOTA DE 1,25% NOS TERMOS DO ART. 23 DA LC 123|

|C100|0|1|000000000000001625|55|00|1|183027|42120110474553000130550010001830271183905474|23012012|23012012|8521,74|1|0,00||8521,74|1|0,00|0,00|0,00|8521,74|596,52|0,00|0,00|0,00|0,00|0,00|0,00|0,00|

|C170|1|000000001450||112,910|KG|2032,38|0,00|0|000|2101||2032,38|7,00|142,27|0,00|0,00|0,00||||||||||||||||||||

|C170|2|000000001451||73,070|KG|1315,26|0,00|0|000|2101||1315,26|7,00|92,07|0,00|0,00|0,00||||||||||||||||||||

|C170|3|000000001452||66,850|KG|1203,30|0,00|0|000|2101||1203,30|7,00|84,23|0,00|0,00|0,00||||||||||||||||||||

|C170|4|000000001453||66,580|KG|1198,44|0,00|0|000|2101||1198,44|7,00|83,89|0,00|0,00|0,00||||||||||||||||||||

|C170|5|000000001454||57,190|KG|1029,42|0,00|0|000|2101||1029,42|7,00|72,06|0,00|0,00|0,00||||||||||||||||||||

|C170|6|000000001455||96,830|KG|1742,94|0,00|0|000|2101||1742,94|7,00|122,00|0,00|0,00|0,00||||||||||||||||||||

|C190|000|2101|7,00|8521,74|8521,74|596,52|0,00|0,00|0,00|0,00|027371|

|C195|027371|Pedido: 317668 Romaneio: 182121|

|C990|19|

|D001|1|

|D990|2|

|E001|0|

|E100|01012012|31012012|

|E110|0,00|0,00|123738,68|0,00|596,52|0,00|0,00|0,00|123142,16|0,00|0,00|0,00|0,00|0,00|

|E111|GO000018|FISCALIZAÇÃO|123738,68|

|E200|SC|01012012|31012012|

|E210|0|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|0,00|

|E500|0|01012012|31012012|

|E520|0,00|0,00|0,00|0,00|0,00|0,00|0,00|

|E990|9|

|G001|1|

|G990|2|

|H001|1|

|H990|2|

|1001|1|

|1990|2|

|9001|0|

|9900|0000|1|

|9900|0001|1|

|9900|0005|1|

|9900|0100|1|

|9900|0150|2|

|9900|0190|1|

|9900|0200|7|

|9900|0460|3|

|9900|0990|1|

|9900|C001|1|

|9900|C100|3|

|9900|C170|8|

|9900|C190|3|

|9900|C195|3|

|9900|C990|1|

|9900|D001|1|

|9900|D990|1|

|9900|E001|1|

|9900|E100|1|

|9900|E110|1|

|9900|E111|1|

|9900|E200|1|

|9900|E210|1|

|9900|E500|1|

|9900|E520|1|

|9900|E990|1|

|9900|G001|1|

|9900|G990|1|

|9900|H001|1|

|9900|H990|1|

|9900|1001|1|

|9900|1990|1|

|9900|9001|1|

|9900|9900|36|

|9900|9990|1|

|9900|9999|1|

|9990|39|

|9999|93|

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Do you have a question?

^{“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

kk1173 Ten Centuries Points: 1000 More actions · Answer 12

Then what is the need of LTRIM and RTRIM functions in SQL

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

To remove spaces on the left or right of a string.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kk1173 Ten Centuries Points: 1000 More actions · Answer 14

If I have a query like

Select * from table1 WHERE ColVal IN (Select AllVals from table2)

Is it not better to have the following instead? It will bring in those values that have trailing and leading spaces also.

Select * from table1 WHERE LTRIM(RTRIM(ColVal))

IN (Select LTRIM(RTRIM(AllVals)) from table2)