Post reply

SELECT - LIKE %

  • December 8, 2011 at 9:27 am

    #391073

    I have a simple SELECT STATEMENT which is not returning data. I am attempting to find all entries ending in "LA"

    SELECT * FROM "Table"

    WHERE "Column" LIKE '%LA'

    GO

    This statement returns no values. If I use '%LA%' I do have data returned but it includes thousand of rows which are not needed. Any idea why this simple statement is not returning data?

    Platform is SQL Server 2008

  • December 8, 2011 at 9:28 am

    #1418764

    if the column you are searching is a char and not varchar, it might not truely end in LA;

    try WHERE RTRIM(ColumnName) LIKE '%LA' instead

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 8, 2011 at 9:36 am

    #1418774

    Lowell,

    Adding rtrim before the column name worked perfectly. The column is actually nchar. Can you tell me what this data type adds to the end of data which requires a trim to be used?

  • December 8, 2011 at 9:44 am

    #1418787

    Spaces, like char. They're only a factor when using LIKE. = ignores trailing spaces, LIKE doesn't (it is documented)

    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

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

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