asked on 9/13/2018

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 Server SQL

Last Comment

Queennie L

9/13/2018

lcohan

9/13/2018

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

9/13/2018

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)

9/13/2018

Try this:

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

Open in new window

slightwv (䄆 Netminder)

9/13/2018

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

9/13/2018

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

_agx_

9/13/2018

@pcelba - Nice, much simpler than mine.

slightwv (䄆 Netminder)

9/13/2018

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

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

Pavel Celba

9/13/2018

Yes, very similar...

Queennie L

9/13/2018

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

slightwv (䄆 Netminder)

9/13/2018

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Queennie L

9/13/2018

ASKER

Thank you Experts for your sharing your expertise and intelligence.

I really appreciate it.

Thank you.