Hi,

  Anyone who can help me to get the first non-zero number of a field? I have a column in my table (varchar) which contains a series of numbers. I need to get the first non-zero number in the column. Charindex and patindex can get the position of the zero, but I can't get the positition of non-zero using these functions. I hope you have a better idea to do this.

Thanks a lot in advance!

Hi there.

Just thinking out loud, what if you convert the varchar to number, convert it again to string, count the quantity of characters there, and substract the quantity of the original field from this number?

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

I think something like this might work. (Its late so it may not be accurate but it furthers Gonzalos thought)

SELECT
(LEN(<varcharfield>) - (Len(Convert(int, <varcharfield>))))
From <table>

If the varchar length is 10 and is padded with 2 zeros the select statment above would return the result: 2

varchar Field length == 10
int field length == 8
difference == 2

hth

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========

Thanks so much guys! It works!