where clause on varchar2 field
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: where clause on varchar2 field

Hybrid View

  1. #1
    Join Date
    Jan 2001
    Posts
    30
    I have at table where a field called taxing_district_code is a varchar2(16). In the following select I get 58 rows

    select wr_no, revision_no, point_span_seq_id, taxing_district_code
    from wr_point_span
    where wr_no = 4317 and revision_no = 2 and taxing_district_code = 474

    if I change the where clause to:

    select wr_no, revision_no, point_span_seq_id, taxing_district_code
    from wr_point_span
    where wr_no = 4317 and revision_no = 2 and taxing_district_code = '474'
    order by wr_no, revision_no, point_span_seq_id

    I only get 17 rows. The only difference is putting ' around the 474 value.

    Can someone explain this?

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    in some of the rows you have blanks righ or left padded to your '474' string.

    In your first queryOracle performs implicit TO_NUMBER conversion, trimming all the blanks, thats why it returns all the rows with or without blanks arround the string '474'. In second query it returns only the rows with string '474' without blanks.

    Run the following query and check for yourself:

    SELECT REPLACE(taxing_district_code,' ','_'), COUNT(*)
    FROM wr_point_span
    GROUP BY REPLACE(taxing_district_code,' ','_')
    WHERE taxing_district_code = 474;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Jan 2001
    Posts
    30
    Thanks jurij

Posting Permissions

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


Click Here to Expand Forum to Full Width