LTRIM,RTRIM

  • hi

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

  • 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.

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

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

  • 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

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

  • 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]

  • 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)

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

  • 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)

  • Thanks jeff

  • |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|

  • 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

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

  • 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
  • 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)

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

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