formualizer_eval/builtins/text/
find_search_exact.rs

1use super::super::utils::ARG_ANY_ONE;
2use crate::args::ArgSchema;
3use crate::function::Function;
4use crate::traits::{ArgumentHandle, FunctionContext};
5use formualizer_common::{ExcelError, ExcelErrorKind, LiteralValue};
6use formualizer_macros::func_caps;
7
8fn scalar_like_value(arg: &ArgumentHandle<'_, '_>) -> Result<LiteralValue, ExcelError> {
9    Ok(match arg.value()? {
10        crate::traits::CalcValue::Scalar(v) => v,
11        crate::traits::CalcValue::Range(rv) => rv.get_cell(0, 0),
12        crate::traits::CalcValue::Callable(_) => LiteralValue::Error(
13            ExcelError::new(ExcelErrorKind::Calc).with_message("LAMBDA value must be invoked"),
14        ),
15    })
16}
17
18fn to_text<'a, 'b>(a: &ArgumentHandle<'a, 'b>) -> Result<String, ExcelError> {
19    let v = scalar_like_value(a)?;
20    Ok(match v {
21        LiteralValue::Text(s) => s,
22        LiteralValue::Empty => String::new(),
23        LiteralValue::Boolean(b) => {
24            if b {
25                "TRUE".into()
26            } else {
27                "FALSE".into()
28            }
29        }
30        LiteralValue::Int(i) => i.to_string(),
31        LiteralValue::Number(f) => f.to_string(),
32        LiteralValue::Error(e) => return Err(e),
33        other => other.to_string(),
34    })
35}
36
37// FIND(find_text, within_text, [start_num]) - case sensitive
38#[derive(Debug)]
39pub struct FindFn;
40/// Returns the 1-based position of one text string inside another.
41///
42/// `FIND` is case-sensitive and does not interpret wildcard characters.
43///
44/// # Remarks
45/// - Search is case-sensitive (`"A"` and `"a"` are different).
46/// - `start_num` is 1-based and must be greater than `0`.
47/// - If no match is found, returns `#VALUE!`.
48/// - Errors in either argument are propagated.
49///
50/// # Examples
51///
52/// ```yaml,sandbox
53/// title: "Case-sensitive match"
54/// formula: '=FIND("World", "Hello World")'
55/// expected: 7
56/// ```
57///
58/// ```yaml,sandbox
59/// title: "Case mismatch fails"
60/// formula: '=FIND("world", "Hello World")'
61/// expected: "#VALUE!"
62/// ```
63///
64/// ```yaml,docs
65/// related:
66///   - SEARCH
67///   - EXACT
68///   - TEXTBEFORE
69/// faq:
70///   - q: "Do wildcard characters work in FIND?"
71///     a: "No. FIND treats * and ? as literal characters and matches case-sensitively."
72/// ```
73/// [formualizer-docgen:schema:start]
74/// Name: FIND
75/// Type: FindFn
76/// Min args: 2
77/// Max args: variadic
78/// Variadic: true
79/// Signature: FIND(arg1...: any@scalar)
80/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
81/// Caps: PURE
82/// [formualizer-docgen:schema:end]
83impl Function for FindFn {
84    func_caps!(PURE);
85    fn name(&self) -> &'static str {
86        "FIND"
87    }
88    fn min_args(&self) -> usize {
89        2
90    }
91    fn variadic(&self) -> bool {
92        true
93    }
94    fn arg_schema(&self) -> &'static [ArgSchema] {
95        &ARG_ANY_ONE[..]
96    }
97    fn eval<'a, 'b, 'c>(
98        &self,
99        args: &'c [ArgumentHandle<'a, 'b>],
100        _: &dyn FunctionContext<'b>,
101    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
102        if args.len() < 2 || args.len() > 3 {
103            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
104                ExcelError::new_value(),
105            )));
106        }
107        let needle = to_text(&args[0])?;
108        let hay = to_text(&args[1])?;
109        let start = if args.len() == 3 {
110            let n = number_like(&args[2])?;
111            if n < 1 {
112                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
113                    ExcelError::new_value(),
114                )));
115            }
116            (n - 1) as usize
117        } else {
118            0
119        };
120        if needle.is_empty() {
121            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Int(1)));
122        }
123        // FIND renvoie une position en CARACTERES (pas en octets) : indexer par char
124        // evite la panique "char boundary" sur l'accentue et donne la position Excel.
125        match char_find(&hay, &needle, start) {
126            Some(idx) => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Int(
127                (idx + 1) as i64,
128            ))),
129            None => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
130                ExcelError::new_value(),
131            ))),
132        }
133    }
134}
135
136// SEARCH(find_text, within_text, [start_num]) - case insensitive + simple wildcard * ?
137#[derive(Debug)]
138pub struct SearchFn;
139/// Returns the 1-based position of one text string inside another.
140///
141/// `SEARCH` is case-insensitive and supports `*` and `?` wildcards.
142///
143/// # Remarks
144/// - Search is case-insensitive.
145/// - `*` matches any sequence and `?` matches a single character.
146/// - `start_num` is 1-based and must be greater than `0`.
147/// - If no match is found, returns `#VALUE!`.
148///
149/// # Examples
150///
151/// ```yaml,sandbox
152/// title: "Case-insensitive search"
153/// formula: '=SEARCH("world", "Hello World")'
154/// expected: 7
155/// ```
156///
157/// ```yaml,sandbox
158/// title: "Wildcard pattern"
159/// formula: '=SEARCH("d?ta*", "Meta Data Lake")'
160/// expected: 6
161/// ```
162///
163/// ```yaml,docs
164/// related:
165///   - FIND
166///   - EXACT
167///   - SUBSTITUTE
168/// faq:
169///   - q: "How are case and wildcards handled?"
170///     a: "SEARCH is case-insensitive and supports * for any sequence plus ? for one character."
171/// ```
172/// [formualizer-docgen:schema:start]
173/// Name: SEARCH
174/// Type: SearchFn
175/// Min args: 2
176/// Max args: variadic
177/// Variadic: true
178/// Signature: SEARCH(arg1...: any@scalar)
179/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
180/// Caps: PURE
181/// [formualizer-docgen:schema:end]
182impl Function for SearchFn {
183    func_caps!(PURE);
184    fn name(&self) -> &'static str {
185        "SEARCH"
186    }
187    fn min_args(&self) -> usize {
188        2
189    }
190    fn variadic(&self) -> bool {
191        true
192    }
193    fn arg_schema(&self) -> &'static [ArgSchema] {
194        &ARG_ANY_ONE[..]
195    }
196    fn eval<'a, 'b, 'c>(
197        &self,
198        args: &'c [ArgumentHandle<'a, 'b>],
199        _: &dyn FunctionContext<'b>,
200    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
201        if args.len() < 2 || args.len() > 3 {
202            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
203                ExcelError::new_value(),
204            )));
205        }
206        let needle = to_text(&args[0])?.to_ascii_lowercase();
207        let hay_raw = to_text(&args[1])?;
208        let hay = hay_raw.to_ascii_lowercase();
209        let start = if args.len() == 3 {
210            let n = number_like(&args[2])?;
211            if n < 1 {
212                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
213                    ExcelError::new_value(),
214                )));
215            }
216            (n - 1) as usize
217        } else {
218            0
219        };
220        if needle.is_empty() {
221            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Int(1)));
222        }
223        // SEARCH renvoie une position en CARACTERES et accepte les jokers * et ?.
224        // On indexe par char (pas par octet) -> pas de panique "char boundary" sur
225        // l'accentue, et ? compte bien pour UN caractere (sémantique Excel).
226        let hay_chars: Vec<char> = hay.chars().collect();
227        if start > hay_chars.len() {
228            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
229                ExcelError::new_value(),
230            )));
231        }
232        let found = if needle.contains('*') || needle.contains('?') {
233            let pat: Vec<char> = needle.chars().collect();
234            char_wildcard_search(&pat, &hay_chars, start)
235        } else {
236            char_find(&hay, &needle, start)
237        };
238        match found {
239            Some(idx) => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Int(
240                (idx + 1) as i64,
241            ))),
242            None => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
243                ExcelError::from_error_string("#VALUE!"),
244            ))),
245        }
246    }
247}
248
249/// Recherche en espace CARACTERES (Excel) : position 0-based du 1er match de `needle`
250/// dans `hay` a partir du caractere `start`. Indexer par char (et non par octet) evite
251/// la panique "byte index is not a char boundary" sur les chaines accentuees.
252fn char_find(hay: &str, needle: &str, start: usize) -> Option<usize> {
253    let hay_chars: Vec<char> = hay.chars().collect();
254    let needle_chars: Vec<char> = needle.chars().collect();
255    if needle_chars.is_empty() {
256        return Some(start.min(hay_chars.len()));
257    }
258    if needle_chars.len() > hay_chars.len() || start > hay_chars.len() {
259        return None;
260    }
261    let last = hay_chars.len() - needle_chars.len();
262    let mut i = start;
263    while i <= last {
264        if hay_chars[i..i + needle_chars.len()] == needle_chars[..] {
265            return Some(i);
266        }
267        i += 1;
268    }
269    None
270}
271
272/// Recherche joker (* / ?) en espace CARACTERES. `?` = exactement un caractere.
273fn char_wildcard_search(pat: &[char], hay: &[char], start: usize) -> Option<usize> {
274    let mut i = start;
275    while i <= hay.len() {
276        if wildcard_match_chars(pat, &hay[i..]) {
277            return Some(i);
278        }
279        i += 1;
280    }
281    None
282}
283
284fn wildcard_match_chars(p: &[char], t: &[char]) -> bool {
285    if p.is_empty() {
286        return true;
287    }
288    match p[0] {
289        '*' => (0..=t.len()).any(|i| wildcard_match_chars(&p[1..], &t[i..])),
290        '?' => !t.is_empty() && wildcard_match_chars(&p[1..], &t[1..]),
291        c => !t.is_empty() && t[0] == c && wildcard_match_chars(&p[1..], &t[1..]),
292    }
293}
294
295// EXACT(text1,text2)
296#[derive(Debug)]
297pub struct ExactFn;
298/// Compares two text values for exact equality.
299///
300/// # Remarks
301/// - Comparison is case-sensitive.
302/// - No wildcard semantics are applied.
303/// - Non-text values are converted to text before comparison.
304/// - Errors in either argument are propagated.
305///
306/// # Examples
307///
308/// ```yaml,sandbox
309/// title: "Exact same text"
310/// formula: '=EXACT("Form", "Form")'
311/// expected: true
312/// ```
313///
314/// ```yaml,sandbox
315/// title: "Case difference is not equal"
316/// formula: '=EXACT("Form", "form")'
317/// expected: false
318/// ```
319///
320/// ```yaml,docs
321/// related:
322///   - FIND
323///   - SEARCH
324///   - UPPER
325/// faq:
326///   - q: "Does EXACT perform case-sensitive comparison?"
327///     a: "Yes. EXACT compares the resulting text values with exact case and character equality."
328/// ```
329/// [formualizer-docgen:schema:start]
330/// Name: EXACT
331/// Type: ExactFn
332/// Min args: 2
333/// Max args: 1
334/// Variadic: false
335/// Signature: EXACT(arg1: any@scalar)
336/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
337/// Caps: PURE
338/// [formualizer-docgen:schema:end]
339impl Function for ExactFn {
340    func_caps!(PURE);
341    fn name(&self) -> &'static str {
342        "EXACT"
343    }
344    fn min_args(&self) -> usize {
345        2
346    }
347    fn arg_schema(&self) -> &'static [ArgSchema] {
348        &ARG_ANY_ONE[..]
349    }
350    fn eval<'a, 'b, 'c>(
351        &self,
352        args: &'c [ArgumentHandle<'a, 'b>],
353        _: &dyn FunctionContext<'b>,
354    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
355        let a = to_text(&args[0])?;
356        let b = to_text(&args[1])?;
357        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Boolean(
358            a == b,
359        )))
360    }
361}
362
363fn number_like<'a, 'b>(a: &ArgumentHandle<'a, 'b>) -> Result<i64, ExcelError> {
364    let v = scalar_like_value(a)?;
365    Ok(match v {
366        LiteralValue::Int(i) => i,
367        LiteralValue::Number(f) => f as i64,
368        LiteralValue::Text(t) => t.parse::<i64>().unwrap_or(0),
369        LiteralValue::Boolean(b) => {
370            if b {
371                1
372            } else {
373                0
374            }
375        }
376        LiteralValue::Empty => 0,
377        LiteralValue::Error(e) => return Err(e),
378        other => other.to_string().parse::<i64>().unwrap_or(0),
379    })
380}
381
382pub fn register_builtins() {
383    use std::sync::Arc;
384    crate::function_registry::register_function(Arc::new(FindFn));
385    crate::function_registry::register_function(Arc::new(SearchFn));
386    crate::function_registry::register_function(Arc::new(ExactFn));
387}
388
389#[cfg(test)]
390mod tests {
391    use super::*;
392    use crate::test_workbook::TestWorkbook;
393    use crate::traits::ArgumentHandle;
394    use formualizer_common::LiteralValue;
395    use formualizer_parse::parser::{ASTNode, ASTNodeType};
396    fn lit(v: LiteralValue) -> ASTNode {
397        ASTNode::new(ASTNodeType::Literal(v), None)
398    }
399    #[test]
400    fn find_search() {
401        let wb = TestWorkbook::new()
402            .with_function(std::sync::Arc::new(FindFn))
403            .with_function(std::sync::Arc::new(SearchFn));
404        let ctx = wb.interpreter();
405        let f = ctx.context.get_function("", "FIND").unwrap();
406        let s = ctx.context.get_function("", "SEARCH").unwrap();
407        let hay = lit(LiteralValue::Text("Hello World".into()));
408        let needle = lit(LiteralValue::Text("World".into()));
409        assert_eq!(
410            f.dispatch(
411                &[
412                    ArgumentHandle::new(&needle, &ctx),
413                    ArgumentHandle::new(&hay, &ctx)
414                ],
415                &ctx.function_context(None)
416            )
417            .unwrap()
418            .into_literal(),
419            LiteralValue::Int(7)
420        );
421        let needle2 = lit(LiteralValue::Text("world".into()));
422        assert_eq!(
423            s.dispatch(
424                &[
425                    ArgumentHandle::new(&needle2, &ctx),
426                    ArgumentHandle::new(&hay, &ctx)
427                ],
428                &ctx.function_context(None)
429            )
430            .unwrap()
431            .into_literal(),
432            LiteralValue::Int(7)
433        );
434    }
435
436    /// Regression: FIND/SEARCH must index by CHARACTER (Excel), not by byte.
437    /// On multi-byte UTF-8 (accents), the old byte-based implementation returned wrong
438    /// positions and, in SEARCH's wildcard scan, panicked when a byte offset landed inside
439    /// a multi-byte char ("byte index N is not a char boundary").
440    #[test]
441    fn find_search_utf8_char_positions() {
442        let wb = TestWorkbook::new()
443            .with_function(std::sync::Arc::new(FindFn))
444            .with_function(std::sync::Arc::new(SearchFn));
445        let ctx = wb.interpreter();
446        let f = ctx.context.get_function("", "FIND").unwrap();
447        let s = ctx.context.get_function("", "SEARCH").unwrap();
448        let call =
449            |func: &std::sync::Arc<dyn crate::function::Function>, needle: &str, hay: &str| {
450                let n = lit(LiteralValue::Text(needle.into()));
451                let h = lit(LiteralValue::Text(hay.into()));
452                func.dispatch(
453                    &[ArgumentHandle::new(&n, &ctx), ArgumentHandle::new(&h, &ctx)],
454                    &ctx.function_context(None),
455                )
456                .unwrap()
457                .into_literal()
458            };
459
460        // "éz": 'z' is the 2nd CHARACTER (but starts at byte 2 because 'é' is 2 bytes).
461        // Byte-based FIND returned 3; the correct Excel answer is 2.
462        assert_eq!(call(&f, "z", "éz"), LiteralValue::Int(2));
463
464        // SEARCH wildcard scan over an accented haystack. The byte-based loop sliced
465        // &hay[1..] at offset 1 — inside 'é' — and panicked. Must return char position 1.
466        assert_eq!(call(&s, "?z", "éz"), LiteralValue::Int(1));
467
468        // '?' matches exactly one CHARACTER (not one byte) even when that char is multi-byte.
469        assert_eq!(call(&s, "c?fé", "cafés"), LiteralValue::Int(1));
470    }
471}
formualizer_eval/builtins/text/find_search_exact.rs

formualizer_eval/builtins/text/
find_search_exact.rs
