SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


LTRIM,RTRIM


LTRIM,RTRIM

Author
Message
mantoniosouza1
mantoniosouza1
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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|
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15972 Visits: 19524
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
kk1173
kk1173
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 89
Then what is the need of LTRIM and RTRIM functions in SQL
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86078 Visits: 45226
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
kk1173
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 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)


Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25775 Visits: 17509
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 Modens 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)
kk1173
kk1173
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 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.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25775 Visits: 17509
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 Modens 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)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86078 Visits: 45226
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, 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
kk1173
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search