# Clean up text Shape / columns string functions: trim, case, pad, substring, remove characters, normalise whitespace or unicode Page: ## Status Runs as a SQL step. arcform carries it as a `sql:` step today. ## The DuckDB step trim, fold case and accents, collapse spaces It reads the `orders` table the proofs share. Create it first: ```sql CREATE TABLE orders AS SELECT * FROM (VALUES (1, 'Alice Ng', 'north', DATE '2026-01-03', 120.00, 'widget', 'a|b', '{"channel":"web","items":[1,2]}'), (2, 'alice ng', 'north', DATE '2026-01-05', 80.50, 'gadget', 'b', '{"channel":"shop","items":[3]}'), (3, 'Bob Okafor', 'south', DATE '2026-01-05', 300.00, 'widget', 'a|c', '{"channel":"web","items":[4,5,6]}'), (4, 'Carla Díaz', 'east', DATE '2026-01-09', NULL, 'gizmo', NULL, '{"channel":"web","items":[]}'), (5, 'Bob Okafor', 'south', DATE '2026-01-12', 45.25, 'gadget', 'c', '{"channel":"phone","items":[7]}'), (6, 'Dev Patel', 'west', DATE '2026-01-12', 510.00, 'widget', 'a', '{"channel":"web","items":[8,9]}'), (7, 'Carla Diaz', 'east', DATE '2026-01-20', 99.99, 'gizmo', 'b|c', '{"channel":"shop","items":[10]}'), (8, 'Dev Patel', 'west', DATE '2026-02-02', 510.00, 'widget', 'a', '{"channel":"web","items":[8,9]}'), (9, 'Eve Larsen', NULL, DATE '2026-02-03', 15.00, 'gadget', NULL, '{"channel":"web","items":[11]}'), (10, 'Alice Ng', 'north', DATE '2026-02-10', 220.00, 'gizmo', 'a|b|c', '{"channel":"phone","items":[12,13]}') ) t(order_id, customer, region, order_date, amount, product, tags, meta); ``` Then run the statement: ```sql CREATE TEMP TABLE result AS SELECT customer, regexp_replace(trim(lower(strip_accents(customer))), '\s+', ' ', 'g') AS cleaned FROM orders; SELECT * FROM result; SELECT CASE WHEN ((SELECT count(DISTINCT customer) = 8 AND count(DISTINCT cleaned) = 5 FROM result)) THEN 'as expected' ELSE error('eight spellings did not clean to five names') END AS verdict; ``` It needs nothing installed beyond DuckDB itself. ## Variants - [Change letter case](https://meridian.online/operations/clean-up-text/change-letter-case): Convert text to lower, upper, title or sentence case. - Other text functions: Use a library of text functions, a multi-step cleaner, or a text function no other variant covers. - [Pad text to a fixed length](https://meridian.online/operations/clean-up-text/pad-text-to-a-fixed-length): Add characters to the left or right of text until it reaches a given length. - Standardise phone numbers: Rewrite phone numbers in one standard format, checking that each is valid. - [Remove classes of characters](https://meridian.online/operations/clean-up-text/remove-characters): Remove every letter, digit, punctuation mark, symbol or space from text. - [Make a slug](https://meridian.online/operations/clean-up-text/make-a-slug): Turn text into a lowercase identifier with no spaces or punctuation, fit for a URL or a column name. - [Turn HTML into plain text](https://meridian.online/operations/clean-up-text/strip-html-from-text): Remove HTML tags from text and decode its character entities. - [Take part of a text](https://meridian.online/operations/clean-up-text/take-part-of-a-text): Keep the characters of a text between two positions, or its first or last N characters. - [Transliterate or remove accents](https://meridian.online/operations/clean-up-text/transliterate-or-remove-accents): Convert text to a standard character set, such as removing accents, replacing curly quotes or writing another script in Latin letters. - [Trim and collapse whitespace](https://meridian.online/operations/clean-up-text/trim-whitespace): Remove spaces, line breaks or given characters from the ends of text and collapse runs of whitespace inside it. ## What other tools call it Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2). - Azure Data Factory: [`String functions`](https://learn.microsoft.com/en-us/azure/data-factory/control-flow-expression-language-functions) (read 2026-09-27) - Alteryx Designer: [`Data Cleanse Pro`](https://help.alteryx.com/current/en/designer/tools/preparation/data-cleanse-pro-tool.html) (read 2026-09-27) - Alteryx Designer: [`Data Cleansing`](https://help.alteryx.com/current/en/designer/tools/preparation/data-cleansing-tool.html) (read 2026-09-27) - Bruin: [`bruin.slugify(value)`](https://getbruin.com/docs/bruin/assets/templating/macros.html) (read 2026-09-27) - Apache DataFusion: [`String Functions`](https://datafusion.apache.org/user-guide/sql/scalar_functions.html) (read 2026-09-27) - dbt-utils: [`String functions`](https://docs.getdbt.com/reference/dbt-jinja-functions/cross-database-macros) (read 2026-09-27) - dbt-utils: [`String literal functions`](https://docs.getdbt.com/reference/dbt-jinja-functions/cross-database-macros) (read 2026-09-27) - dbt-utils: [`slugify`](https://raw.githubusercontent.com/dbt-labs/dbt-utils/main/README.md) (read 2026-09-27) - DuckDB: [`Text Functions (family)`](https://duckdb.org/docs/current/sql/functions/text) (read 2026-09-27) - Duckle: [`Case Change`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/website/docs/components.html) (read 2026-09-25) - Duckle: [`Pad String`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/website/docs/components.html) (read 2026-09-25) - Duckle: [`Slug`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/frontend/src/workflow-ui/palette-data.ts) (read 2026-09-25) - Duckle: [`Standardize`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/website/docs/components.html) (read 2026-09-25) - Duckle: [`Strip HTML`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/frontend/src/workflow-ui/palette-data.ts) (read 2026-09-25) - Duckle: [`Trim`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/website/docs/components.html) (read 2026-09-25) - EasyMorph: [`Sanitize text`](https://help.easymorph.com/doku.php?id=transformations:sanitize) (read 2026-09-27) - AWS Glue: [`FormatCase`](https://docs.aws.amazon.com/glue/latest/dg/aws-glue-api-pyspark-transforms-FormatCase.html) (read 2026-09-27) - AWS Glue: [`FormatPhoneNumber`](https://docs.aws.amazon.com/glue/latest/dg/aws-glue-api-pyspark-transforms-FormatPhoneNumber.html) (read 2026-09-27) - Apache Hop: [`HTML 2 Text`](https://hop.apache.org/manual/latest/pipeline/transforms/html2text.html) (read 2026-09-27) - Apache Hop: [`Standardize Phone Number`](https://hop.apache.org/manual/latest/pipeline/transforms/standardizephonenumber.html) (read 2026-09-27) - Apache Hop: [`String operations`](https://hop.apache.org/manual/latest/pipeline/transforms/stringoperations.html) (read 2026-09-27) - Ibis: [`Capitalize`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/strings.py) (read 2026-09-27) - Ibis: [`EndsWith`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/strings.py) (read 2026-09-27) - Ibis: [`FindInSet`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/strings.py) (read 2026-09-27) - Ibis: [`LPad`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/strings.py) (read 2026-09-27) - Ibis: [`LStrip`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/strings.py) (read 2026-09-27) - Ibis: [`Lowercase`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/strings.py) (read 2026-09-27) - Ibis: [`RPad`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/strings.py) (read 2026-09-27) - Ibis: [`RStrip`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/strings.py) (read 2026-09-27) - Ibis: [`Repeat`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/strings.py) (read 2026-09-27) - Ibis: [`Reverse`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/strings.py) (read 2026-09-27) - Ibis: [`StartsWith`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/strings.py) (read 2026-09-27) - Ibis: [`StrRight`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/strings.py) (read 2026-09-27) - Ibis: [`StringAscii`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/strings.py) (read 2026-09-27) - Ibis: [`StringContains`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/strings.py) (read 2026-09-27) - Ibis: [`StringFind`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/strings.py) (read 2026-09-27) - Ibis: [`StringLength`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/strings.py) (read 2026-09-27) - Ibis: [`StringSlice`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/strings.py) (read 2026-09-27) - Ibis: [`Strip`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/strings.py) (read 2026-09-27) - Ibis: [`Substring`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/strings.py) (read 2026-09-27) - Ibis: [`Translate`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/strings.py) (read 2026-09-27) - Ibis: [`Uppercase`](https://github.com/ibis-project/ibis/blob/05d2b293344e86cb41c1a426071734f7e270335a/ibis/expr/operations/strings.py) (read 2026-09-27) - KNIME Analytics Platform: [`Case Converter`](https://api.hub.knime.com/nodes/*Twko6RUVUb0ZNQm7?details=full) (read 2026-09-27) - KNIME Analytics Platform: [`String Cleaner`](https://api.hub.knime.com/nodes/*wsgqS8W_d8XAAxX-?details=full) (read 2026-09-27) - Apache NiFi: [`String Manipulation`](https://nifi.apache.org/nifi-docs/expression-language-guide.html) (read 2026-09-27) - OpenRefine: [`Case conversion`](https://openrefine.org/docs/manual/grelfunctions#case-conversion) (read 2026-09-27) - OpenRefine: [`Collapse consecutive whitespace`](https://openrefine.org/docs/manual/cellediting#collapse-consecutive-whitespace) (read 2026-09-27) - OpenRefine: [`Edit all columns`](https://github.com/OpenRefine/OpenRefine/blob/master/main/webapp/modules/core/scripts/views/data-table/data-table-view.js) (read 2026-09-27) - OpenRefine: [`Replace smart quotes with ASCII`](https://openrefine.org/docs/manual/cellediting#replace-smart-quotes-with-ascii) (read 2026-09-27) - OpenRefine: [`Substring`](https://openrefine.org/docs/manual/grelfunctions#substring) (read 2026-09-27) - OpenRefine: [`To lowercase`](https://openrefine.org/docs/manual/cellediting#case-transforms) (read 2026-09-27) - OpenRefine: [`To titlecase`](https://openrefine.org/docs/manual/cellediting#case-transforms) (read 2026-09-27) - OpenRefine: [`To uppercase`](https://openrefine.org/docs/manual/cellediting#case-transforms) (read 2026-09-27) - OpenRefine: [`Trim leading and trailing whitespace`](https://openrefine.org/docs/manual/cellediting#trim-leading-and-trailing-whitespace) (read 2026-09-27) - OpenRefine: [`Trimming`](https://openrefine.org/docs/manual/grelfunctions#trimming) (read 2026-09-27) - OpenRefine: [`Unescape HTML entities`](https://openrefine.org/docs/manual/cellediting#unescape-html) (read 2026-09-27) - Pentaho Data Integration: [`String Operations`](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview) (read 2026-09-27) - Polars: [`Expr.str: case, trim and pad (group)`](https://docs.pola.rs/api/python/stable/reference/expressions/string.html) (read 2026-09-27) - Polars: [`Expr.str: length and slicing (group)`](https://docs.pola.rs/api/python/stable/reference/expressions/string.html) (read 2026-09-27) - Polars: [`SQL functions: String (group)`](https://docs.pola.rs/api/python/stable/reference/sql/functions/string.html) (read 2026-09-27) - Power Query M: [`Text functions`](https://learn.microsoft.com/en-us/powerquery-m/text-functions) (read 2026-09-27) - RapidMiner Studio: [`Cut`](https://docs.rapidminer.com/latest/studio/operators/blending/values/cut.html) (read 2026-09-27) - RapidMiner Studio: [`Transform Cases`](https://docs.rapidminer.com/latest/studio/operators/extensions/Text%20Processing/transformation/transform_cases.html) (read 2026-09-27) - RapidMiner Studio: [`Trim/Strip`](https://docs.rapidminer.com/latest/studio/operators/blending/values/trim_strip.html) (read 2026-09-27) - RapidMiner Studio: [`Unescape HTML`](https://docs.rapidminer.com/latest/studio/operators/extensions/Web%20Mining/utility/unescape_html_attribute.html) (read 2026-09-27) - RapidMiner Studio: [`Unescape HTML Document`](https://docs.rapidminer.com/latest/studio/operators/extensions/Web%20Mining/html_processing/unescape_html.html) (read 2026-09-27) - Apache SeaTunnel: [`String Functions`](https://seatunnel.apache.org/docs/2.3.13/transforms/sql-functions) (read 2026-09-27) - Tableau Prep: [`Clean`](https://help.tableau.com/current/prep/en-us/prep_clean.htm) (read 2026-09-27) - Tableau Prep: [`Make Lowercase`](https://help.tableau.com/current/prep/en-us/prep_clean.htm) (read 2026-09-27) - Tableau Prep: [`Make Titlecase`](https://help.tableau.com/current/prep/en-us/prep_clean.htm) (read 2026-09-27) - Tableau Prep: [`Make Uppercase`](https://help.tableau.com/current/prep/en-us/prep_clean.htm) (read 2026-09-27) - Tableau Prep: [`Remove All Spaces`](https://help.tableau.com/current/prep/en-us/prep_clean.htm) (read 2026-09-27) - Tableau Prep: [`Remove Extra Spaces`](https://help.tableau.com/current/prep/en-us/prep_clean.htm) (read 2026-09-27) - Tableau Prep: [`Remove Letters`](https://help.tableau.com/current/prep/en-us/prep_clean.htm) (read 2026-09-27) - Tableau Prep: [`Remove Numbers`](https://help.tableau.com/current/prep/en-us/prep_clean.htm) (read 2026-09-27) - Tableau Prep: [`Remove Punctuation`](https://help.tableau.com/current/prep/en-us/prep_clean.htm) (read 2026-09-27) - Tableau Prep: [`String functions (group)`](https://help.tableau.com/current/prep/en-us/prep_function_reference.htm) (read 2026-09-27) - Tableau Prep: [`Trim Spaces`](https://help.tableau.com/current/prep/en-us/prep_clean.htm) (read 2026-09-27) - Talend: [`tJapaneseNumberNormalize`](https://help.qlik.com/talend/en-US/components/8.0/text-standardization/text-standardization-component) (read 2026-09-27) - Talend: [`tJapaneseTransliterate`](https://help.qlik.com/talend/en-US/components/8.0/text-standardization/text-standardization-component) (read 2026-09-27) - Talend: [`tStandardizePhoneNumber`](https://help.qlik.com/talend/en-US/components/8.0/phone-number-standardization/phone-number-standardization-component) (read 2026-09-27) - Talend: [`tTransliterate`](https://help.qlik.com/talend/en-US/components/8.0/text-standardization/text-standardization-component) (read 2026-09-27) - Trifacta: [`Lowercase text`](https://help.alteryx.com/aac/en/trifacta-classic/reference/transformation-reference.html) (read 2026-09-27) - Trifacta: [`Pad with leading`](https://help.alteryx.com/aac/en/trifacta-classic/reference/transformation-reference.html) (read 2026-09-27) - Trifacta: [`Prefix text`](https://help.alteryx.com/aac/en/trifacta-classic/reference/transformation-reference.html) (read 2026-09-27) - Trifacta: [`Propercase text`](https://help.alteryx.com/aac/en/trifacta-classic/reference/transformation-reference.html) (read 2026-09-27) - Trifacta: [`Remove accents in text`](https://help.alteryx.com/aac/en/trifacta-classic/reference/transformation-reference.html) (read 2026-09-27) - Trifacta: [`Remove symbols in text`](https://help.alteryx.com/aac/en/trifacta-classic/reference/transformation-reference.html) (read 2026-09-27) - Trifacta: [`Remove whitespace in text`](https://help.alteryx.com/aac/en/trifacta-classic/reference/transformation-reference.html) (read 2026-09-27) - Trifacta: [`String Functions (group)`](https://help.alteryx.com/aac/en/trifacta-classic/wrangle-language/string-functions.html) (read 2026-09-27) - Trifacta: [`Suffix text`](https://help.alteryx.com/aac/en/trifacta-classic/reference/transformation-reference.html) (read 2026-09-27) - Trifacta: [`Trim quotes`](https://help.alteryx.com/aac/en/trifacta-classic/reference/transformation-reference.html) (read 2026-09-27) - Trifacta: [`Trim whitespace`](https://help.alteryx.com/aac/en/trifacta-classic/reference/transformation-reference.html) (read 2026-09-27) - Trifacta: [`Uppercase text`](https://help.alteryx.com/aac/en/trifacta-classic/reference/transformation-reference.html) (read 2026-09-27)