在MySQL中将数字转换为单词 [英] Converting a number into a word in Mysql
本文介绍了在MySQL中将数字转换为单词的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我从数据库中获取一个值,并且希望将此值转换为 Mysql 中的单词.有人可以帮我吗?
I am getting a value from database and I want this value to be converted into a word in Mysql. can someone please help me out.
例如: 123 ->该值从数据库中获取,并以数字形式保存,我想将此值检索为 123.从数据库中获取.
Eg: 123 --> this value am getting from database and it is getting saved in a numeric form and I want to retrieve this value as hundred and twenty three from the database.
这样做的语法是什么?
查询是这样的:
select 'value' from value_table where date is '10-10-2012';
ans是-> 123
ans is--> 123
我希望此值显示为123.
I want this value to be displayed as hundred and twenty three.
推荐答案
我认为这可以帮助您...这只是一种方法:
I think this can help you... it's just a way to do it:
DELIMITER $$
CREATE FUNCTION `number_to_string`(n INT) RETURNS varchar(100)
BEGIN
-- This function returns the string representation of a number.
-- It's just an example... I'll restrict it to hundreds, but
-- it can be extended easily.
-- The idea is:
-- For each digit you need a position,
-- For each position, you assign a string
declare ans varchar(100);
declare dig1, dig2, dig3 int; -- (one variable per digit)
set ans = '';
set dig3 = floor(n / 100);
set dig2 = floor(n / 10) - dig3*10;
set dig1 = n - (dig3*100 + dig2*10);
if dig3 > 0 then
case
when dig3=1 then set ans=concat(ans, 'one hundred');
when dig3=2 then set ans=concat(ans, 'two hundred');
when dig3=3 then set ans=concat(ans, 'three hundred');
when dig3=4 then set ans=concat(ans, 'four hundred');
when dig3=5 then set ans=concat(ans, 'five hundred');
when dig3=6 then set ans=concat(ans, 'six hundred');
when dig3=7 then set ans=concat(ans, 'seven hundred');
when dig3=8 then set ans=concat(ans, 'eight hundred');
when dig3=9 then set ans=concat(ans, 'nine hundred');
else set ans = ans;
end case;
end if;
if dig2 = 1 then
case
when (dig2*10 + dig1) = 10 then set ans=concat(ans,' ten');
when (dig2*10 + dig1) = 11 then set ans=concat(ans,' eleven');
when (dig2*10 + dig1) = 12 then set ans=concat(ans,' twelve');
when (dig2*10 + dig1) = 13 then set ans=concat(ans,' thirteen');
when (dig2*10 + dig1) = 14 then set ans=concat(ans,' fourteen');
when (dig2*10 + dig1) = 15 then set ans=concat(ans,' fifteen');
when (dig2*10 + dig1) = 16 then set ans=concat(ans,' sixteen');
when (dig2*10 + dig1) = 17 then set ans=concat(ans,' seventeen');
when (dig2*10 + dig1) = 18 then set ans=concat(ans,' eighteen');
when (dig2*10 + dig1) = 19 then set ans=concat(ans,' nineteen');
else set ans=ans;
end case;
else
if dig2 > 0 then
case
when dig2=2 then set ans=concat(ans, ' twenty');
when dig2=3 then set ans=concat(ans, ' thirty');
when dig2=4 then set ans=concat(ans, ' fourty');
when dig2=5 then set ans=concat(ans, ' fifty');
when dig2=6 then set ans=concat(ans, ' sixty');
when dig2=7 then set ans=concat(ans, ' seventy');
when dig2=8 then set ans=concat(ans, ' eighty');
when dig2=9 then set ans=concat(ans, ' ninety');
else set ans=ans;
end case;
end if;
if dig1 > 0 then
case
when dig1=1 then set ans=concat(ans, ' one');
when dig1=2 then set ans=concat(ans, ' two');
when dig1=3 then set ans=concat(ans, ' three');
when dig1=4 then set ans=concat(ans, ' four');
when dig1=5 then set ans=concat(ans, ' five');
when dig1=6 then set ans=concat(ans, ' six');
when dig1=7 then set ans=concat(ans, ' seven');
when dig1=8 then set ans=concat(ans, ' eight');
when dig1=9 then set ans=concat(ans, ' nine');
else set ans=ans;
end case;
end if;
end if;
return trim(ans);
END$$
DELIMITER ;
这篇关于在MySQL中将数字转换为单词的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文