JLCEL Course

PW01-L07 · Logic change

Character position in text

Account for different index bases when replacing CHARINDEX.

ProjectWise-specific
Migration outcome

Avoid the common off-by-one migration error.

ProjectWise context

General expression in ProjectWise CEL

SQL functionLegacy ProjectWise configuration
CHARINDEX('C', 'ABC')
CELProjectWise CEL
'ABC'.indexOf('C') + 1

What is different

SQL CHARINDEX uses a one-based position in this mapping. CEL indexOf() uses a zero-based index, so the documented equivalent adds 1.

  1. The SQL result for C is 3; CEL indexOf() alone returns 2.
  2. Add 1 only when downstream logic expects SQL-style numbering.
  3. indexOf() is a ProjectWise CEL extension, not a portable guarantee of every CEL host.

Migration goal

Preserve the numeric result of existing logic based on CHARINDEX.

What changes

ProjectWise CEL indexOf() counts from zero. The official migration example adds 1 to preserve the SQL result.

Why the CEL solution is clearer

The offset is visible. If new logic consumes the result as a CEL index, adding 1 may be unnecessary; the downstream use decides.

Before deployment

Test a character at the start, middle, end, and a missing character. Do not automatically pass a SQL-style position into another CEL function.

Sources

  • BENTLEY-PW-CEL-SQL-FUNCTIONS — CHARINDEX mapping.
  • BENTLEY-PW-CEL-STRINGS — indexOf() reference.