Post reply

Doubt - Like Clause

  • December 6, 2010 at 2:50 pm

    #388374

    Hello,

    I am running a query :

    SELECT * FROM TABLE1 WHERE LTRIM(RTRIM(column1)) = 'abc'

    SELECT * FROM TABLE1 WHERE LTRIM(RTRIM(column1)) LIKE 'abc%'

    The first query does not return any record at all and the second query does return few records.

    I went ahead and updated the column1:

    UPDATE TABLE1

    SET column1 = LTRIM(RTRIM(column1))

    However, I still do not get anyy records with the first query.

    Am I missing something here ?

    Thanks in advance!

  • December 6, 2010 at 3:01 pm

    #1259244

    The two queries are different.

    The first one would not return a row with column1='abcd', the 2nd one would.

    It might be possible there are other character than a space.

    Can you post table def and the sample data in question?


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • December 6, 2010 at 3:18 pm

    #1259255

    Thanks for the reply.

    The table definition is big and thus I did not post it here.

    The column1 is varchar(100) NULL.

    I meant that the second query results in records where column1 = 'abc' and the first query doesn't.

    Is there a setting in SQL Server 2005, which can ignore spaces.

    I really can't see any issue with the queries I am running.

    Thanks again.

  • December 6, 2010 at 3:55 pm

    #1259276

    touchmeknot (12/6/2010)

    Thanks for the reply.

    The table definition is big and thus I did not post it here.

    The column1 is varchar(100) NULL.

    I meant that the second query results in records where column1 = 'abc' and the first query doesn't.

    Is there a setting in SQL Server 2005, which can ignore spaces.

    I really can't see any issue with the queries I am running.

    Thanks again.

    I can't see any issue either.

    Therefore I've asked for a table def (the one column inquestion is fine).

    And the value in question (please verify that you can repeat the results on the test setup).


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • December 7, 2010 at 9:48 am

    #1259640

    Thanks for the response. I am really wondering, there must be some setting in SSMS for sure.

    I ran the following queries in order to check if RTRIM is working and it works fine.

    DECLARE @var varchar(30)

    SET @var = ' Test '

    SET @var = LTRIM(RTRIM(@var))

    SELECT @var

    However, the update mentioned in the previous post results in - 'Test ' i.e. Test with a space at the end.

    Strange 🙁

Viewing 5 posts - 1 through 4 (of 4 total)

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