initcap in postgresql-CodePudding

I need to exchage a part of email before @, i.e. 'MARIE.SUE' from '[email protected]'. Then i need to make first symbol UPPER - 'Marie.sue'. Initcap makes this: 'Marie.Sue'. How can i achieve this?

CodePudding user response：

With split_part and INITCAP

SELECT INITCAP(split_part('[email protected]','@',1)) ||'@' 
|| split_part('[email protected]','@',2)

| ?column?              |
| :-------------------- |
| [email protected] |

SELECT INITCAP(split_part('[email protected],uk','@',1)) ||'@' 
|| split_part('[email protected]','@',2)

| ?column?               |
| :--------------------- |
| [email protected] |

db<>fiddle here

CodePudding user response：

With PostgreSQL 9.6 (or above) try to utilize REGEXP_SPLIT_TO_ARRAY() since a positive lookbehind is supported:

WITH S AS (SELECT REGEXP_SPLIT_TO_ARRAY('[email protected]', '(?<=^.|@)') AS ARR) SELECT CONCAT(UPPER(ARR[1]),LOWER(ARR[2]),ARR[3]) FROM S

See an online demo.

This would:

Uppercase the first character;
Lowercase anything else before '@';
Keep everything intact after '@'.

The pattern (?<=^.|@) means to match any position preceded by the start of the string and any character or by an literal '@'.

Note: In case you don't want the substring from '@'-onwards, try:

WITH S AS (SELECT REGEXP_SPLIT_TO_ARRAY('[email protected]', '(?<=^.)|@.*') AS ARR) SELECT CONCAT(UPPER(ARR[1]),LOWER(ARR[2])) FROM S

Or, without REGEX():

WITH S AS (SELECT LOWER(SPLIT_PART('[email protected]', '@', 1)) AS X) SELECT UPPER(LEFT(X,1))||SUBSTRING(X,2) FROM S

See an online demo