SQL 选择数字的最后 6 位的存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6230665/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Stored Procedure to SELECT Last 6 Digits of Number
提问by korymiller
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.
我在 SQL 表中有一个数据字段，其中有一个大数字（9 位数字，客户信息编号）。我想运行一个只会选择数字的最后 6 位数字的存储过程。
Something like:
就像是：
SELECT (Last 6 Digits of num) FROM db
WHERE user = @user
Does anyone know of a way to accomplish this?
有谁知道实现这一目标的方法？
回答by JonH
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
返回具有指定字符数的字符串的右侧部分。这里也是该Right()功能的 MSDN ：http:
//msdn.microsoft.com/en-us/library/ms177532.aspx
In your case corey you can do:
在你的情况下，corey 你可以这样做：
SELECT RIGHT(num, 6) FROM db WHERE user=@user
SELECT RIGHT(num, 6) FROM db WHERE user=@user
Just replace numwith the name of your column from your database table, and change dbto the name of your table that you are SELECTing from.
只需替换num为数据库表中列的名称，然后更改db为您正在使用的表的名称SELECT。
回答by James Allman
回答by Blindy
Assuming SQL Server,
假设 SQL Server，
SELECT subtring(num,len(num)-6,6) FROM db
WHERE user = @user
Other flavors may use substrinstead of substring.
可以使用其他口味substr代替substring。
回答by Ian Jacobs
Cast it to a string then use SUBSTRING and LEN to pull only the piece you need.
将它投射到字符串上，然后使用 SUBSTRING 和 LEN 只拉出您需要的部分。
回答by MacGyver
This will accurately get the last 6 digits from the argument you pass.
这将准确地从您传递的参数中获取最后 6 位数字。
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.
需要注意的一件事。在 SQL Server 2005 中，“\”（反斜杠）字符不会通过 ISNUMERIC 返回 1。但它在 SQL Server 2008 中确实如此。这个函数应该在两个版本的 SQL Server 中都可以使用，因为我们只是在上面的测试参数的第 7 部分中排除了这个字符。
PRINT RIGHT(dbo.fnNumericDigits('5-555-555551-2345-6-'), 6)
打印权利（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
