Convert number to letters
Description
Convert number to letters
//this is new approach to convert number to letters depending on
//the length of number so it take the number as a string then convert it
//without the need of calculation
//The code also use the old system numbers(million,milliard,billion,billiard....)
//but you can also use the new system(million,billion,trillion,quadrillion..)
//this is new approach to convert number to letters depending on
//the length of number so it take the number as a string then convert it
//without the need of calculation
//The code also use the old system numbers(million,milliard,billion,billiard....)
//but you can also use the new system(million,billion,trillion,quadrillion..)
Source / Exemple :
/*-----------------------------------------------------------------*/
DROP FUNCTION IF EXISTS tab_1_19;
DELIMITER $$
CREATE FUNCTION tab_1_19(xv int) RETURNS varchar(255)
BEGIN
DECLARE xch Varchar(20);
CASE xv
WHEN 0 THEN SET xch := ' ';
WHEN 1 THEN SET xch := ' un';
WHEN 2 THEN SET xch := ' deux';
WHEN 3 THEN SET xch := ' trois';
WHEN 4 THEN SET xch := ' quatre';
WHEN 5 THEN SET xch := ' cinq';
WHEN 6 THEN SET xch := ' six';
WHEN 7 THEN SET xch := ' sept';
WHEN 8 THEN SET xch := ' huit';
WHEN 9 THEN SET xch := ' neuf';
WHEN 10 THEN SET xch := ' dix' ;
WHEN 11 THEN SET xch := ' onze';
WHEN 12 THEN SET xch := ' douze';
WHEN 13 THEN SET xch := ' treize';
WHEN 14 THEN SET xch := ' quatorze';
WHEN 15 THEN SET xch := ' quinze';
WHEN 16 THEN SET xch := ' seize';
WHEN 17 THEN SET xch := ' dix-sept';
WHEN 18 THEN SET xch := ' dix-huit';
WHEN 19 THEN SET xch := ' dix-neuf';
END CASE;
RETURN xch;
END
$$
DELIMITER ;
DROP FUNCTION IF EXISTS tab_20_90;
DELIMITER $$
CREATE FUNCTION tab_20_90(xv int) RETURNS varchar(255)
BEGIN
DECLARE xch Varchar(20);
CASE xv
WHEN 2 THEN SET xch := ' vingt';
WHEN 3 THEN SET xch := ' trente';
WHEN 4 THEN SET xch := ' quarante';
WHEN 5 THEN SET xch := ' cinquante';
WHEN 6 THEN SET xch := ' soixante';
WHEN 7 THEN SET xch := ' soixante-dix';
WHEN 8 THEN SET xch := ' quatre-vingt';
WHEN 9 THEN SET xch := ' quatre-vingt-dix';
END CASE;
RETURN xch;
END
$$
DELIMITER ;
DROP FUNCTION IF EXISTS tabx;
DELIMITER $$
CREATE FUNCTION tabx(xv int) RETURNS varchar(255)
BEGIN
DECLARE xch Varchar(20);
CASE xv
WHEN 1 THEN SET xch := ' cent';
WHEN 2 THEN SET xch := ' mille';
WHEN 3 THEN SET xch := ' million';
WHEN 4 THEN SET xch := ' milliard';
WHEN 5 THEN SET xch := ' billion';
End Case;
RETURN xch;
END
$$
DELIMITER ;
/*------------------------------------------------------------------------------*/
DROP FUNCTION IF EXISTS result_1_99;
DELIMITER $$
CREATE FUNCTION result_1_99(ch varchar(255)) RETURNS varchar(255)
BEGIN
DECLARE xv INT;
SET xv = CAST(ch as UNSIGNED);
CASE
WHEN XV BETWEEN 0 AND 19 THEN RETURN tab_1_19(CAST(ch as UNSIGNED));
WHEN XV BETWEEN 20 AND 99 THEN
CASE XV
WHEN XV IN(20,30,40,50,60) THEN RETURN tab_20_90(CAST(SUBSTRING(ch,1,1) AS UNSIGNED));
WHEN XV IN(21,31,41,51,61) THEN RETURN CONCAT(tab_20_90(CAST(SUBSTRING(ch,1,1) AS UNSIGNED)) , ' et' , tab_1_19(CAST(SUBSTRING(ch,2,1) AS UNSIGNED)));
WHEN XV = 80 THEN RETURN tab_20_90(CAST(SUBSTRING(ch,1,1) AS UNSIGNED));
WHEN XV BETWEEN 70 AND 79 THEN RETURN CONCAT(tab_20_90(CAST(SUBSTRING(ch,1,1) AS UNSIGNED) - 1) , '-' , SUBSTRING(tab_1_19(CAST(ch AS UNSIGNED) - 60),2,length(tab_1_19(CAST(ch AS UNSIGNED) - 60)) -1));
WHEN XV BETWEEN 81 AND 89 THEN RETURN CONCAT(tab_20_90(CAST(SUBSTRING(ch,1,1) AS UNSIGNED)) , '-' , SUBSTRING(tab_1_19(CAST(ch AS UNSIGNED) - 80),2,length(tab_1_19(CAST(ch AS UNSIGNED) - 80) -1)));
WHEN XV BETWEEN 90 AND 99 THEN RETURN CONCAT(tab_20_90(CAST(SUBSTRING(ch,1,1) AS UNSIGNED) - 1) , '-' , SUBSTRING(tab_1_19(CAST(ch AS UNSIGNED) - 80),2,length(tab_1_19(CAST(ch AS UNSIGNED) - 80))-1 ));
ELSE
RETURN CONCAT(tab_20_90(CAST(SUBSTRING(ch,1,1) AS UNSIGNED)) , '-' , SUBSTRING(tab_1_19(CAST(SUBSTRING(ch,2,1) AS UNSIGNED)),2,length(tab_1_19(CAST(SUBSTRING(ch,2,1) AS UNSIGNED))) -1));
END CASE;
ELSE
RETURN '';
END CASE;
END
$$
DELIMITER ;
/*-------------------------------------------------------------------*/
/*--------------------------------------------------------------------*/
/*--------------------------------------------------------------------*/
DROP FUNCTION IF EXISTS Convert_FR;
DELIMITER $$
CREATE FUNCTION Convert_FR(chIFfre real, SM varchar(40),CM varchar(40)) RETURNS varchar(255)
BEGIN
DECLARE ch_int Varchar(20);
DECLARE ch, ch_r Varchar(255);
DECLARE i, fin_I INT;
SET ch_int = chIFfre * 100 div 100 ;
SET ch_r = '';
SET fin_i = length(ch_int) div 3;
IF length(ch_int) > fin_i * 3 THEN
SET ch = SUBSTRING(ch_int,1, length(ch_int) - fin_i * 3);
SET ch_r = result_1_99(ch);
IF CAST(ch AS UNSIGNED) = 1 AND fin_i = 1 THEN
SET ch_r = tabx(fin_i + 1);
ELSE
IF length(ch_int) > 2 THEN
SET ch_r = CONCAT(ch_r , tabx(fin_i + 1));
END IF;
END IF;
END IF;
SET i = fin_i ;
WHILE I > 0 DO
SET ch = SUBSTRING(ch_int, length(ch_int) - i * 3 + 1 , 3);
CASE CAST(SUBSTRING(ch,1,1) AS UNSIGNED)
WHEN 0 THEN SET ch_r = CONCAT(ch_r , result_1_99(SUBSTRING(ch,1,1))); /* dans le cas 0, on peut remplacer result_1_99(SUBSTRING(ch,1,1)) par une chaine vide. C'est à dire ch_r = ch_r + ''; */
WHEN 1 THEN SET ch_r = CONCAT(ch_r , tabx(1)); /* là aussi on peut mettre cent à la place de tab[1] */
ELSE
SET ch_r = CONCAT(ch_r , result_1_99(SUBSTRING(ch,1,1)) , tabx(1));
END CASE;
IF (i = 2) AND (CAST(ch AS UNSIGNED) = 1) THEN /* pour le cas de par ex 125001489 */
SET ch_r = CONCAT(ch_r , SUBSTRING(result_1_99(SUBSTRING(ch,2,2)),3,length(result_1_99(SUBSTRING(ch,2,2))) - 3 ));
ELSE
SET ch_r = CONCAT(ch_r , result_1_99(SUBSTRING(ch,2,2)));
END IF;
IF (i > 1) AND (CAST(ch AS UNSIGNED) > 0) THEN
SET ch_r = CONCAT(ch_r , tabx(i));
END IF;
SET I = I - 1;
END WHILE;
IF Trim(SM) <> '' THEN
SET ch_r = CONCAT(Upper(SUBSTRING(ch_r,2,1)) , SUBSTRING(ch_r,3,length(ch_r) - 2) , SM);
IF chiffre * 100 MOD 100 > 0 THEN
/* ch_r := ch_r + ', et ' + SUBSTRING(floattostr(frac(chiffre) + 1.005 ),3,2) + ' ' centimes.'; */
SET ch_r = CONCAT(ch_r , ' et ' , result_1_99(chiffre * 100 MOD 100 ) , CM);
END IF;
/* le 1.005 pour obtenir le bon resultat, enlever le et essayer avec les montants suivants: par ex 15,20 ou 147,50 */
END IF;
RETURN ch_r;
END
$$
DELIMITER ;
Vous n'êtes pas encore membre ?
inscrivez-vous, c'est gratuit et ça prend moins d'une minute !
Les membres obtiennent plus de réponses que les utilisateurs anonymes.
Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.
Le fait d'être membre vous permet d'avoir des options supplémentaires.