CODEWITHSUNDEEP
sqlstringpostgresql
Luffydude
Luffydude

Reputation: 782

How to cut off strings before 4th occurrence of a character in postgresql?

I know in a lot of databases the charindex function can accept getting the character on the third occurrence but the strpos in postgresql doesn't accept it.

Basically I'd need to cut everything after the 4th space (including the last space)

If I have a string like:

FLAT 11, ELMER HOUSE 33-35

How to cut it after the 'HOUSE' to turn it into just:

FLAT 11, ELMER HOUSE

And no, using left won't work because these strings are very variable.

Upvotes: 1

Views: 104

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Here is one method:

select substring(str || '    ' from '^[^ ]* [^ ]* [^ ]*')

This looks for groups of characters (possibly empty) separated by a space. It goes for everything up to the fourth space in str.

The || ' ' is just to be sure that there are four spaces on the string. Otherwise the pattern will return NULL.

Upvotes: 3

Related Questions