Avatar of Queennie L
Queennie L
 asked on

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
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Queennie L

8/22/2022 - Mon
lcohan

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
Queennie L

ASKER
@lcohan:

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

Thank you for your help.
slightwv (䄆 Netminder)

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

Open in new window

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
slightwv (䄆 Netminder)

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

ASKER CERTIFIED SOLUTION
Pavel Celba

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
_agx_

@pcelba - Nice, much simpler than mine.
slightwv (䄆 Netminder)

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

And oddly similar to my new one...  I just found the inline view cleaner
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Pavel Celba

Yes, very similar...
Queennie L

ASKER
@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.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Queennie L

ASKER
Thank you Experts for your sharing your expertise and intelligence.

I really appreciate it.

Thank you.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23