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

Asked

Viewed 437 times

4

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.

3 answers

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.

  • Thanks for your reply. This option is not running on Postgresql version 8. The @Rovann Linhalis version has solved my problem. Thank you very much!

  • This function is available for PG from 8.1.

0


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

  • 1

    Thank you, Rovann! Your solution solved my problem. Unfortunately I can’t vote on the answer yet, but as soon as I can I’ll give her a +1.

  • Blz @Eduardo, arrange =]

-1

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.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.