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        if start > hay.len() {
124            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
125                ExcelError::new_value(),
126            )));
127        }
128        if let Some(pos) = hay[start..].find(&needle) {
129            Ok(crate::traits::CalcValue::Scalar(LiteralValue::Int(
130                (start + pos + 1) as i64,
131            )))
132        } else {
133            Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
134                ExcelError::new_value(),
135            )))
136        }
137    }
138}
139
140// SEARCH(find_text, within_text, [start_num]) - case insensitive + simple wildcard * ?
141#[derive(Debug)]
142pub struct SearchFn;
143/// Returns the 1-based position of one text string inside another.
144///
145/// `SEARCH` is case-insensitive and supports `*` and `?` wildcards.
146///
147/// # Remarks
148/// - Search is case-insensitive.
149/// - `*` matches any sequence and `?` matches a single character.
150/// - `start_num` is 1-based and must be greater than `0`.
151/// - If no match is found, returns `#VALUE!`.
152///
153/// # Examples
154///
155/// ```yaml,sandbox
156/// title: "Case-insensitive search"
157/// formula: '=SEARCH("world", "Hello World")'
158/// expected: 7
159/// ```
160///
161/// ```yaml,sandbox
162/// title: "Wildcard pattern"
163/// formula: '=SEARCH("d?ta*", "Meta Data Lake")'
164/// expected: 6
165/// ```
166///
167/// ```yaml,docs
168/// related:
169///   - FIND
170///   - EXACT
171///   - SUBSTITUTE
172/// faq:
173///   - q: "How are case and wildcards handled?"
174///     a: "SEARCH is case-insensitive and supports * for any sequence plus ? for one character."
175/// ```
176/// [formualizer-docgen:schema:start]
177/// Name: SEARCH
178/// Type: SearchFn
179/// Min args: 2
180/// Max args: variadic
181/// Variadic: true
182/// Signature: SEARCH(arg1...: any@scalar)
183/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
184/// Caps: PURE
185/// [formualizer-docgen:schema:end]
186impl Function for SearchFn {
187    func_caps!(PURE);
188    fn name(&self) -> &'static str {
189        "SEARCH"
190    }
191    fn min_args(&self) -> usize {
192        2
193    }
194    fn variadic(&self) -> bool {
195        true
196    }
197    fn arg_schema(&self) -> &'static [ArgSchema] {
198        &ARG_ANY_ONE[..]
199    }
200    fn eval<'a, 'b, 'c>(
201        &self,
202        args: &'c [ArgumentHandle<'a, 'b>],
203        _: &dyn FunctionContext<'b>,
204    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
205        if args.len() < 2 || args.len() > 3 {
206            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
207                ExcelError::new_value(),
208            )));
209        }
210        let needle = to_text(&args[0])?.to_ascii_lowercase();
211        let hay_raw = to_text(&args[1])?;
212        let hay = hay_raw.to_ascii_lowercase();
213        let start = if args.len() == 3 {
214            let n = number_like(&args[2])?;
215            if n < 1 {
216                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
217                    ExcelError::new_value(),
218                )));
219            }
220            (n - 1) as usize
221        } else {
222            0
223        };
224        if needle.is_empty() {
225            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Int(1)));
226        }
227        if start > hay.len() {
228            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
229                ExcelError::new_value(),
230            )));
231        }
232        // Convert wildcard to regex-like simple pattern
233        // We'll implement manual scanning.
234        let is_wild = needle.contains('*') || needle.contains('?');
235        if !is_wild {
236            if let Some(pos) = hay[start..].find(&needle) {
237                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Int(
238                    (start + pos + 1) as i64,
239                )));
240            } else {
241                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
242                    ExcelError::new_value(),
243                )));
244            }
245        }
246        // Wildcard scan
247        for offset in start..=hay.len() {
248            if wildcard_match(&needle, &hay[offset..]) {
249                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Int(
250                    (offset + 1) as i64,
251                )));
252            }
253        }
254        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
255            ExcelError::from_error_string("#VALUE!"),
256        )))
257    }
258}
259
260fn wildcard_match(pat: &str, text: &str) -> bool {
261    fn rec(p: &[u8], t: &[u8]) -> bool {
262        if p.is_empty() {
263            return true;
264        }
265        match p[0] {
266            b'*' => {
267                for i in 0..=t.len() {
268                    if rec(&p[1..], &t[i..]) {
269                        return true;
270                    }
271                }
272                false
273            }
274            b'?' => {
275                if t.is_empty() {
276                    false
277                } else {
278                    rec(&p[1..], &t[1..])
279                }
280            }
281            c => {
282                if !t.is_empty() && t[0] == c {
283                    rec(&p[1..], &t[1..])
284                } else {
285                    false
286                }
287            }
288        }
289    }
290    rec(pat.as_bytes(), text.as_bytes())
291}
292
293// EXACT(text1,text2)
294#[derive(Debug)]
295pub struct ExactFn;
296/// Compares two text values for exact equality.
297///
298/// # Remarks
299/// - Comparison is case-sensitive.
300/// - No wildcard semantics are applied.
301/// - Non-text values are converted to text before comparison.
302/// - Errors in either argument are propagated.
303///
304/// # Examples
305///
306/// ```yaml,sandbox
307/// title: "Exact same text"
308/// formula: '=EXACT("Form", "Form")'
309/// expected: true
310/// ```
311///
312/// ```yaml,sandbox
313/// title: "Case difference is not equal"
314/// formula: '=EXACT("Form", "form")'
315/// expected: false
316/// ```
317///
318/// ```yaml,docs
319/// related:
320///   - FIND
321///   - SEARCH
322///   - UPPER
323/// faq:
324///   - q: "Does EXACT perform case-sensitive comparison?"
325///     a: "Yes. EXACT compares the resulting text values with exact case and character equality."
326/// ```
327/// [formualizer-docgen:schema:start]
328/// Name: EXACT
329/// Type: ExactFn
330/// Min args: 2
331/// Max args: 1
332/// Variadic: false
333/// Signature: EXACT(arg1: any@scalar)
334/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
335/// Caps: PURE
336/// [formualizer-docgen:schema:end]
337impl Function for ExactFn {
338    func_caps!(PURE);
339    fn name(&self) -> &'static str {
340        "EXACT"
341    }
342    fn min_args(&self) -> usize {
343        2
344    }
345    fn arg_schema(&self) -> &'static [ArgSchema] {
346        &ARG_ANY_ONE[..]
347    }
348    fn eval<'a, 'b, 'c>(
349        &self,
350        args: &'c [ArgumentHandle<'a, 'b>],
351        _: &dyn FunctionContext<'b>,
352    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
353        let a = to_text(&args[0])?;
354        let b = to_text(&args[1])?;
355        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Boolean(
356            a == b,
357        )))
358    }
359}
360
361fn number_like<'a, 'b>(a: &ArgumentHandle<'a, 'b>) -> Result<i64, ExcelError> {
362    let v = scalar_like_value(a)?;
363    Ok(match v {
364        LiteralValue::Int(i) => i,
365        LiteralValue::Number(f) => f as i64,
366        LiteralValue::Text(t) => t.parse::<i64>().unwrap_or(0),
367        LiteralValue::Boolean(b) => {
368            if b {
369                1
370            } else {
371                0
372            }
373        }
374        LiteralValue::Empty => 0,
375        LiteralValue::Error(e) => return Err(e),
376        other => other.to_string().parse::<i64>().unwrap_or(0),
377    })
378}
379
380pub fn register_builtins() {
381    use std::sync::Arc;
382    crate::function_registry::register_function(Arc::new(FindFn));
383    crate::function_registry::register_function(Arc::new(SearchFn));
384    crate::function_registry::register_function(Arc::new(ExactFn));
385}
386
387#[cfg(test)]
388mod tests {
389    use super::*;
390    use crate::test_workbook::TestWorkbook;
391    use crate::traits::ArgumentHandle;
392    use formualizer_common::LiteralValue;
393    use formualizer_parse::parser::{ASTNode, ASTNodeType};
394    fn lit(v: LiteralValue) -> ASTNode {
395        ASTNode::new(ASTNodeType::Literal(v), None)
396    }
397    #[test]
398    fn find_search() {
399        let wb = TestWorkbook::new()
400            .with_function(std::sync::Arc::new(FindFn))
401            .with_function(std::sync::Arc::new(SearchFn));
402        let ctx = wb.interpreter();
403        let f = ctx.context.get_function("", "FIND").unwrap();
404        let s = ctx.context.get_function("", "SEARCH").unwrap();
405        let hay = lit(LiteralValue::Text("Hello World".into()));
406        let needle = lit(LiteralValue::Text("World".into()));
407        assert_eq!(
408            f.dispatch(
409                &[
410                    ArgumentHandle::new(&needle, &ctx),
411                    ArgumentHandle::new(&hay, &ctx)
412                ],
413                &ctx.function_context(None)
414            )
415            .unwrap()
416            .into_literal(),
417            LiteralValue::Int(7)
418        );
419        let needle2 = lit(LiteralValue::Text("world".into()));
420        assert_eq!(
421            s.dispatch(
422                &[
423                    ArgumentHandle::new(&needle2, &ctx),
424                    ArgumentHandle::new(&hay, &ctx)
425                ],
426                &ctx.function_context(None)
427            )
428            .unwrap()
429            .into_literal(),
430            LiteralValue::Int(7)
431        );
432    }
433}
formualizer_eval/builtins/text/find_search_exact.rs

formualizer_eval/builtins/text/
find_search_exact.rs
