Saved procedure for selecting the last 6 digits of a number

Question

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

Sort of:

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

Does anyone know how to do this?

Answer 1

 DECLARE @bigOne bigint SET @bigOne = 999333444 SELECT RIGHT(@bigOne, 6) 

Returns the right part of the character string with the specified number of characters. Here is the MSDN for the Right() function: 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 name of your column in the database table and change db to the name of your table in which you SELECT ing.

Answer 2

You can use the modulo operator to easily extract the last 6 digits if num is a numeric data type:

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

Answer 3

Assuming SQL Server,

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

Other flavors may use substr instead of substring .

Answer 4

Move it to a string, then use SUBSTRING and LEN to pull out only the fragment you need.

Answer 5

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

One remark. In SQL Server 2005, the \ character (backslash) does not return 1 ISNUMERIC. But this happens in SQL Server 2008. This function should work in both versions of SQL Server, since we simply exclude this character in the seventh of my test argument above.

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

Scalar 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