Monday, July 22, 2019

Amount to Words Convert via PostgreSQL

July 22, 2019 by 
asd


?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
CREATE OR REPLACE FUNCTION dateToWords(the_date TIMESTAMP) RETURNS text AS $$
DECLARE
_month INTEGER;
_day  INTEGER;
_year INTEGER;
_hour INTEGER;
_minute INTEGER;
_year_text TEXT;
_hour_text TEXT;
_day_text TEXT;
_minute_text TEXT;
_exact_time TEXT;
_month_text TEXT;
e TEXT;
BEGIN
_month = EXTRACT(MONTH FROM the_date);
_day = EXTRACT(DAY FROM the_date);
_year = EXTRACT(YEAR FROM the_date);
_hour = EXTRACT(HOUR FROM the_date);
_minute = EXTRACT(MINUTE FROM the_date);
 
 
WITH Below20(Word, Id) AS
(
VALUES
  ('Zero', 0), ('One', 1),( 'Two', 2 ), ( 'Three', 3),
  ( 'Four', 4 ), ( 'Five', 5 ), ( 'Six', 6 ), ( 'Seven', 7 ),
  ( 'Eight', 8), ( 'Nine', 9), ( 'Ten', 10), ( 'Eleven', 11 ),
  ( 'Twelve', 12 ), ( 'Thirteen', 13 ), ( 'Fourteen', 14),
  ( 'Fifteen', 15 ), ('Sixteen', 16 ), ( 'Seventeen', 17),
  ('Eighteen', 18 ), ( 'Nineteen', 19 )
),
Below100(Word, Id) AS
(
  VALUES
   ('Twenty', 2), ('Thirty', 3),('Forty', 4), ('Fifty', 5),
   ('Sixty', 6), ('Seventy', 7), ('Eighty', 8), ('Ninety', 9)
)SELECT
CASE
  WHEN (_year % 1000) BETWEEN 1 AND 19 THEN
  CONCAT(
      (Select Word FROM Below20 WHERE ID=_year / 1000), ' Thousand ',
      (Select Word FROM Below20 WHERE ID=_year % 1000)
    )
  WHEN (_year % 1000) BETWEEN 20 AND 99 THEN
  CONCAT(
      (Select Word FROM Below20 WHERE ID=_year / 1000), ' Thousand ',
      (Select Word FROM Below20 WHERE ID=(_year % 1000) / 10), ' ',
      (Select Word FROM Below20 WHERE ID=(_year % 1000) / 10)
    )
  WHEN (_year % 1000) BETWEEN 100 AND 999 THEN
    CASE
        WHEN (_year % 1000) % 100 BETWEEN 1 AND 19 THEN
        CONCAT(
            (Select Word FROM Below20 WHERE ID=_year / 1000), ' Thousand ',
            (Select Word FROM Below20 WHERE ID=(_year % 1000) / 100), ' Hundred ',
            (Select Word FROM Below20 WHERE ID=((_year % 1000) % 100))
          )
        WHEN (_year % 1000) % 100 BETWEEN 20 AND 99 THEN
        CONCAT(
            (Select Word FROM Below20 WHERE ID=_year / 1000), ' Thousand ',
            (Select Word FROM Below20 WHERE ID=(_year % 1000) / 100), ' Hundred ',
            (Select Word FROM Below100 WHERE ID=((_year % 1000) % 100) / 10), ' ',
            (Select Word FROM Below20 WHERE ID=((_year % 1000) % 100) % 10)
          )
    END
END INTO _year_text;
 
 
WITH Below20(Word, Id) AS
(
 VALUES
   ('', 0), ('One', 1),( 'Two', 2 ), ( 'Three', 3),
   ( 'Four', 4 ), ( 'Five', 5 ), ( 'Six', 6 ), ( 'Seven', 7 ),
   ( 'Eight', 8), ( 'Nine', 9), ( 'Ten', 10), ( 'Eleven', 11 ),
   ( 'Twelve', 12 )
)
Select
    CASE
      WHEN _hour > 12 THEN (Select Word FROM Below20 WHERE ID= _hour - 12)
      ELSE (Select Word FROM Below20 WHERE ID= _hour)
