从 SQL 字段中修剪前导零的算法? [英] Algorithms to trim leading zeroes from a SQL field?

问题描述

我刚刚遇到了一个有趣的问题，即尝试从 SQL 中的非数字字段中修剪前导零.(因为它可以包含字符，所以不能只是转换为数字然后返回.)

I just came across the interesting problem of trying to trim the leading zeroes from a non-numeric field in SQL. (Since it can contain characters, it can't just be converted to a number and then back.)

这是我们最终使用的:

SELECT REPLACE(LTRIM(REPLACE(fieldWithLeadingZeroes,'0',' ')),' ','0')

它用空格替换零，左修剪它，然后把零放回去.我认为这是一个非常聪明和有趣的方法，虽然如果你以前从未遇到过它，那么可读性不强.

It replaces the zeroes with spaces, left trims it, and then puts the zeroes back in. I thought this was a very clever and interesting way to do it, although not so readable if you've never come across it before.

有没有更清晰的方法来做到这一点?有没有更有效的方法来做到这一点?或者有什么其他方法可以做到这一点?我对这个问题很感兴趣，很想看看有什么方法可以解决它.

Are there any clearer ways to do this? Any more efficient ways to do this? Or any other ways to do this period? I was intrigued by this problem and would be interested to see any methods of getting around it.

推荐答案

查找第一个非零字符，以子串结尾(最大类型可以放 20 亿)

Find 1st non-zero character, substring to end (can put 2 billion for max types)

LTRIM 是可选的，真的

LTRIM is optional, really

DECLARE @MyVar varchar(8000)

SET @MyVar = '000foobar'
SELECT LTRIM(SUBSTRING(@MyVar, PATINDEX ('%[^0]%', @MyVar), 8000))
SET @MyVar = '000000  oh my lord'
SELECT LTRIM(SUBSTRING(@MyVar, PATINDEX ('%[^0]%', @MyVar), 8000))

这篇关于从 SQL 字段中修剪前导零的算法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！