473,903 Members | 4,817 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to add leading zeroes

I have the following column:

1.4.1
1.10.1
1.10.1.2.2
1.22.99.1
2
2.8.11
2.7.7

I am trying to add leading zeros to every part before dots, to make them 3
digit numbers:
001.004.001
001.010.001
001.010.001
001.022.099.001
002
002.008.011
002.007.007

My first try did't work:
SELECT
REPLACE ('1.4.22.34 ', S, B)
FROM (SELECT '.34 ' , '.034 '
FROM SYSIBM.SYSDUMMY 1
UNION SELECT '.4.' , '.004.'
FROM SYSIBM.SYSDUMMY 1
UNION SELECT '.22.' , '.022.'
FROM SYSIBM.SYSDUMMY 1) T(S,B)

output:

1.4.022.34
1.4.22.034
1.004.22.34

Any idea how to produce requested result?
Thank's in advance
Lenny G.

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200805/1

Jun 27 '08 #1
4 11814
On May 5, 5:27 pm, "lenygold via DBMonster.com" <u41482@uwewrot e:
I have the following column:

1.4.1
1.10.1
1.10.1.2.2
1.22.99.1
2
2.8.11
2.7.7

I am trying to add leading zeros to every part before dots, to make them 3
digit numbers:
001.004.001
001.010.001
001.010.001
001.022.099.001
002
002.008.011
002.007.007

My first try did't work:
SELECT
REPLACE ('1.4.22.34 ', S, B)
FROM (SELECT '.34 ' , '.034 '
FROM SYSIBM.SYSDUMMY 1
UNION SELECT '.4.' , '.004.'
FROM SYSIBM.SYSDUMMY 1
UNION SELECT '.22.' , '.022.'
FROM SYSIBM.SYSDUMMY 1) T(S,B)

output:

1.4.022.34
1.4.22.034
1.004.22.34

Any idea how to produce requested result?
Thank's in advance
Lenny G.
You can do it with a case expression:

case when length(rtrim(x) ) = 1 then '00' || x ...

but you are (IMO) going in the wrong direction. Let's look at a
simplified example:

[lelle@53dbd181 ~]$ db2 "select * from lateral(values( '1+21+131'))
x(c), table(elements( x.c))"

C ORDINAL INDEX
-------- ----------- -----------
1+21+131 0 0
1+21+131 1 2
1+21+131 2 5

3 record(s) selected.

index+1 will give us the startpos for each new number:

[lelle@53dbd181 ~]$ db2 "select substr(x.c,inde x+1) from
lateral(values( '1+21+131')) x(c), table(elements( x.c))"
1
--------
1+21+131
21+131
131

The stop pos for each number will be the next '+' and in case there
are no '+' left, we can use the length of the remaining string:

[lelle@53dbd181 ~]$ db2 "select substr(x.c,inde x
+1,coalesce(nul lif(locate('+', substr(x.c,inde x+1)),
0)-1,length(substr (x.c,index+1))) ) from lateral(values( '1+21+131'))
x(c), table(elements( x.c))"

1
--------
1
21
131

IMO, this kind of problem is better solved in the application layer

/Lennart
Jun 27 '08 #2
On May 5, 5:27 pm, "lenygold via DBMonster.com" <u41482@uwewrot e:
Any idea how to produce requested result?
I would have used perl:

$ cat /tmp/foo.pl
use strict;
use warnings;

while(my $line = <DATA>){
print join(q{.}, map { sprintf("%03d", $_); } split(/\./,
$line));
}

__DATA__
1.4.1
1.10.1
1.10.1.2.2
1.22.99.1
2
2.8.11
2.7.7

$ perl -wl /tmp/foo.pl
001.004.001
001.010.001
001.010.001.002 .002
001.022.099.001
002
002.008.011
002.007.007

--
Serman D.
Jun 27 '08 #3
On May 6, 1:48 pm, "lenygold via DBMonster.com" <u41482@uwewrot e:
Thank' s everybody for help.
The reason why i need this conversion is to sort the following rows:
I didn't realize that it was sorting that you where after. The
following post by Vadim is an alternative way of attacking traversal
of a tree, and therefor might be of interest to you.

http://groups.google.com/gr*********...ing.google.com

A total ordering is defined as:

