Find and Count Leading Zeros in Column

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
Queennie LAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
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
0
Queennie LAuthor Commented:
@lcohan:

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

Thank you for your help.
0
slightwv (䄆 Netminder) Commented:
Try this:
select checknumber, patindex('%[^0]%', checknumber)-1 from your_table;

Open in new window

1
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

slightwv (䄆 Netminder) 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

1
pcelbaCommented:
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

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
_agx_Commented:
@pcelba - Nice, much simpler than mine.
0
slightwv (䄆 Netminder) Commented:
>>@pcelba - Nice, much simpler than mine.

And oddly similar to my new one...  I just found the inline view cleaner
0
pcelbaCommented:
Yes, very similar...
0
Queennie LAuthor 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.
0
slightwv (䄆 Netminder) 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

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

I really appreciate it.

Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.