Reputation: 31743

Best way to get right n digits from number in SQL Server (CE)

I have a sql server compact framework database and I want to query an int column

the column can contain values from 1 to 99999999 and I am interested in the right 4 digits

Examples:

     1 ->    1
    12 ->   12
   123 ->  123
  1234 -> 1234
 12345 -> 2345
123456 -> 3456

I could convert the result to string and use substring, but there is propably a better solution.

Upvotes: 7

Answers (4)

Reputation: 26209

You can do Mod 10 for getting the last digit of any number.

Example : 12345%10 = 5

so if you want to get n number of last digits u n number of zeros :

example : if you want last 4 digits use 4 zero's after 1

Sql command: select column% 10000;

ex: 123456-> 123456/10000 = 3456

you can use Division by 10 for getting the first Digits.

Upvotes: 0

Reputation: 7189

SELECT RIGHT(column_name, n)

-- n is number of digits

Upvotes: 3

Reputation: 33139

If you

SELECT WhateverField % 10000

you will get the 4 rightmost digits.

Upvotes: 4

Reputation: 204794

 select 123456 % 10000

Upvotes: 14