Postgresql 8 - Replace the first occurrence of a character in a string

Question

I have a field in a table in a Postgresql 8 database whose values are strings and have hyphens in some irregular positions.

I would like to replace the first occurrence of the hyphen in the string with a blank space, but I could not find anything appropriate in the documentation.

The functions I found as REPLACE() replace all occurrences and not just the first as I would wish.

Currently I am exporting the results . CSV and opening in MS Excel. With function =SUBSTITUIR(A1;"-";" ";1), I can do it. But I try to be more efficient and spend time doing it.

I appreciate the community’s help in advance.

Answer 1

Directly in SQL for POSTGRESQL 8.1+

Use the function REGEXP_REPLACE

Example for your case

SELECT REGEXP_REPLACE('ALGUMA COISA - OUTRA COISA', '^([^-]*)-(.*?)', E'\\2|\\1', 'g');
-- Retorna  OUTRA COISA|ALGUMA COISA 
SELECT REGEXP_REPLACE('ALGUMA COISA - OUTRA COISA - MAIS OUTRA COISA', '^([^-]*)-(.*?)', E'\\1 \\2', 'g');
-- Retorna

For earlier versions it is possible to use overlay or substring as the answer accepted.

Answer 2

You can use the function Position to find the first occurrence of the hyphen in your string. After that, you can replace it with space, using the function Overlay:

select overlay('Palavra-Teste-X' placing ' ' from position('-' in 'Palavra-Teste-X') for 1)

Upshot: Palavra Teste-X

I put in the Sqlfiddle

Documentation: String Functions and Operators

Answer 3

Version 8.0 was no longer supported in October/2010. Upgrade your installation to a newer version. The current version is 10.3 which will receive support until October/2022.