topic Re: Spark SQL INITCAP not capitalizing letters after periods in abbreviations in Data Engineering

Spark SQL INITCAP not capitalizing letters after periods in abbreviations

dkhodyriev1208 — Tue, 25 Nov 2025 09:19:14 GMT

Using SELECT INITCAP("text (e.g., text, text, etc.)") abbreviations with periods like e.g. are not being fully capitalized.

Current behavior:

Input:  "text (e.g., text, text, etc.)"Output: "Text (e.g., Text, Text, Etc.)"

Expected behavior:

Output: "Text (E.G., Text, Text, Etc.)"

Version:

16.4.x-scala2.12

Re: Spark SQL INITCAP not capitalizing letters after periods in abbreviations

Coffee77 — Tue, 25 Nov 2025 10:28:15 GMT

Try something like this:

Re: Spark SQL INITCAP not capitalizing letters after periods in abbreviations

dkhodyriev1208 — Tue, 25 Nov 2025 10:48:06 GMT

Thanks for suggestion.

Good option, but few concerns here: there is no space in my original example for e.g. so it would require more sophisticated regex or custom udf.

I think that the root cause of the issue is some limitation of initcap function, that treats only spaces as delimiter. Also, I've tried such query in EKS cluster, and it works as expected, so this is rather some limitation of Databricks Spark version

Re: Spark SQL INITCAP not capitalizing letters after periods in abbreviations

Coffee77 — Tue, 25 Nov 2025 10:55:12 GMT

My solution is indeed a workaround. INITCAP is behaving as you comment. You can include another regular expression at the beginning to remove non-original "spaces" but I agree that makes it a little complex. However, no other solution so far I'm aware of

Re: Spark SQL INITCAP not capitalizing letters after periods in abbreviations

iyashk-DB — Wed, 26 Nov 2025 18:06:20 GMT

Yes similar to what @Coffee77 has told, you can alternatively create an SQL function and use it directly with the custom logic using the regexp:

CREATE OR REPLACE FUNCTION PROPER_WITH_ABBREVIATIONS(input STRING)
RETURNS STRING
RETURN regexp_replace(
INITCAP(input),
'(?i)(?<!\\d)([a-z])\\.(?!\\d)',
upper('$1') || '.'
);