2

I have sene Split words with a capital letter in sql which is applicable to MS SQL but I was wondering how to achieve the same using PostgreSQL

Basically I'm getting values such as FirstNameValue but I need it to be First Name Value

Unfortunately I don't know where to even start. I got to the following and got stuck straight away

SELECT REGEXP_REPLACE('ThoMasTest', '[^ ][A-Z].', ' ')

The result from a string such as ThoMasTest should be Tho Mas Test

Thanks

Community
  • 1
  • 1
pee2pee
  • 3,619
  • 7
  • 52
  • 133

1 Answers1

9

This should do the trick:

select regexp_replace('ThoMasTest', '([a-z])([A-Z])', '\1 \2','g');

The expression matches two characters next to eachother, each one in its own group:

  1. [a-z] that matches a lowercase letter.
  2. [A-Z] finds a capital letter

So if one lowercase if immediately followed by a capital letter insert a space between them.

Do that globally 'g'.

UlfR
  • 4,175
  • 29
  • 45
  • I get the following result "Th a est" but the result features an odd character in the position of the space... – pee2pee Nov 10 '14 at 11:50
  • 1
    The 3:rd argument needs to be `\1 \2` not `' '`! Or if you have that, put in one extra backslash to make it `'\\1 \\2'`, that could solve it if you are using some strange terminal. – UlfR Nov 10 '14 at 11:55
  • select regexp_replace('JanuszJasinski', '([a-z])([A-Z])', '\\1 \\2','g'); worked – pee2pee Nov 10 '14 at 11:55