sql_cli/sql/functions/
number_words.rs

1use crate::data::datatable::DataValue;
2use crate::sql::functions::{ArgCount, FunctionCategory, FunctionSignature, SqlFunction};
3use anyhow::Result;
4
5/// Convert number to English words
6pub struct ToWords;
7
8impl SqlFunction for ToWords {
9    fn signature(&self) -> FunctionSignature {
10        FunctionSignature {
11            name: "TO_WORDS",
12            category: FunctionCategory::String,
13            arg_count: ArgCount::Fixed(1),
14            description: "Convert number to English words",
15            returns: "String with number spelled out in words",
16            examples: vec![
17                "SELECT TO_WORDS(42)        -- Returns 'forty-two'",
18                "SELECT TO_WORDS(999)       -- Returns 'nine hundred ninety-nine'",
19                "SELECT TO_WORDS(1234)      -- Returns 'one thousand two hundred thirty-four'",
20                "SELECT TO_WORDS(1000000)   -- Returns 'one million'",
21            ],
22        }
23    }
24
25    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
26        if args.len() != 1 {
27            return Ok(DataValue::Null);
28        }
29
30        let num = match &args[0] {
31            DataValue::Float(n) => *n as i64,
32            DataValue::Integer(n) => *n,
33            DataValue::Null => return Ok(DataValue::Null),
34            _ => return Ok(DataValue::Null),
35        };
36
37        if num < 0 {
38            Ok(DataValue::String(format!(
39                "negative {}",
40                number_to_words(-num)
41            )))
42        } else {
43            Ok(DataValue::String(number_to_words(num)))
44        }
45    }
46}
47
48/// Convert number to ordinal words (1st, 2nd, 3rd, etc.)
49pub struct ToOrdinal;
50
51impl SqlFunction for ToOrdinal {
52    fn signature(&self) -> FunctionSignature {
53        FunctionSignature {
54            name: "TO_ORDINAL",
55            category: FunctionCategory::String,
56            arg_count: ArgCount::Fixed(1),
57            description: "Convert number to ordinal form (1st, 2nd, 3rd, etc.)",
58            returns: "String with ordinal representation",
59            examples: vec![
60                "SELECT TO_ORDINAL(1)       -- Returns '1st'",
61                "SELECT TO_ORDINAL(2)       -- Returns '2nd'",
62                "SELECT TO_ORDINAL(21)      -- Returns '21st'",
63                "SELECT TO_ORDINAL(123)     -- Returns '123rd'",
64            ],
65        }
66    }
67
68    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
69        if args.len() != 1 {
70            return Ok(DataValue::Null);
71        }
72
73        let num = match &args[0] {
74            DataValue::Float(n) => *n as i64,
75            DataValue::Integer(n) => *n,
76            DataValue::Null => return Ok(DataValue::Null),
77            _ => return Ok(DataValue::Null),
78        };
79
80        Ok(DataValue::String(number_to_ordinal(num)))
81    }
82}
83
84/// Convert number to ordinal words
85pub struct ToOrdinalWords;
86
87impl SqlFunction for ToOrdinalWords {
88    fn signature(&self) -> FunctionSignature {
89        FunctionSignature {
90            name: "TO_ORDINAL_WORDS",
91            category: FunctionCategory::String,
92            arg_count: ArgCount::Fixed(1),
93            description: "Convert number to ordinal words (first, second, third, etc.)",
94            returns: "String with ordinal words",
95            examples: vec![
96                "SELECT TO_ORDINAL_WORDS(1)   -- Returns 'first'",
97                "SELECT TO_ORDINAL_WORDS(21)  -- Returns 'twenty-first'",
98                "SELECT TO_ORDINAL_WORDS(100) -- Returns 'one hundredth'",
99            ],
100        }
101    }
102
103    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
104        if args.len() != 1 {
105            return Ok(DataValue::Null);
106        }
107
108        let num = match &args[0] {
109            DataValue::Float(n) => *n as i64,
110            DataValue::Integer(n) => *n,
111            DataValue::Null => return Ok(DataValue::Null),
112            _ => return Ok(DataValue::Null),
113        };
114
115        Ok(DataValue::String(number_to_ordinal_words(num)))
116    }
117}
118
119/// Convert a number to English words
120fn number_to_words(n: i64) -> String {
121    if n == 0 {
122        return "zero".to_string();
123    }
124
125    let units = [
126        "",
127        "one",
128        "two",
129        "three",
130        "four",
131        "five",
132        "six",
133        "seven",
134        "eight",
135        "nine",
136        "ten",
137        "eleven",
138        "twelve",
139        "thirteen",
140        "fourteen",
141        "fifteen",
142        "sixteen",
143        "seventeen",
144        "eighteen",
145        "nineteen",
146    ];
147
148    let tens = [
149        "", "", "twenty", "thirty", "forty", "fifty", "sixty", "seventy", "eighty", "ninety",
150    ];
151
152    let thousands = [
153        "",
154        "thousand",
155        "million",
156        "billion",
157        "trillion",
158        "quadrillion",
159    ];
160
161    fn convert_hundreds(n: i64, units: &[&str], tens: &[&str]) -> String {
162        let mut result = String::new();
163
164        let hundreds = n / 100;
165        let remainder = n % 100;
166
167        if hundreds > 0 {
168            result.push_str(units[hundreds as usize]);
169            result.push_str(" hundred");
170            if remainder > 0 {
171                result.push(' ');
172            }
173        }
174
175        if remainder < 20 {
176            result.push_str(units[remainder as usize]);
177        } else {
178            let tens_digit = remainder / 10;
179            let ones_digit = remainder % 10;
180            result.push_str(tens[tens_digit as usize]);
181            if ones_digit > 0 {
182                result.push('-');
183                result.push_str(units[ones_digit as usize]);
184            }
185        }
186
187        result
188    }
189
190    let mut num = n;
191    let mut parts = Vec::new();
192    let mut thousand_index = 0;
193
194    while num > 0 && thousand_index < thousands.len() {
195        let group = num % 1000;
196        if group > 0 {
197            let mut part = convert_hundreds(group, &units, &tens);
198            if !thousands[thousand_index].is_empty() {
199                part.push(' ');
200                part.push_str(thousands[thousand_index]);
201            }
202            parts.push(part);
203        }
204        num /= 1000;
205        thousand_index += 1;
206    }
207
208    parts.reverse();
209    parts.join(" ")
210}
211
212/// Convert number to ordinal form (1st, 2nd, 3rd, etc.)
213fn number_to_ordinal(n: i64) -> String {
214    let abs_n = n.abs();
215    let suffix = if abs_n % 100 >= 11 && abs_n % 100 <= 13 {
216        "th"
217    } else {
218        match abs_n % 10 {
219            1 => "st",
220            2 => "nd",
221            3 => "rd",
222            _ => "th",
223        }
224    };
225
226    format!("{}{}", n, suffix)
227}
228
229/// Convert number to ordinal words
230fn number_to_ordinal_words(n: i64) -> String {
231    // Special cases for common ordinals
232    match n {
233        1 => return "first".to_string(),
234        2 => return "second".to_string(),
235        3 => return "third".to_string(),
236        4 => return "fourth".to_string(),
237        5 => return "fifth".to_string(),
238        6 => return "sixth".to_string(),
239        7 => return "seventh".to_string(),
240        8 => return "eighth".to_string(),
241        9 => return "ninth".to_string(),
242        10 => return "tenth".to_string(),
243        11 => return "eleventh".to_string(),
244        12 => return "twelfth".to_string(),
245        20 => return "twentieth".to_string(),
246        30 => return "thirtieth".to_string(),
247        40 => return "fortieth".to_string(),
248        50 => return "fiftieth".to_string(),
249        60 => return "sixtieth".to_string(),
250        70 => return "seventieth".to_string(),
251        80 => return "eightieth".to_string(),
252        90 => return "ninetieth".to_string(),
253        100 => return "one hundredth".to_string(),
254        1000 => return "one thousandth".to_string(),
255        _ => {}
256    }
257
258    // For compound numbers, convert the base and add ordinal to the last part
259    if n < 100 && n > 20 {
260        let tens = (n / 10) * 10;
261        let ones = n % 10;
262        if ones == 0 {
263            return number_to_ordinal_words(tens);
264        }
265        let tens_word = number_to_words(tens);
266        let ones_ordinal = number_to_ordinal_words(ones);
267        return format!("{}-{}", tens_word, ones_ordinal);
268    }
269
270    // For larger numbers, convert to words and add "th"
271    let words = number_to_words(n);
272
273    // Handle special endings
274    if words.ends_with("one") {
275        format!(
276            "{}st",
277            words
278                .trim_end_matches("one")
279                .trim_end_matches('-')
280                .trim_end_matches(' ')
281        )
282    } else if words.ends_with("two") {
283        format!(
284            "{}second",
285            words
286                .trim_end_matches("two")
287                .trim_end_matches('-')
288                .trim_end_matches(' ')
289        )
290    } else if words.ends_with("three") {
291        format!(
292            "{}third",
293            words
294                .trim_end_matches("three")
295                .trim_end_matches('-')
296                .trim_end_matches(' ')
297        )
298    } else if words.ends_with("y") {
299        format!("{}ieth", words.trim_end_matches('y'))
300    } else {
301        format!("{}th", words)
302    }
303}
304
305#[cfg(test)]
306mod tests {
307    use super::*;
308
309    #[test]
310    fn test_to_words() {
311        let func = ToWords;
312
313        assert_eq!(
314            func.evaluate(&[DataValue::Float(0.0)]).unwrap(),
315            DataValue::String("zero".to_string())
316        );
317
318        assert_eq!(
319            func.evaluate(&[DataValue::Float(42.0)]).unwrap(),
320            DataValue::String("forty-two".to_string())
321        );
322
323        assert_eq!(
324            func.evaluate(&[DataValue::Float(999.0)]).unwrap(),
325            DataValue::String("nine hundred ninety-nine".to_string())
326        );
327
328        assert_eq!(
329            func.evaluate(&[DataValue::Float(1234.0)]).unwrap(),
330            DataValue::String("one thousand two hundred thirty-four".to_string())
331        );
332
333        assert_eq!(
334            func.evaluate(&[DataValue::Float(1000000.0)]).unwrap(),
335            DataValue::String("one million".to_string())
336        );
337    }
338
339    #[test]
340    fn test_to_ordinal() {
341        let func = ToOrdinal;
342
343        assert_eq!(
344            func.evaluate(&[DataValue::Float(1.0)]).unwrap(),
345            DataValue::String("1st".to_string())
346        );
347
348        assert_eq!(
349            func.evaluate(&[DataValue::Float(2.0)]).unwrap(),
350            DataValue::String("2nd".to_string())
351        );
352
353        assert_eq!(
354            func.evaluate(&[DataValue::Float(3.0)]).unwrap(),
355            DataValue::String("3rd".to_string())
356        );
357
358        assert_eq!(
359            func.evaluate(&[DataValue::Float(11.0)]).unwrap(),
360            DataValue::String("11th".to_string())
361        );
362
363        assert_eq!(
364            func.evaluate(&[DataValue::Float(21.0)]).unwrap(),
365            DataValue::String("21st".to_string())
366        );
367
368        assert_eq!(
369            func.evaluate(&[DataValue::Float(123.0)]).unwrap(),
370            DataValue::String("123rd".to_string())
371        );
372    }
373
374    #[test]
375    fn test_to_ordinal_words() {
376        let func = ToOrdinalWords;
377
378        assert_eq!(
379            func.evaluate(&[DataValue::Float(1.0)]).unwrap(),
380            DataValue::String("first".to_string())
381        );
382
383        assert_eq!(
384            func.evaluate(&[DataValue::Float(21.0)]).unwrap(),
385            DataValue::String("twenty-first".to_string())
386        );
387
388        assert_eq!(
389            func.evaluate(&[DataValue::Float(100.0)]).unwrap(),
390            DataValue::String("one hundredth".to_string())
391        );
392    }
393}
sql_cli/sql/functions/number_words.rs

sql_cli/sql/functions/
number_words.rs
