在印度编号系统中将数字转换成单词

本文介绍了在印度编号系统中将数字转换成单词的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在使用此代码将数字转换为单词

I am converting numbers to words with this code

select to_char(to_date(:number,'j'),'jsp') from dual;

当我输入值100000时，输出为One Hundred Thousand，但我希望输出显示:

when I enter value 100000 then output is One Hundred Thousand but I want output show:

One Lac

如何处理?

推荐答案

SQL提琴

Oracle 11g R2架构设置:

CREATE TABLE numbers ( value ) AS
SELECT               0 FROM DUAL UNION ALL
SELECT          123456 FROM DUAL UNION ALL
SELECT        12345678 FROM DUAL UNION ALL
SELECT   1234567890123 FROM DUAL UNION ALL
SELECT 123456789012345 FROM DUAL UNION ALL
SELECT 900000000000000 FROM DUAL UNION ALL
SELECT 700000001200000 FROM DUAL UNION ALL
SELECT 543000000000000 FROM DUAL;

查询1 :

WITH rsqfc ( original, value, suffix, result, lvl, mxlvl ) AS (
  SELECT value,
         TRUNC( value / 10000000 ),
         CAST( 'CRORE ' AS VARCHAR2(4000) ),
         CAST(
           RTRIM(
             CASE
             WHEN MOD( TRUNC( value / 100000 ), 100 ) > 0
             THEN TO_CHAR(
                    TO_DATE(
                      MOD( TRUNC( value / 100000 ), 100 ),
                      'J'
                    ),
                    'JSP'
                  ) || ' LAKH '
             END
             ||
             CASE
             WHEN value = 0
             THEN 'ZERO'
             WHEN MOD( value, 100000 ) > 0
             THEN TO_CHAR(
                    TO_DATE(
                      MOD( value, 100000 ),
                      'J'
                    ),
                    'JSP'
                  ) || ' '
             END
           )
           AS VARCHAR2(4000)
         ),
         1,
         CEIL( LENGTH( ABS( value ) ) / 7 )
  FROM   numbers
UNION ALL
  SELECT original,
         TRUNC( value / 10000000 ),
         'CRORE ' || suffix,
         RTRIM(
           CASE
           WHEN MOD( TRUNC( value / 100000 ), 100 ) > 0
           THEN TO_CHAR(
                  TO_DATE(
                    MOD( TRUNC( value / 100000 ), 100 ),
                    'J'
                  ),
                  'JSP'
                ) || ' LAKH ' || suffix
           END
           ||
           CASE
           WHEN MOD( value, 100000 ) > 0
           THEN TO_CHAR(
                  TO_DATE(
                    MOD( value, 100000 ),
                    'J'
                  ),
                  'JSP'
                ) || ' ' || suffix
           END
           || result
         ),
         lvl + 1,
         mxlvl
  FROM   rsqfc
  WHERE  lvl < mxlvl
)
SELECT original, result
FROM   rsqfc
WHERE  lvl = mxlvl

结果 :

Results:

|        ORIGINAL |                                                                                                                                           RESULT |
|-----------------|--------------------------------------------------------------------------------------------------------------------------------------------------|
|               0 |                                                                                                                                             ZERO |
|          123456 |                                                                                            ONE LAKH TWENTY-THREE THOUSAND FOUR HUNDRED FIFTY-SIX |
|        12345678 |                                                                        ONE CRORE TWENTY-THREE LAKH FORTY-FIVE THOUSAND SIX HUNDRED SEVENTY-EIGHT |
|   1234567890123 |                    ONE LAKH CRORE TWENTY-THREE THOUSAND FOUR HUNDRED FIFTY-SIX CRORE SEVENTY-EIGHT LAKH NINETY THOUSAND ONE HUNDRED TWENTY-THREE |
| 123456789012345 | ONE CRORE CRORE TWENTY-THREE LAKH CRORE FORTY-FIVE THOUSAND SIX HUNDRED SEVENTY-EIGHT CRORE NINETY LAKH TWELVE THOUSAND THREE HUNDRED FORTY-FIVE |
| 900000000000000 |                                                                                                                                 NINE CRORE CRORE |
| 700000001200000 |                                                                                                                    SEVEN CRORE CRORE TWELVE LAKH |
| 543000000000000 |                                                                                                          FIVE CRORE CRORE FORTY-THREE LAKH CRORE |

这篇关于在印度编号系统中将数字转换成单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！