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, February 1, 2013 5:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 1, 2013 6:07 AM
Points: 1, Visits: 23
|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|
Post #1414609
Posted Friday, February 1, 2013 6:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:43 AM
Points: 6,890, Visits: 14,254
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1414620
Posted Tuesday, April 2, 2013 1:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 1, 2013 11:59 AM
Points: 38, Visits: 89
Then what is the need of LTRIM and RTRIM functions in SQL
Post #1438068
Posted Tuesday, April 2, 2013 2:24 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 40,615, Visits: 37,081
To remove spaces on the left or right of a string.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1438082
Posted Tuesday, April 2, 2013 2:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 1, 2013 11:59 AM
Points: 38, Visits: 89
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)


Post #1438085
Posted Tuesday, April 2, 2013 2:52 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
kk1173 (4/2/2013)
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)



The query you posted second is not sargable. What that means is that if there is an index on ColVal it will be ignored and the engine will perform a scan instead of a seek.

From Jeff's post on page 1:


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.






_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1438092
Posted Tuesday, April 2, 2013 2:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 1, 2013 11:59 AM
Points: 38, Visits: 89
So both the queries will return same results? I want to include leading and trailing spaces from table2.
If I understanding correctly, LTRIM/RTRIM does not make any difference.
Post #1438094
Posted Tuesday, April 2, 2013 3:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
kk1173 (4/2/2013)
So both the queries will return same results? I want to include leading and trailing spaces from table2.
If I understanding correctly, LTRIM/RTRIM does not make any difference.


If you want to include the leading and trailing spaces why are using either of the trim functions at all?

Those two queries will NOT necessarily return the same thing. When comparing string values trailing spaces will be ignored but leading spaces will not. In your case you might have ' this value ' in one table but 'this value ' in the other table. The only way those two values will be the same is if you LTRIM the values. Does that help to clarify?


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1438097
Posted Tuesday, April 2, 2013 3:08 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 40,615, Visits: 37,081
kk1173 (4/2/2013)
Is it not better to have the following instead? It will bring in those values that have trailing and leading spaces also.


So you have dirty data and would rather make queries perform badly than fix the data?

Unless leading spaces are significant and meaningful, data properly cleaned and inserted should not have leading spaces. Trailing spaces will be ignored, so there's no need for RTRIM in your example. You only need LTRIM if you know you have bad data.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1438104
Posted Tuesday, April 2, 2013 3:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 1, 2013 11:59 AM
Points: 38, Visits: 89
Sorry, I was not clear. I want include values from table column that has leading/trailing spaces also.
For e.g.

table1
Col
----------
' ABC'
'XYZ '
' MNR'
' MCR'

table2
Col
-------
'XYZ'
'MNR'


Select * from table1 where Col IN (Select Col from table2)
I want to return XYZ and MNR values as a result of this query.

Looks like i do not need to do any trimming even though table1 have spaces
Post #1438105
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse