We help IT Professionals succeed at work.

Find and Count Leading Zeros in Column

89 Views
Last Modified: 2018-09-13
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

lcohanDatabase Analyst
CERTIFIED EXPERT

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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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

CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2015

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

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

And oddly similar to my new one...  I just found the inline view cleaner
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

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

I really appreciate it.

Thank you.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.