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

Navigation
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 space, but I could not find anything appropriate in documentation .

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

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

Thanks for the community's help in advance.

    
asked by anonymous 20.04.2018 / 17:47

3 answers

0

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

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

Result: Palavra Teste-X

I put it in SQLFiddle

Documentation: String Functions and Operators

    
20.04.2018 / 18:12
1

Directly in SQL for POSTGRESQL 8.1 +

Use the 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 previous versions it is possible to use overlay or substring according to the accepted response.

    
20.04.2018 / 18:24
-1

Version 8.0 is no longer supported in October / 2010. Please upgrade your installation to a newer version. The current version is 10.3 which will be supported by October / 2022.

    
24.04.2018 / 20:02
Returning content from a form via cURL Christofides Algorithm - TSP, Problem in transforming AGM into a graph with all vertices of even degree