How SQL Server creates language helper functions

In a globalized environment like this, because there are many different grammatical rules in different languages, many of the simple tasks of the past now become difficult. You can generalize a particular language by dividing it into a set of grammatical rules and exceptions to these rules (and a basic word). In some programming languages, such as Perl and Java, there are common domain modules that can be used to complete language transformations on text.

Here's a slightly simpler example, assuming we want to convert a number to its spelling version (for example, a check and a legal contract are required). This trick has been in the early days of Oracle, and is generally used in the following ways:

selectto_char(to_date(12345,'J'),'Jsp') from dual;
　　
　　Twelve Thousand Three Hundred Forty-Five

The To_date function converts a number to a date using the Julian date format. To_char then accepts a date parameter and formats it again as a string that represents the version of the spelling number for the Julian date. But there are some limitations to the know-how.

First, the maximum valid value for the Julian date in Oracle is 9999, so the maximum date value can only be 5373484, and the minimum value is 1 or 4712BC. Also, because there is no "0" year, it is not possible to generate the text "0" without using an additional DECODE or case statement. The third big limitation is that it ignores your NLS settings. No matter what language you use, the numbers are always spelled out in American English. Some simple operations also have such problems, such as spelling out the day. For example, try to generate the Spanish phrase "Cinco de Mayo":

　alter session set nls_language = 'SPANISH';
　　select to_char(to_date('0505','MMDD'),'Ddspth Month') from dual;
　　
　　Fifth Mayo

The syntax involved in generating numbers for most languages is actually fairly straightforward. Body work involves collecting all the different grammatical rules and establishing enough rules to generate the correct syntax patterns. (Now I'm going to avoid issues that involve matching numbers and gender.) ）

First, I'll create two tables: The first table holds basic words and exceptions, and the second table holds some simple template patterns for generating text. If there is a number in the first table, then my language function returns that text. For each of the other numbers, I will try to match it in a series of patterns and apply a template to generate the correct text.

create table numwords
　　(
　　　　lang　　varchar2(2),
　　　　num　　 integer,
　　　　word　　varchar2(30),
　　　　constraint numwords_pk primary key (lang,num)
　　);
　　
　　create table numrules
　　(
　　　　lang　　varchar2(2),
　　　　seq　　 integer,
　　　　p1　　　integer,
　　　　p2　　　integer,
　　　　temp0　 varchar2(30),
　　　　temp　　varchar2(30),
　　　　constraint numrules_pk primary key (lang,seq)
　　);

The following is the code needed to generate a numeric spelling version. Here I will follow the cardinality (such as 1, 2, and 3), and in fact, these functions can generate ordinals (1th, 2nd, third) and plural versions by listing more exceptions and patterns for each language.

REM--Create a table of base words and exceptions


Create or Replace package Genword


as


function Get_word (n number) return varchar2;


function cardinal (n number) return varchar2;


End Genword;


　　/


Show errors;


　　


Create or Replace package body Genword


as


function Get_word (n number) return VARCHAR2


is


L_wordnumwords.word%type;


begin


Select Word into L_word from numwords


where lang = Sys_context (' userenv ', ' lang ') and num = n;


return L_word;


Exception


when No_data_found then


return null;


end;


　　　　--


function cardinal (n number) return VARCHAR2


is


p number; --Power


T VARCHAR2 (30); --Template


v number; --Lower portion


L_word Numwords.word%type;


Begin


if n < 0 then


L_word: = Get_word (-1);


if L_word is null then


return null;


End If;


return l_word| | ' '|| Cardinal (-N);


End If;


L_word: = Get_word (n);


if L_word is not NULL then


return L_word;


End If;


for row in


　　　　　　(


SELECT * from Numrules


where lang = Sys_context (' userenv ', ' Lang ')


ORDER by Seq


　　　　　　)


Loop


if Length (n) <= row.p1 + row.p2 then


p: = Power (10,ROW.P2);


V: = mod (n,p);


if Row.seq = 0 Then


if n < then


Return replace (row.temp0, ' ~2 ', Cardinal (v));


End If;


Else


If v = 0 Then


return replace (row.temp0, ' ~ ', cardinal (n/p));


Else


return replace (replace NVL (row.temp, ' ~ ~2 '),


' ~ ', cardinal (n-v),


' ~2 ', Cardinal (v));


End If;


End If;


End If;


end Loop;


return ' number TOO LARGE ';


end Cardinal;


End Genword;


　　/


Show errors;