Results 1 to 6 of 6

Thread: LIKE in a UDF

  1. #1
    Join Date
    Jul 2009
    Location
    Sydney
    Posts
    13

    Unanswered: LIKE in a UDF

    Hi!

    I am a bit stuck (DB2 9.5)

    here is my UDF

    Code:
    CREATE FUNCTION myfunc( a varchar(1), b varchar(1) ) returns varchar( 1 )
    DETERMINISTIC NO EXTERNAL ACTION 
    BEGIN ATOMIC
    DECLARE tmpstr varchar(2);
    DECLARE result varchar(1);
    SET tmpstr = a||b;
    SET result  = select col from table where refcol like '%'||tmpstr||'%';
    RETURN result;
    END
    and here is the error which I am getting:
    SQL0132N A LIKE predicate or POSSTR scalar function is not valid because the first operand is not a string expression or the second operand is not a string

    any ideas how I could properly cast the inputs so that I can use LIKE in this way??
    Last edited by gamo; 11-12-09 at 19:52.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The patter for a LIKE predicate must be constant. In your case, it depends on the input parameters and is not constant.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jul 2009
    Location
    Sydney
    Posts
    13
    Yes, I understand that - what I was wondering is if there is some way that I can work around this fact.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If you are just looking for substrings, you can use LOCATE(). If you want to allow more general patterns, regular expression matching may be something for you: Bringing the Power of Regular Expression Matching to SQL
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  6. #6
    Join Date
    Jul 2009
    Location
    Sydney
    Posts
    13
    ah - thankyou - I didn't see that one!

    helps when I search for the right thing

Posting Permissions

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