Solved

mysql check number length

Posted on 2012-04-09
4
321 Views
Last Modified: 2012-05-01
hi,
is it possible to check a numbers length in mysql because the zeros are missing off the beginning of some numbers.. is there away to check if its 9 in length and add a zero in the beginning if it is.

thanks in advance
0
Comment
Question by:MK15
  • 2
4 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 37825766
'Numbers' do not have leading zeros (or trailing zeros to the right of a decimal point) because they contribute nothing to the value.  If you want leading zeros, you must convert it to a string and check it there.
0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 500 total points
ID: 37825980
try:

right('00000000'+num,9)
0
 
LVL 51

Accepted Solution

by:
HainKurt earned 500 total points
ID: 37825985
or use

lpad(num, 9, '0')

you may need to cast it

lpad(cast(num as char), 9, '0')
0
 

Author Closing Comment

by:MK15
ID: 37914223
thanks guys
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question