Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2012
    Posts
    27

    Unanswered: The Like operator using parameters

    What is the equivalent to this select statement with a parameter using the Like operator for DB2?

    SELECT * FROM Table1 WHERE Name Like @Name%

    I tried something like this (Name Like ?'%'), but of course it didn't work.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You cannot use parameter markers. See the documentation:

    Code:
    The expression can be specified by any one of:
    
        A constant
        A special register
        A global variable
        A host variable
        A scalar function whose operands are any of the above
        An expression concatenating any of the above
    
    with the restrictions that:
    
        No element in the expression can be of type LONG VARCHAR, CLOB, LONG VARGRAPHIC, or DBCLOB. In addition, it cannot be a BLOB file reference variable.
        For character columns, the result of the expression must be one character, or a binary string containing exactly one byte (SQLSTATE 22019).
        For graphic columns, the result of the expression must be one character (SQLSTATE 22019).
        The result of the expression must not be a non-spacing combining character sequence (for example, U&'\0301', Combining Acute Accent).
    Andy

  3. #3
    Join Date
    Mar 2012
    Posts
    27
    Hmm...

    I didn't understand exactly the information you supplied me, but let me try to understand the limitations here.

    I can do something like this when passing a parameter (example 1), but I cannot do something like (example 2) because of the wildcard %???

    Is this correct?

    Example 1
    SELECT * FROM Table1 WHERE (Name Like ?)

    Example 2
    SELECT * FROM Table1 WHERE (Name Like '?%')

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Example 2 has no parameter, it only has a literal so it is actually valid, although it will not do what you want.

    Example 2 is valid. Just make sure that the value that you will substitute for the parameter marker has the % in it.

    Andy

  5. #5
    Join Date
    Mar 2012
    Posts
    27
    Thanks Andy for responding back.

    So example 2 would need to be hard coded for it to work like this...

    SELECT * FROM Tabel1 WHERE (Name Like 'Jane%')

    I understand how that works, but there is no way to allow a user to type in a value "representing a parameter" that can use the wildcard when searching people names?

    Does that make sense?

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Well if you read the the documentation, you can do it one of two way.

    1) Your variable string has the % inside as part of the value
    2) Use Concatenate: like ?||'%'


    I prefer to do it with the first method, that way the application has control.

    Andy

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    In my opinion better to use CAST(? as Varchar(LLLL))....
    Last edited by Lenny77; 11-16-12 at 20:04.

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by MrDatabase View Post
    What is the equivalent to this select statement with a parameter using the Like operator for DB2?

    SELECT * FROM Table1 WHERE Name Like @Name%
    Code:
    CREATE PROCEDURE x(IN p VARCHAR(100))
    LANGUAGE SQL
    DYNAMIC RESULT SETS 1
    BEGIN
      DECLARE v VARCHAR(101);
      DECLARE c CURSOR WITH RETURN FOR
        SELECT * FROM Table1 WHERE Name Like v;
      SET v = p || '%';
      OPEN c;
    END
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •