CODEWITHSUNDEEP
sqlsql-serverstored-procedures
korymiller
korymiller

Reputation: 357

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?

Upvotes: 7

Views: 46027

Answers (5)

JustBeingHelpful
JustBeingHelpful

Reputation: 18980

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

Upvotes: 0

James Allman
James Allman

Reputation: 41178

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

Upvotes: 5

JonH
JonH

Reputation: 33153

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.

Upvotes: 21

Blindy
Blindy

Reputation: 67386

Assuming SQL Server,

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

Other flavors may use substr instead of substring.

Upvotes: 4

Ian Jacobs
Ian Jacobs

Reputation: 5501

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

Upvotes: 1

Related Questions