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?
2
-
Postgres has a initcap function in the last version postgresql.org/docs/14/functions-string.html search for initcapnbk– nbk2022-06-28 11:40:29 +00:00Commented Jun 28, 2022 at 11:40
-
@nbk, It looks to me that's exactly what OP tried and will capitalize more than just the 1st letter of a string.JvdV– JvdV2022-06-28 12:06:49 +00:00Commented Jun 28, 2022 at 12:06
Add a comment
|
2 Answers
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
Comments
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
