更好的修剪SQL Server中前导零的技术？

我一直在使用这一段时间:

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

但是最近,我发现所有"0"字符的列都有问题,例如'00000000',因为它永远找不到匹配的非"0"字符.

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

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

如果存在嵌入空格,则会出现问题,因为当空格变回"0"时,它们将变为"0".

我试图避免使用标量UDF.我在SQL Server 2005中发现了很多UDF的性能问题.

1> Arvo..：

---

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

---

没关系,我意识到'.' 不在子字符串中,因为它只用于查找模式 - 它比我想象的更聪明.

---

将其封装在函数中会导致查询速度变慢.我不太清楚为什么,但我认为它与类型转换有关.使用SUBSTRING内联速度要快得多.

2> Quassnoi..：

---

你为什么不把价值投入INTEGER然后又回到VARCHAR？

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

--------
       0

---

这是一个字符串列,所以我猜他们不时会期待非数字数据.类似于MRN编号的东西,其中数据仅为_mostly_ numeric.

---

`当1然后CAST（CAST（str_col AS BIGINT）AS VARCHAR（255））ELSE str_col END时选择大小写ISNUMERIC（str_col）

3> MikeTeeVee..：

---

如果你有全零(或甚至一个零),这里的其他答案不予考虑.
有些人总是将空字符串默认为零,这在它应该保持空白时是错误的.
重新阅读原始问题.这回答了发问者想要的内容.

解决方案#1:

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

结果:

摘要:

您可以使用我上面的内容来一次性删除前导零.
如果您打算重复使用它,那么将它放在内联表值函数(ITVF)中.
您对UDF性能问题的担忧是可以理解的.
但是,此问题仅适用于All-Scalar-Functions和Multi-Statement-Table-Functions.
使用ITVF非常好.

我的第三方数据库存在同样的问题.
有了Alpha-Numeric字段,很多人都没有领先的空间就进入了人类!
如果不清理丢失的前导零,这使得连接成为不可能.

结论:

您可能需要考虑在执行连接时使用前导零填充修剪值,而不是删除前导零.
更好的是,通过添加前导零,然后重建索引来清理表中的数据.
我认为这将更快,更简单.

SELECT RIGHT('0000000000' + LTRIM(RTRIM(NULLIF(' 0A10  ', ''))), 10)--0000000A10
SELECT RIGHT('0000000000' + LTRIM(RTRIM(NULLIF('', ''))), 10)--NULL --When Blank.

---

@DiegoQueiroz如果答案是错误的,那么请低估并解释为什么它不起作用.如果答案有效,但对您来说太全面,那么请不要低估我或本网站上的其他成员.感谢您的评论.听到这是很好的反馈 - 我真诚地说出来.

4> Joel Coehoor..：

---

而不是空格将0替换为"稀有"空格字符,该字符通常不应位于列的文本中.对于像这样的列,换行可能已经足够了.然后你可以正常LTrim并再次用0替换特殊字符.