Stored Procedure to SELECT Last 6 Digits of Number

Question

I have a data field in a SQL table with a large number (9 digits, A Customer Information Number). I want to run a stored procedure that will only SELECT the last 6 digits of the number.

Something like:

SELECT (Last 6 Digits of num) FROM db
WHERE user = @user

Does anyone know of a way to accomplish this?

Answer 1

DECLARE @bigOne bigint

SET @bigOne = 999333444

SELECT RIGHT(@bigOne, 6)

Returns the right part of a character string with the specified number of characters. Here is the MSDN for the Right() function as well: http://msdn.microsoft.com/en-us/library/ms177532.aspx

In your case corey you can do:

SELECT RIGHT(num, 6) FROM db WHERE user=@user

Just replace num with the name of your column from your database table, and change db to the name of your table that you are SELECTing from.

Answer 2

You can use the modulo operator to easily extract the last 6 digits assuming num is a numeric datatype:

select num % 1000000 as num
from db
where user = ?