Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2013
    Posts
    4

    Question Unanswered: Removing or Changing digits in a number

    Okay, this one should be simple and can be solved in one of two ways. I have two seperate tables with a 12 digit identifying number that is ALMOST exactly the same in each. I am trying to match up these two tables, which works amazingly well, except for the occasional case where the 12 digit ID (which is called API) ends in 01, instead of 00. The last two digits do not make a difference at all, but in one table it will end in 00 and the other it will end in 01, even though they are the same record.

    Basically, it looks like this...
    Table 1---------------Table 2
    541236554700-----541236554700
    541236123700-----541236123700
    443231246700-----443231246701


    So basically, in my final query, the last entry will not show up since these two are not related by this API (ID) number. Does anyone know of a way to either replace the 01 on the end with 00... OR to remove the last two digits?

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Using integer division, divide by 100. If you want to compare 12 digits, then multiply by 100.

    Code:
    Select t1.api, t2.api
    from table1 t1
    inner join table2 t2 on (t2.api / 100) = (t1.api / 100)
    Of course you could do this as an update statement to every number before the query.

    Code:
    Update table1 SET
       api = api / 100 * 100
    
    Update table2 SET
       api = api / 100 * 100
    Assuming of course that api is indeed of type int. If they are not int's, Cast them into ints!
    Last edited by LinksUp; 07-13-13 at 16:47.

Posting Permissions

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