END INTO _hour_text;
 
 
 
WITH Below20(Word, Id) AS
(
VALUES
  ('', 0), ('First', 1),( 'Second', 2 ), ( 'Third', 3),
  ( 'Fourth', 4 ), ( 'Fifth', 5 ), ( 'Sixth', 6 ), ( 'Seventh', 7 ),
  ( 'Eighth', 8), ( 'Ninth', 9), ( 'Tenth', 10), ( 'Eleventh', 11 ),
  ( 'Twelfth', 12 ), ( 'Thirteenth', 13 ), ( 'Fourteenth', 14),
  ( 'Fifteenth', 15 ), ('Sixteenth', 16 ), ( 'Seventeenth', 17),
  ('Eighteenth', 18 ), ( 'Nineteenth', 19 )
),
Below100(Word, Id) AS
(
   VALUES
    ('Twenty', 2), ('Thirty', 3)
)
Select
CASE
  WHEN _day BETWEEN 1 AND 19 THEN (Select Word FROM Below20 WHERE ID=_day)
  ELSE
  CONCAT(
      (Select Word FROM Below100 WHERE ID= _day / 10), ' ',
      (Select Word FROM Below20 WHERE ID= _day % 10)
   )
END INTO _day_text;
 
 
 
WITH Below20(Word, Id) AS
(
VALUES
  ('', 0), ('One', 1),( 'Two', 2 ), ( 'Three', 3),
  ( 'Four', 4 ), ( 'Five', 5 ), ( 'Six', 6 ), ( 'Seven', 7 ),
  ( 'Eight', 8), ( 'Nine', 9), ( 'Ten', 10), ( 'Eleven', 11 ),
  ( 'Twelve', 12 ), ( 'Thirteen', 13 ), ( 'Fourteen', 14),
  ( 'Fifteen', 15 ), ('Sixteen', 16 ), ( 'Seventeen', 17),
  ('Eighteen', 18 ), ( 'Nineteen', 19 )
),
Below100(Word, Id) AS
(
   VALUES
    ('Twenty', 2), ('Thirty', 3),('Forty', 4), ('Fifty', 5),
    ('Sixty', 6)
)
Select
CASE
  WHEN _minute BETWEEN 1 AND 19 THEN (Select Word FROM Below20 WHERE ID=_day)
  ELSE
  CONCAT(
      (Select Word FROM Below100 WHERE ID= _minute / 10), ' ',
      (Select Word FROM Below20 WHERE ID= _minute % 10)
   )
END INTO _minute_text;
 
 
 
SELECT
CASE
WHEN _hour BETWEEN 0 AND 12 THEN 'Morning'
WHEN _hour BETWEEN 13 AND 17 THEN 'Afternoon'
ELSE 'Evening'
END INTO _exact_time;
 
 
 
WITH nameOfTheMonth(monthAsText, Id) AS
(
VALUES
  ('January', 1), ('February', 2),( 'MARCH', 3), ( 'April', 4),
  ( 'May', 5), ( 'June', 6), ( 'July', 7), ( 'August', 8),
  ( 'September', 9), ( 'October', 10), ( 'November', 11), ( 'December', 12)
)
select monthAsText FROM nameOfTheMonth WHERE ID=_month INTO _month_text;
 
 
SELECT CONCAT(
      _day_text, ' day of', ' ',
      _month_text,', ',
     _year_text,', ',
     _hour_text,' ',
     _minute_text, ' in the ',
  _exact_time)
    INTO e;
 
 
return e;
END; $$
LANGUAGE PLPGSQL;
 
 
select dateToWords('May 15, 2018 5:38 PM')

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)