Calculated field
PROPER([label])
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 array_to_string(list_transform(regexp_extract_all(lower(label),'[a-z0-9]+|[^a-z0-9]+'), x -> upper(left(x,1)) || substr(x,2)), '') AS result FROM input
Runs the example above.
SELECT INITCAP(label) AS result FROM input
Written for this dialect; not run here.
SELECT INITCAP(label) AS result FROM input
Written for this dialect; not run here.
Result
| result |
|---|
| Cedar Cedar |
| Orchid-West |
| Maple-East |
| Willow North |
| Birch-Birch |
| Pine/South |
| Ash |
| null |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- Snowflake INITCAP and BigQuery INITCAP use their own word-delimiter profiles; review delimiters.
Naive SQL
SELECT upper(label) AS result FROM input
result CEDAR CEDAR ORCHID-WEST MAPLE-EAST WILLOW NORTH BIRCH-BIRCH PINE/SOUTH ASH null Matches the calculation
SELECT array_to_string(list_transform(regexp_extract_all(lower(label),'[a-z0-9]+|[^a-z0-9]+'), x -> upper(left(x,1)) || substr(x,2)), '') AS result FROM input
result Cedar Cedar Orchid-West Maple-East Willow North Birch-Birch Pine/South Ash null
Signature
PROPER(string)