interesting query

  • Hi friends

    I have a interesting situation here.

    I'm currently running sql 2000 and i have table like following

    tablename :tab1

    flields   :fld1 varchar(5),fld2 varchar(2000)

    i have data like following

    '!6mTK','checked in'

    'Ph[(L','Family Practice Unit University of Adela'

    when i run following query it works fine

    select * from tab1 where fld1 like '!6mTK'

    but it does not return any data when i say

    select * from tab1 where fld1 like 'Ph[(L'

    but it works when i say

    select * from tab1 where fld1 = 'Ph[(L'

    is it something to do with keywords in the data?if so what

    is work around?

    I need to use "Like" condition not "=" bcoz its dynamic query.

    Any ideas please

    Thank you very much

  • When you use the LIKE operator you need to include a wildcard character in the criteria.

    EG: select * from tab1 where fld1 like 'Ph[(L%'

    This will return all rows where fld1 begins with Ph[(L

    --------------------
    Colt 45 - the original point and click interface

  • Yes,i did still it does not work.

    with out % 1st query runs but why not 2nd one?

  • While using wild chaaracters one needs to use the escape characters while using symbols like % and [. Look in BOL for the syntax for I am not recollecting it right now.

    You have to dig for tons of dirt to get an ounce of Gold

  • Yes that is it. Look at "Pattern Matching in Search Conditions" in Books Online.

    --------------------
    Colt 45 - the original point and click interface

  • Thanks guys i'm trying..

  • i found following books online

    If the LIKE '5%' symbol is specified, SQL Server searches for the number 5 followed by any string of zero or more characters.

    For example, this query shows all system tables in a database, because they all begin with the letters sys:

    SELECT TABLE_NAMEFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_NAME LIKE 'sys%'

    To see all objects that are not system tables, use NOT LIKE 'sys%'. If you have a total of 32 objects and LIKE finds 13 names that match the pattern, NOT LIKE finds the 19 objects that do not match the LIKE pattern.

    You may not always find the same names with a pattern such as LIKE '[^s][^y][^s]%'. Instead of 19 names, you may get only 14, with all the names that begin with s or have y as the second letter or have s as the third letter eliminated from the results, as well as the system table names. This is because match strings with negative wildcards are evaluated in steps, one wildcard at a time. If the match fails at any point in the evaluation, it is eliminated.

  • i tried again like following but still did not work

     

    select * from tab1where fld1 like 'Ph[(L' escape '['

    select * from tab1where fld1 like  like '[Ph[(L]'

  • Try

    select * from tab1where fld1 like  like 'Ph[[](L'

    Note the use of [] to enclose the wildcard character in the data.

     

    --------------------
    Colt 45 - the original point and click interface

  • hi Phill Carter

    it worked like a charm 

    but how can use it in my dynamic query

    some times there may be [ char in my data so how frame my query .for example see my possible data in my initial post.

    Thank you very much

  • I'm presuming this is running via a stored procedure and you pass in the criteria using a parameter.

    That being the case you would need to validate the passed parameter before executing the query.

    Use the REPLACE function to change [ into [[] and % into [%]

    EG:

    CREATE PROCEDURE List @fld varchar(10)
    AS
    
    SET @fld = REPLACE(@fld,'[','[[]'
    SET @fld = REPLACE(@fld,'%','[%]'
    
    select * from tab1 where fld1 like @fld
    

    --------------------
    Colt 45 - the original point and click interface

  • Dear Phill Carter

    you are 100% right.yes i am using it in stored proc.

    I'll implement your suggestion.

    Thank you so much

  • Dear Phill Carter

    i implemented it and its working fine .

    Thank you very much

  • Glad to be of service.

     

    --------------------
    Colt 45 - the original point and click interface

  • If you're using dynamic SQL, you could substitute CHAR(39) for the single quote, CHAR(91) for the left bracket and CHAR(93) for the right bracket, as follows:

    ------------------------------------------------------

    DECLARE @SQL varchar(200)

    SELECT @SQL = 'SELECT * FROM tab1 WHERE fld1 LIKE ' + CHAR(39) + 'Ph' + CHAR(91) + '(' + 'L' + CHAR(39)

    EXEC(@SQL)

    ------------------------------------------------------

    It will generate the following:  'SELECT * FROM tab1 WHERE fld1 LIKE 'Ph[(L'

    I prefer using these ASCII characters because it's easier to separate search characters from wildcard characters.



    Dana
    Connecticut, USA
    Dana

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

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