473,320 Members | 2,000 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Leading Zeros

Am queying table using this statement
Expand|Select|Wrap|Line Numbers
  1. select max(SUBSTRING (policyno,11,6)) as policyno from table where substring(policyno,5,3) = 070
The statement works but it does not return the maximum value where policyno has leading zeros. Example of policyno is TM1/070/1/009899/2008

Want to remove 009899 substring and get the maximum value from the table then increment by one. It works only when the number has no leading zeros like 9899

Any help will be appreciated
Jan 24 '08 #1
1 2221
mwasif
802 Expert 512MB
Hi rawlund,

Welcome to TSDN!!!

You need to convert the value to INTEGER and then use it for calculation e.g.
Expand|Select|Wrap|Line Numbers
  1. SELECT max(CAST(SUBSTRING(policyno,11,6) AS SIGNED )) as policyno 
  2. FROM table WHERE SUBSTRING(policyno,5,3) = 070
Jan 24 '08 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: david | last post by:
Hi, I have an application as follows: MySQL database Back-Eend linked to MS Access Front-End and ASP Web Application. I require users to enter Serial Numbers such as: 0105123567 (10...
5
by: samik_tanik | last post by:
I need to export a datagrid to Excel. I could did this. But, also need to keep the leading zeros in the data. How can I acheive this? Any help would be appreciated. -- Thanking you in...
1
by: mmmgood1 | last post by:
Help, I'm linking an excel spreadsheet in access and I have datafields with leading zeros (01021). When the file is linked in access, I get a #num in the field with the leading zeros. The zeros...
6
by: Clint Stowers | last post by:
Using A2k Exporting a query to a CSV file. The problem is any text fields (i.e. 000345) lose any leading zeros. Exporting to an excel file this problem does not exist. Tried to create a...
5
by: OneDay | last post by:
I've got a field that has some old data with text in it, but all forward data will be a 3 digit number. But many of the numbers are still only 2 digits. I would like to force the leading zero in...
6
by: Rich Raffenetti | last post by:
How can one format an integer into a hex string with leading zeros? Suppose an integer is 512 which in Hex is 200. I wish to print the 4-byte integer as 0200 or even 0x0200. The HEX function...
2
by: chris | last post by:
Hi, I have a simple ms access application that allows you to scan barcodes in to a form which stores them in the database. The barcodes are 6 digits in length e.g. 555666 but my handheld...
5
by: GarryJones | last post by:
I have code numbers in 2 fields from a table which correspond to month and date. (Month, Code number) Field name = ml_mna 1 2 3 etc up to 12 (Data is entered without a leading zero)
6
by: JimmyKoolPantz | last post by:
Task: Customer wants a script of the data that was processed in a "CSV" file. Problem: Zip-Code leading zeros are dropped Basically we have a client that has requested a custom script for...
0
by: Monty | last post by:
Hi All, I am having a problem with leading zeros being stripped from fields in a CSV file when I bring them in using Jet/OleDB. In VB.Net/VS 2008, I am accessing a CSV file like so: sSQL =...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.