Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Location
    Riga, Latvia
    Posts
    6

    Unanswered: <text> LIKE <column> || '%' or "startswith" function

    Hello,

    I tried to do folliwing:
    db2 => select name from test where 'Super tester' like name || '%'

    But..
    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. SQLSTATE=42824

    Is it possible to implement it in SQL manner w/o UDF?
    Thanks a lot.

    P.S.
    DB2 version 8.2

    db2 => select name from test

    NAME
    --------------------------------
    Super

    1 record(s) selected.

    db2 => describe table test

    Column Type Type
    name schema name Length Scale Nulls
    ------------------------------ --------- ------------------ -------- ----- ------
    NAME SYSIBM VARCHAR 32 0 No

    1 record(s) selected.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    have you tried locate function ?

    with temp1(col1) as
    (
    values('Super'),
    ('per'),
    ('Test'),
    ('XYZ')
    )
    select col1,locate(col1,'Super Tester')
    from
    temp1

    COL1 2
    Super 1
    per 3
    Test 7
    XYZ 0
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Sep 2003
    Location
    Riga, Latvia
    Posts
    6
    Hello sathyaram_s,

    location function, how I understood, simular to "".indexOf("" so, but for me required "".startsWith(" (in java world)

    At current moment I resolved it problem in one way only (udf implementation)
    Simple to define java function, like following:
    public short startsWith( String str1, String str2 ) {
    if( str1.startsWith(str2) )
    return 1;
    else
    return 0;
    }
    and all.

    Thanks a lot for advice.

  4. #4
    Join Date
    Jan 2005
    Posts
    191
    where left('Super Tester' ,length(name)) = name

    James Campbell

  5. #5
    Join Date
    Sep 2003
    Location
    Riga, Latvia
    Posts
    6
    2 jacampbell
    Yep, it's work.
    Thanks a lot for advice.

Posting Permissions

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