CODEWITHSUNDEEP
sqlsql-servercount
xalx
xalx

Reputation: 47

Count how many times an integer is appears in a column

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

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

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?

EDIT: typo

Upvotes: 3

Views: 229

Answers (1)

bummi
bummi

Reputation: 27377

You could use Reverse and Patindex like this:

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

Upvotes: 7

Related Questions