Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52

    Unanswered: wildcards and ranges

    Running V8.1

    I don't know if its somekind of installation DB2 setting or what but for some reason ranges don't work with the LIKE predicate.

    For example

    Have the following table :-
    CREATE TABLE x
    (
    a vargraphic(10)
    )

    Insert 3 records :-
    insert into x values ('a')
    insert into x values ('aa')
    insert into x values ('aaa')

    Now retreive the data:

    select * from x where a = 'a'
    OK - returns 1 row.

    select * from x where a like 'a'
    OK - returns 1 row.

    select * from x where a like 'a%'
    OK - returns 3 rows.

    select * from x where a like '[a]'
    BAD - Should return 1 row!!!

    select * from x where a like '[a]%'
    BAD - Should return 3 rows!!!

    Thanks in advance for any input on this.

    Andy.
    ahm@exel.co.uk

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Andy,
    DB2 has no construct for ranges while using LIKE.
    It only has % = match zero or more characters and _ = match any single character. When you use LIKE '[a]' DB2 is looking for strings that are exactly = '[a]' . You will need to use some other construct to do ranges.

    Andy

  3. #3
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52
    Really ?

    [] is ANSI standard with wildcards.

    I would have thought DB2 support this method.

    Ohh well - have to do somekind of work around...

    thanks.

Posting Permissions

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