#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    40
    Rep Power
    1

    Removing last number


    Hi!
    I have ID numbers with 4 and 5 digits, for those values with 5 digits I want to remove the last digit so that all IDs are 4 digit values.

    Anyone know the syntax?

    SELECT Murex_SPS_SecondaryTrades.NB_EXT
    FROM Murex_SPS_SecondaryTrades, dbo_sdCounterpartyPrices;

    I want to remove the last digit from NB_EXT
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,400
    Rep Power
    1688
    When you say last digit, do you mean the 5 from 12345 or the 1?
    In either case there's a couple of ways of doing it. Can we also gather that the field is an INT of some sort?
    Not sure if you'd need to convert/cast the column to varchar/string first, but most likely you'd end up with something like:
    Code:
    LEFT(myNumberColumn,4)
    You could do that to all rows returned as doing it to an already 4-digit column would have no impact on the data. Testing to see if you need to do so first gives options: IF value > 9999 would be one way, but would be a lesser option if there was a chance your digit could grow to 6 numbers. A little more 'wordy' but a touch more elegant in that case would be to use a CASE statement.

    If you want to truncate the '1' then just change the LEFT to a RIGHT.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    40
    Rep Power
    1
    Originally Posted by SimonJM
    When you say last digit, do you mean the 5 from 12345 or the 1?
    In either case there's a couple of ways of doing it. Can we also gather that the field is an INT of some sort?
    Not sure if you'd need to convert/cast the column to varchar/string first, but most likely you'd end up with something like:
    Code:
    LEFT(myNumberColumn,4)
    You could do that to all rows returned as doing it to an already 4-digit column would have no impact on the data. Testing to see if you need to do so first gives options: IF value > 9999 would be one way, but would be a lesser option if there was a chance your digit could grow to 6 numbers. A little more 'wordy' but a touch more elegant in that case would be to use a CASE statement.

    If you want to truncate the '1' then just change the LEFT to a RIGHT.
    Thank you! Worked
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    40
    Rep Power
    1
    Originally Posted by ChristopherL
    Thank you! Worked
    Another question... What is a static table?
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    Originally Posted by ChristopherL
    Another question... What is a static table?
    it's where you pile your laundry after taking it out of the dryer when you forgot to use fabric softener

    ok, srsly, it's any table that isn't a temporary table
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo