Re: How to add leading zeroes

>> 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.