Results 1 to 4 of 4

Thread: Trim or Like

  1. #1
    Join Date
    Feb 2004
    Posts
    25

    Lightbulb Unanswered: Trim or Like

    Hi, I wonder if someone knows what is Optimal:

    Select * from Table where Field like '%value%';
    Or
    Select * from Table where trim(Field) = 'value';

    That is because Field may have trailing spaces, since it is declared as char(10) and there's no chance to change to something like varchar(10).

    Thanks for your answers on this respect!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Hi, I wonder if someone knows what is Optimal:
    Neither.
    both force Full Table Scan so it does not matter which is implemented

    Below is better option because it would allow use of index if one exists
    Select * from Table where Field like 'value%';
    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.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    And another way is

    Select * from Table where Field = rpad('value',10);

    As a good rule, never use LONG or CHAR.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Feb 2004
    Posts
    25

    Cool

    Thanks for your answers anacedent and beilstwh, were very usefull.

    The use of char in this case is for historical reasons and can't be changed

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
  •