Post reply

QOD August 18th 03

  • August 18, 2003 at 2:42 am

    #106441

    Hi,

    Regarding QOD August 18th, there is another way to perform the described search. It is possible to use brackets around the % sign:

    like 5[%]

    This goes for SQL Server 2000 - I do not know with older versions of SQL Server.

    Best regards,

    Benny Tordrup

  • August 18, 2003 at 3:51 am

    #470238

    Never knew about the escape character always used Square brackets. Well we learn something everyday.

    if i understand the use of the escape character correctly ,then there are two correct answers besides no4. no3 will also return the one line result.

  • August 18, 2003 at 4:56 am

    #470244

    Always known about the escape character but never would have considered the use of square brackets. Bit of lateral thinking there... we learn something everyday.

    But (MikeTomkies), only one of the options is correct. You still need a second "un-escaped" % to match all the trailing characters.

    Cheers,

    - Mark


    Cheers,
    - Mark

  • August 18, 2003 at 7:52 am

    #470266

    Won't this answer still return too many results? If you have 15%, 25%, etc, they will be returned when you really only want 5%.

  • August 18, 2003 at 8:07 am

    #470278

    The fact remains that while the answer will work for the rows presented, it's still not correct for retreiving only those records with '5%' in them. Any records with '15%', '25%', etc will also be returned with the solution provided. A space should be added between the first % and the 5 in answer 4 to return the correct recordset.

  • August 18, 2003 at 8:39 am

    #470280

    quote:

    The fact remains that while the answer will work for the rows presented, it's still not correct for retreiving only those records with '5%' in them. Any records with '15%', '25%', etc will also be returned with the solution provided. A space should be added between the first % and the 5 in answer 4 to return the correct recordset.

    Excellent catch

    Brian Knight

    bknight@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bknight

    Brian Knight
    Free SQL Server Training Webinars

  • August 18, 2003 at 9:16 am

    #470284

    I was about to answer none of the above, because none would work for 4.5% or 15%. Two days ago I entered incorrectly when I overlooked such a detail with the keyword table column name.

    I chose the "correct" answer because it works for the results presented. My answer would be with a space before the 5, eliminating the other possibilities, or better yet: 

    select * from rates where RateDescription like '%[^1-9,.]5/%%' ESCAPE '/'

    The problem is that it's not always possible to predict all the variations in text strings - especially if it's been typed in. The right way is to normalize. In reality however, existing data is often not normalized.

    Data: Easy to spill, hard to clean up!

  • August 18, 2003 at 10:17 am

    #470293

    since when did like '5%%' stop working?

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

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