6. For any nodes A and B we write A B whenever
i. B is Ancestor of A or
ii. there exists node B' which is an ancestor of B,
and A' which is an ancestor of A,
and both A' and B' having the same parent,
and A' B'

/Lennart

Jun 27 '08 #4
>Although I agree with Lennart, I want to try in SQL simply for my entertainment. <<

I agree, but it is fun. Life is easier if the input string always ends
with a period, and I think that is required in one of the ISO or US
Government Standards for outlines.

1) Can you nest function calls 100 levels deep? So we just nest
REPLACE() calls that deep:

SELECT REPLACE ( ..
REPLACE (sourcestring, '1.', '001.'),
..
'99.', '099.')
FROM Foobar;

Since the functions will be on the stack, this ought to run pretty
fast. But only a LISP programmer would love it.

2) Go back to procedural programming and write a loop?

CREATE TABLE LeadingZeros
(instring VARCHAR(3) NOT NULL PRIMARY KEY,
outstring CHAR(4) NOT NULL
CHECK (outstring SIMILAR TO '[:DIGITS:][:DIGITS:][:DIGITS:]\.');

INSERT INTO LeadingZeros (convert_nbr, instring, outstring)
VALUES (1, '1.', '001.'), (2, '2.', '002.'), .., (99, '99.', '099.');

CREATE PROCEDURE PaddingZeros ()
LANGUAGE SQL
READS SQL DATA
BEGIN DECLARE i INTEGER;
SET i = 1;
WHILE i < 100
DO UPDATE Foobar
SET sourcestring
= REPLACE(sources tring,
(SELECT instring
FROM LeadingZeros
WHERE i = convert_nbr),
(SELECT outstring
FROM LeadingZeros
WHERE i = convert_nbr));
SET i = i+1;
END WHILE;
END;

This can be done with a recursive CTE, but that might be even worse.
Jun 27 '08 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
13792
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 digits), the first 4 being the month and year (mmyy)
8
2750
by: TheTeapot | last post by:
Hi all, Here's a puzzle: // Say I have an array of numbers set up like so: $arr = array(15,16,17,100,121,1000); // How can I create a function so that I can use it like so: addleadingzeros_arr($arr); // and have the output look like:
1
17363
by: Jean-michel | last post by:
I need to convert a decimal field to char() but also trim the leading zeroes. Any idea? I could not find any function to do that.
1
1604
by: Mike P | last post by:
I'm trying to write the contents of a csv file to a table, but I am having problems with fields with leading zeroes. Whenever I save as csv I lose the leading zeroes. Does anybody know how to prevent this? *** Sent via Developersdex http://www.developersdex.com ***
24
2499
by: FAQ server | last post by:
----------------------------------------------------------------------- FAQ Topic - How can I see in javascript if a web browser accepts cookies? ----------------------------------------------------------------------- Writing a cookie, reading it back and checking if it's the same. http://www.w3schools.com/js/js_cookies.asp Additional Notes:
2
10490
by: tomlebold | last post by:
How do you remove leading zeroes in a column? Update 0000123456 to 123456
8
46966
by: stainless | last post by:
Is it possible, using the ToString function, to take an integer and conver to a string of fiexd width with the leading spaces padded with zeroes. e.g. integer 123 converted to a string of length 9 with 6 leading zeroes i.e. 000000123 integer 98765432 converted to a string of length 9 with 1 leading zero i.e. 098765432
9
3026
by: jbaranski | last post by:
Access 2003 on XP pro machine... i'm running a crosstab query and an export to a 3rd party company showing different benefit plans for employees; specifically dental, vision and medical pulling the effective dates (date which the plan when into effect). generally the plans all have the same effective date. however in the case they don't 2 lines are returned for the same employee; the first containing, for example, their medical and vision...
9
4585
by: marcelo27 | last post by:
I need to add leading zeroes to an input box. For example, the user enters "2" , I need to convert to a three digit number eg."002". If the user enters "23", I need to convert to "023" If the user enters a three digit number leave it alone. Thanks for your help.
2
2831
by: DanCole42 | last post by:
Newbie question, here. I have a database that frequently imports ZIP code data that frequently needs cleaning: 12365 6487 64684-3543 3213-6546
0
9997
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9845
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10981
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10499
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9675
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7205
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
6085
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4307
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3323
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.