Stored Procedure to SELECT Last 6 Digits of Number

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?

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.

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 = ?

Assuming SQL Server,

SELECT subtring(num,len(num)-6,6) FROM db
WHERE user = @user

Other flavors may use substr instead of substring.

Cast it to a string then use SUBSTRING and LEN to pull only the piece you need.

This will accurately get the last 6 digits from the argument you pass.

One thing to note. In SQL Server 2005, the "\" (backslash) character does not return 1 by ISNUMERIC. But it does in SQL Server 2008. This function should work in both versions of SQL Server since we're simply excluding this character in the 7th piece of my test argument above.

PRINT RIGHT(dbo.fnNumericDigits('5-555-555551-2345-6-'), 6)

Scalar-valued Function:

ALTER FUNCTION [dbo].[fnNumericDigits]
(
    @startString NVARCHAR(255)
) RETURNS NVARCHAR(255)
AS
BEGIN

    DECLARE @returnString NVARCHAR(255)
    DECLARE @length INT, @currentDigit INT, @currentCharacter NVARCHAR(1)
    SELECT @length = LEN(@startString), @currentDigit = 1, @returnString = ''

    WHILE @currentDigit <= @length
    BEGIN

    SELECT @currentCharacter = SUBSTRING(@startString, @currentDigit, @currentDigit)

    IF ISNUMERIC(@currentCharacter) = 1 
        AND @currentCharacter != '$'
        AND @currentCharacter != '+'
        AND @currentCharacter != ','
        AND @currentCharacter != '-'
        AND @currentCharacter != '.'
        AND @currentCharacter != '\'
    BEGIN
    SELECT @returnString = @returnString + @currentCharacter
    END

    SELECT @currentDigit = @currentDigit + 1

END

RETURN @returnString
END