计算一个整数出现在列中的次数

I'm attempting to find a way to count the end of an integer and display the trailing value. For example, I have the following table:

我试图找到一种方法来计算一个整数的结束值并显示它的后置值。例如，我有以下表格:

CREDIT
======
1051000
10000
2066
16000

I'd like to be able to count the amount of times '0' appears and end up with a result like the following

我希望能够计算出出现“0”的次数，并得到如下结果

CREDIT              CntOccuranceVals
======              ======
1051000             3
10000               4
2066                0
16000               3

Now I have tried using a query that will find all of the '0', but the problem I'm facing is for the first row it returns 4 instead of three as it is searching the whole row and not the trailing. This is what I've tried using to find the occurrence count

现在我尝试使用一个查询来查找所有的'0'，但是我面临的问题是对于第一行，它返回4而不是3，因为它搜索的是整行而不是尾行。这就是我用来查找事件计数的方法

DECLARE @LongSequence INT(MAX)
DECLARE @FindSubString INT(MAX)
SET @LongSequence = CREDIT
SET @FindSubString = '0'
SELECT (LEN(@LongSequence) - LEN(REPLACE(@LongSequence, @FindSubString, '')))     CntReplacedVals,
(LEN(@LongSequence) - LEN(REPLACE(@LongSequence, @FindSubString, '')))/LEN(@FindSubString) CntOccuranceVals

Is there a way I can find only the trailing 0's and not the ones in the middle of the value?

有没有一种方法，我只能找到后面的0，而不是在值中间的那些?

EDIT: typo

编辑:输入错误

1 个解决方案

#1

You could use Reverse and Patindex like this:

你可以像这样使用反向和Patindex:

Declare @a Table (i int)
insert into @a
Select 123000
union
Select 123001
union
Select 100000
union
Select 123001

Select i, PatIndex('%[1-9]%',Reverse(Cast(i as Varchar(50)))) - 1  as  CntOccuranceVals
from @a