Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2011
    Posts
    2

    Unanswered: filtering data using ESCAPE and wildcards

    I have a table entries I want to filter by the field, product description, which is CHAR2(60) that has character fields separated by the "_" character. for examples anything_anything_anything_1Ganything_......
    anything_anything_anything_1Nanything_......
    anything_anything_anything_1Ranything_......
    anything_anything_anything_1Danything_......

    I only want returned by my select statement, those values with the "G", the "G" will always be preceded by at least one numeric. but I don't want returned, are values "G"'s anywhere else unless, off course there is a "G" as described earlier... not sure if I'm being clear but my select statement had a LIKE clause..... LIKE '%_%G'.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    There is no CHAR2 datatype in Oracle.

  3. #3
    Join Date
    Sep 2011
    Posts
    2

    oops

    Sorry lost my VAR

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    realize & understand that this problem is more challenging than it should be because the data is not stored according to Third Normal Form.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    It would also help, if you exactly specified, what result do you expect from this data. As you posted sample data, you should state it too.
    Do you want to filter matching rows with entire product description or just cut the matched part from it?
    Shall this row be taken too:
    anything_anything_1BanythinG_1Nanything_
    ?
    I must agree with anacedent, this data model is horrible.

    Anyway, for LIKE operator, you may consult SQL Reference. It is available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/
    For cutting the matched part, you would need to use SUBSTR and INSTR functions.
    If you use at least Oracle 10g and this is not school assignment, think of using REGEXP_LIKE function.

Posting Permissions

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