Calculated field
CONTAINS([label], 'WEST')
Sample data
| label | quantity | amount | flag |
|---|---|---|---|
| cedar cedar | 65 | 12.8 | True |
| ORCHID-west | 66 | -7.6 | False |
| maple-east | 67 | 0 | null |
| willow north | 68 | 25.3 | True |
| birch-birch | 69 | 4.9 | False |
| pine/south | 70 | -1.2 | null |
| ash | 71 | 9 | True |
| null | null | null | null |
The same calculation as SQL
SELECT contains(label,'WEST') AS result FROM input
Runs the example above.
SELECT contains(label,'WEST') AS result FROM input
Written for this dialect; not run here.
SELECT STRPOS(label,'WEST') > 0 AS result FROM input
Written for this dialect; not run here.
Result
| result |
|---|
| False |
| False |
| False |
| False |
| False |
| False |
| False |
| null |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- Replacing CONTAINS with contains(lower(label),'west') changes the shown values or null handling.
Naive SQL
SELECT contains(lower(label),'west') AS result FROM input
result False True False False False False False null Matches the calculation
SELECT contains(label,'WEST') AS result FROM input
result False False False False False False False null
Signature
CONTAINS(string, string)