在 SQLServer 中修剪前导零的更好技术？

我使用 这个已经有一段时间了:

SUBSTRING(str_col, PATINDEX('%[^0]%', str_col), LEN(str_col))

但是最近，我发现所有“0”字符的列都有问题，比如“00000000”，因为它从未找到匹配的非“0”字符。

我见过的另一种技术是使用 TRIM:

REPLACE(LTRIM(REPLACE(str_col, '0', ' ')), ' ', '0')

如果有嵌入的空格，这就有问题了，因为当这些空格变回“0”时，它们就会变成“0”。

我试图避免标量 UDF。我在 SQLServer2005中发现了很多 UDF 的性能问题。

374206 次浏览
小开

为什么不将值转换为 INTEGER，然后返回到 VARCHAR？

SELECT  CAST(CAST('000000000' AS INTEGER) AS VARCHAR)


--------
0
小开

用一个通常不应该出现在专栏文本中的“罕见”空白字符代替空格。换行符对于这样的列来说可能已经足够好了。然后您可以正常的 LTrim 并用0代替特殊字符。

小开
最佳答案 
SUBSTRING(str_col, PATINDEX('%[^0]%', str_col+'.'), LEN(str_col))
小开

如果字符串完全由零组成，则返回’0’:

CASE WHEN SUBSTRING(str_col, PATINDEX('%[^0]%', str_col+'.'), LEN(str_col)) = '' THEN '0' ELSE SUBSTRING(str_col, PATINDEX('%[^0]%', str_col+'.'), LEN(str_col)) END AS str_col
小开

这是一个很好的功能... 。

DROP FUNCTION [dbo].[FN_StripLeading]
GO
CREATE FUNCTION [dbo].[FN_StripLeading] (@string VarChar(128), @stripChar VarChar(1))
RETURNS VarChar(128)
AS
BEGIN
-- http://stackoverflow.com/questions/662383/better-techniques-for-trimming-leading-zeros-in-sql-server
DECLARE @retVal VarChar(128),
@pattern varChar(10)
SELECT @pattern = '%[^'+@stripChar+']%'
SELECT @retVal = CASE WHEN SUBSTRING(@string, PATINDEX(@pattern, @string+'.'), LEN(@string)) = '' THEN @stripChar ELSE SUBSTRING(@string, PATINDEX(@pattern, @string+'.'), LEN(@string)) END
RETURN (@retVal)
END
GO
GRANT EXECUTE ON [dbo].[FN_StripLeading] TO PUBLIC
小开
如果你的答案是全零(或者只有一个零) ，那么这里的其他答案就不需要考虑了。 < br/> 有些默认的空字符串为零，如果它应该保持为空，那么它就是错误的。 < br/> 重读原来的问题。这回答了提问者想要的答案

解决方案一:

--This example uses both Leading and Trailing zero's.
--Avoid losing those Trailing zero's and converting embedded spaces into more zeros.
--I added a non-whitespace character ("_") to retain trailing zero's after calling Replace().
--Simply remove the RTrim() function call if you want to preserve trailing spaces.
--If you treat zero's and empty-strings as the same thing for your application,
--  then you may skip the Case-Statement entirely and just use CN.CleanNumber .
DECLARE @WackadooNumber VarChar(50) = ' 0 0123ABC D0 '--'000'--
SELECT WN.WackadooNumber, CN.CleanNumber,
(CASE WHEN WN.WackadooNumber LIKE '%0%' AND CN.CleanNumber = '' THEN '0' ELSE CN.CleanNumber END)[AllowZero]
FROM (SELECT @WackadooNumber[WackadooNumber]) AS WN
OUTER APPLY (SELECT RTRIM(RIGHT(WN.WackadooNumber, LEN(LTRIM(REPLACE(WN.WackadooNumber + '_', '0', ' '))) - 1))[CleanNumber]) AS CN
--Result: "123ABC D0"

解决方案 # 2(带有示例数据) :

