从 SQL 语句中的字段中删除前导零
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/92093/
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
Removing leading zeroes from a field in a SQL statement
提问by Tim C
I am working on a SQL query that reads from a SQLServer database to produce an extract file. One of the requirements to remove the leading zeroes from a particular field, which is a simple VARCHAR(10)field. So, for example, if the field contains '00001A', the SELECT statement needs to return the data as '1A'.
我正在处理从 SQLServer 数据库读取以生成提取文件的 SQL 查询。从特定字段中删除前导零的要求之一，这是一个简单的VARCHAR(10)字段。因此，例如，如果字段包含“00001A”，则 SELECT 语句需要将数据返回为“1A”。
Is there a way in SQL to easily remove the leading zeroes in this way? I know there is an RTRIMfunction, but this seems only to remove spaces.
SQL 中有没有办法以这种方式轻松删除前导零？我知道有一个RTRIM函数，但这似乎只是删除空格。
回答by Ian Horwill
select substring(ColumnName, patindex('%[^0]%',ColumnName), 10)
回答by MTZ
select replace(ltrim(replace(ColumnName,'0',' ')),' ','0')
回答by Nat
select substring(substring('B10000N0Z', patindex('%[0]%','B10000N0Z'), 20),
patindex('%[^0]%',substring('B10000N0Z', patindex('%[0]%','B10000N0Z'),
20)), 20)
returns N0Z, that is, will get rid of leading zeroes and anything that comes before them.
Returns N0Z，也就是说，将摆脱前导零和它们之前的任何东西。
回答by ekc
I had the same need and used this:
我有同样的需求并使用了这个：
select
case
when left(column,1) = '0'
then right(column, (len(column)-1))
else column
end
回答by Kathryn Wilson
If you want the query to return a 0 instead of a string of zeroes or any other value for that matter you can turn this into a case statement like this:
如果您希望查询返回 0 而不是一串零或任何其他值，您可以将其转换为这样的 case 语句：
select CASE
WHEN ColumnName = substring(ColumnName, patindex('%[^0]%',ColumnName), 10)
THEN '0'
ELSE substring(ColumnName, patindex('%[^0]%',ColumnName), 10)
END
回答by Stelian
You can use this:
你可以使用这个：
SELECT REPLACE(LTRIM(REPLACE('000010A', '0', ' ')),' ', '0')
回答by Shailendra Mishra
You can try this - it takes special care to onlyremove leading zeroes if needed:
你可以试试这个——如果需要，只删除前导零需要特别注意：
DECLARE @LeadingZeros VARCHAR(10) ='-000987000'
SET @LeadingZeros =
CASE WHEN PATINDEX('%-0', @LeadingZeros) = 1 THEN
@LeadingZeros
ELSE
CAST(CAST(@LeadingZeros AS INT) AS VARCHAR(10))
END
SELECT @LeadingZeros
Or you can simply call
或者你可以简单地打电话
CAST(CAST(@LeadingZeros AS INT) AS VARCHAR(10))
回答by Vikas
Here is the SQL scalar value function that removes leading zeros from string:
这是从字符串中删除前导零的 SQL 标量值函数：
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Vikas Patel
-- Create date: 01/31/2019
-- Description: Remove leading zeros from string
-- =============================================
CREATE FUNCTION dbo.funRemoveLeadingZeros
(
-- Add the parameters for the function here
@Input varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result varchar(max)
-- Add the T-SQL statements to compute the return value here
SET @Result = @Input
WHILE LEFT(@Result, 1) = '0'
BEGIN
SET @Result = SUBSTRING(@Result, 2, LEN(@Result) - 1)
END
-- Return the result of the function
RETURN @Result
END
GO
回答by Frank Chen
In case you want to remove the leading zeros from a string with a unknown size.
如果您想从未知大小的字符串中删除前导零。
You may consider using the STUFF command.
您可以考虑使用 STUFF 命令。
Here is an example of how it would work.
这是它如何工作的一个例子。
SELECT ISNULL(STUFF(ColumnName
,1
,patindex('%[^0]%',ColumnName)-1
,'')
,REPLACE(ColumnName,'0','')
)
See in fiddler various scenarios it will cover
在 fiddler 中查看它将涵盖的各种场景
https://dbfiddle.uk/?rdbms=sqlserver_2012&fiddle=14c2dca84aa28f2a7a1fac59c9412d48
https://dbfiddle.uk/?rdbms=sqlserver_2012&fiddle=14c2dca84aa28f2a7a1fac59c9412d48
回答by Krin
To remove leading 0, You can multiply number column with 1 Eg: Select (ColumnName * 1)
要删除前导 0，您可以将数字列乘以 1 例如：Select (ColumnName * 1)
