Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Atlanta, GA
    Posts
    21

    Unanswered: selecting unique values

    Hi All,

    I'm trying to return unique data for the TESTID field from one of my tables (test). Data from this field sometimes have some weird characters such as ./-() and spaces. I have used the TRANSLATE function to convert all the fields such that 'AA123.123' is translated to 'AA123123', however if there is a field with 'BB125.123' and another in the correct form of 'BB125123' (which does need conversion). The data is returned as the same thing, 'BB125123' hence two occurence of the same id. I have tried using DINSTINCT w/o success. Pls help

    select distinct translate(testid, '123456789-/.()''''','123456789') , testid
    from test;

    translate(testid testid
    ------------------ ---------
    AA123123 AA123.123
    BB125123 BB125.123
    BB125123 BB125/123
    BB125123 BB125123
    It pays to share!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: selecting unique values

    The DISTINCT applies to the whole selected row. If you leave out the un-translated testid, it will work:

    select distinct translate(testid, '123456789-/.()''''','123456789')
    from test;

    translate(testid
    ------------------
    AA123123
    BB125123

  3. #3
    Join Date
    Feb 2004
    Location
    Atlanta, GA
    Posts
    21
    Thanks andrew, I selected the testid again just to compare the original data with the translated one. Thanks a lot.
    Last edited by daymauler; 02-13-04 at 16:06.
    It pays to share!

Posting Permissions

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