SELECT O.Type, O.Value, Parsed.Value[WrongValue],
(CASE WHEN CHARINDEX('0', T.Value)  > 0--If there's at least one zero.
AND LEN(Parsed.Value) = 0--And the trimmed length is zero.
THEN '0' ELSE Parsed.Value END)[FinalValue],
(CASE WHEN CHARINDEX('0', T.Value)  > 0--If there's at least one zero.
AND LEN(Parsed.TrimmedValue) = 0--And the trimmed length is zero.
THEN '0' ELSE LTRIM(RTRIM(Parsed.TrimmedValue)) END)[FinalTrimmedValue]
FROM
(
VALUES ('Null', NULL), ('EmptyString', ''),
('Zero', '0'), ('Zero', '0000'), ('Zero', '000.000'),
('Spaces', '    0   A B C '), ('Number', '000123'),
('AlphaNum', '000ABC123'), ('NoZero', 'NoZerosHere')
) AS O(Type, Value)--O is for Original.
CROSS APPLY
( --This Step is Optional.  Use if you also want to remove leading spaces.
SELECT LTRIM(RTRIM(O.Value))[Value]
) AS T--T is for Trimmed.
CROSS APPLY
( --From @CadeRoux's Post.
SELECT SUBSTRING(O.Value, PATINDEX('%[^0]%', O.Value + '.'), LEN(O.Value))[Value],
SUBSTRING(T.Value, PATINDEX('%[^0]%', T.Value + '.'), LEN(T.Value))[TrimmedValue]
) AS Parsed

结果:

MikeTeeVee_SQL_Server_Remove_Leading_Zeros

摘要:

你可以使用我上面的方法来一次性删除前导零 如果您计划大量重用它，那么将它放在 Inline-Table-Value-function (ITVF)中 您对 UDF 性能问题的担忧是可以理解的 然而，这个问题只适用于全标量函数和多语句表函数。 < br/> 使用 ITVF 是非常好的。 < br/>
我们的第三方数据库也有同样的问题 使用 Alpha-Numeric 字段时，很多输入都没有前导空格，该死的人类! < br/> 这使得不清除缺少的前导零就不可能连接。 < br/>

结论:

与删除前导零相反，您可能需要考虑在进行连接时仅用前导零填充修剪后的值。< br/> 更好的方法是，在表中添加前导零，然后重新生成索引，从而清理数据。 < br/> 我认为这会更快更简单 
SELECT RIGHT('0000000000' + LTRIM(RTRIM(NULLIF(' 0A10  ', ''))), 10)--0000000A10
SELECT RIGHT('0000000000' + LTRIM(RTRIM(NULLIF('', ''))), 10)--NULL --When Blank.
小开

如果 string 是一个数字，cast (value as int)将始终有效

小开

试试这个:

replace(ltrim(replace(@str, '0', ' ')), ' ', '0')
小开

我的版本是根据 Arvo 的工作改编的，为了确保另外两个案例的安全，还加了一点。

1)如果我们有所有的0，我们应该返回数字0。

2)如果我们有一个空白字符，我们仍然应该返回一个空白字符。

CASE
WHEN PATINDEX('%[^0]%', str_col + '.') > LEN(str_col) THEN RIGHT(str_col, 1)
ELSE SUBSTRING(str_col, PATINDEX('%[^0]%', str_col + '.'), LEN(str_col))
END
小开 
replace(ltrim(replace(Fieldname.TableName, '0', '')), '', '0')

托马斯 · G 的建议满足了我们的需要。

在我们的例子中，字段已经是字符串，只有前导零需要修剪。大部分都是数字，但有时也有字母，所以以前的 INT 转换会崩溃。

小开 
SELECT CAST(CAST('000000000' AS INTEGER) AS VARCHAR)

这限制了可以转换为 INT 的字符串的长度

小开
如果您不想转换为 int，我更喜欢下面的逻辑，因为它可以处理 null IFNULL (field，LTRIM (field,’0’))

小开

如果您正在使用雪花 SQL，可以使用以下命令:

ltrim(str_col,'0')

该函数从左侧删除指定字符集的所有实例。

因此,’00000008A’上的 lrim (str _ coll,’0’)将返回’8A’

而“ $125.00”上的 rrim (str _ coll,’0.’)将返回“ $125”

小开 
  SUBSTRING(str_col, IIF(LEN(str_col) > 0, PATINDEX('%[^0]%', LEFT(str_col, LEN(str_col) - 1) + '.'), 0), LEN(str_col))

即使’0’,’00’等工作良好。

小开

在 MySQL 中你可以这样做..。

Trim(Leading '0' from your_column)
小开

为了将数字转换为 Varchar到 Int，您还可以使用简单的

(column + 0)
小开

这个可能会有帮助

SELECT ABS(column_name) FROM [db].[schema].[table]
小开

非常简单的方法，当你只处理数值时:

SELECT
TRY_CONVERT(INT, '000053830')

提交答案