Find and Count Leading Zeros in Column

Queennie L
Queennie L used Ask the Experts™
on
Hello Experts,

How to search for values in a column that has leading zeros? Count them?

I know this is just simple to experts but I searched but cannot find any answers.

See attach file.

Thank you.
FindandCountLeadingZeros_inValue.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
Not quite clear where (SQL Server database table column? what data type?) do you need to count them and until you provide more details here's how you can do that in excel:

https://www.extendoffice.com/documents/excel/4963-excel-count-leading-zeros.html

Author

Commented:
@lcohan:

This is Microsoft SQL  Server not Excel. I apologized I was not clear in my question.

Thank you for your help.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Try this:
select checknumber, patindex('%[^0]%', checknumber)-1 from your_table;

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
If they can be all 0:
select checknumber, case when mycol > 0 then mycol else len(checknumber)end zero_count from (
select checknumber, patindex('%[^0]%', checknumber)-1 as mycol from your_table)
newtable

Open in new window

This should provide more accurate results:
select checknumber, CASE patindex('%[^0]%', checknumber) WHEN 0 THEN LEN(checknumber) ELSE patindex('%[^0]%', checknumber)-1 END from your_table;

Open in new window

Most Valuable Expert 2015

Commented:
@pcelba - Nice, much simpler than mine.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>@pcelba - Nice, much simpler than mine.

And oddly similar to my new one...  I just found the inline view cleaner
Yes, very similar...

Author

Commented:
@slightwv (䄆 Netminder):

The query is counting the length of the variables.

@pcelba:

WOW! Your query works. I will do more test and I will let you know.

Thank you again.
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>The query is counting the length of the variables.

I had a small bug if it had no zeros.  Only the first row returned the length.  The rest worked.

I had ">0" and needed ">=":
select checknumber, case when mycol >= 0 then mycol else len(checknumber)end zero_count from (
select checknumber, patindex('%[^0]%', checknumber)-1 as mycol from your_table)
newtable

Open in new window

Author

Commented:
Thank you Experts for your sharing your expertise and intelligence.

I really appreciate it.

Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial