473,322 Members | 1,431 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,322 software developers and data experts.

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.SYSDUMMY1
UNION SELECT '.4.' , '.004.'
FROM SYSIBM.SYSDUMMY1
UNION SELECT '.22.' , '.022.'
FROM SYSIBM.SYSDUMMY1) 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 11726
On May 5, 5:27 pm, "lenygold via DBMonster.com" <u41482@uwewrote:
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.SYSDUMMY1
UNION SELECT '.4.' , '.004.'
FROM SYSIBM.SYSDUMMY1
UNION SELECT '.22.' , '.022.'
FROM SYSIBM.SYSDUMMY1) 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,index+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,index
+1,coalesce(nullif(locate('+', substr(x.c,index+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@uwewrote:
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@uwewrote:
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(sourcestring,
(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
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...
8
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:...
1
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
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...
24
by: FAQ server | last post by:
----------------------------------------------------------------------- FAQ Topic - How can I see in javascript if a web browser accepts cookies?...
2
by: tomlebold | last post by:
How do you remove leading zeroes in a column? Update 0000123456 to 123456
8
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
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...
9
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...
2
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
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
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
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
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.