Remova espaços e substitua caracteres por regexp_replace()

Instale o módulo adicional unaccent, que (entre outras coisas) fornece a função de mesmo nome unaccent(). Isto ...

remove acentos (sinais diacríticos) de uma determinada string

Então:

SELECT replace(unaccent(compar), ' ', '')
FROM   tbl;

replace()remove todas as ocorrências de uma sequência de padrões, não apenas a primeira. Nenhuma mágica regexp é necessária aqui. Veja abaixo.

Você UPDATEpoderia ser:

UPDATE tbl
SET    compar = replace(unaccent(compar), ' ', '')
WHERE  compar ~ ' ';  -- ignores strings with accents but no spaces (?!)

violino

Na verdade, quando o atributo contém vários espaços, apenas um é eliminado.

Você pode modificar o comportamento regexp_replace()adicionando 'g' como quarto parâmetro, como:

regexp_replace(compar, ' ', '', 'g')

Ver:

• Regex para remover vários caracteres

Para o caso simples, essa é apenas uma maneira cara de dizer:

replace(compar, ' ', '')

Posso ter ficado um pouco confuso aqui - pensei que a questão era "remover espaços" - acreditei que eram vários espaços que estavam sendo discutidos. De qualquer forma, eu respondi, então aqui está - talvez ajude as pessoas no futuro.

Solução 1 – remova todas as vírgulas, espaços e acentos.

De longe, a maneira mais simples de remover todos os espaços e vírgulas, bem como acentos de uma só vez, é usar a TRANSLATE()função (veja violino aqui ).

SELECT
  TRANSLATE(str, 'áéíóú ,', 'aeiou')
FROM 
  test;

Esta solução tem uma vantagem - às vezes os funcionários/consultores não têm permissão para instalar extensões ou a instância do PostgreSQL pode ser baseada na nuvem e a UNACCENTextensão não está disponível - caso contrário, a solução do @ErwinBrandstetter tem melhor desempenho, mesmo com um duplo REPLACE()para eliminar as vírgulas:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT 
  REPLACE(REPLACE(unaccent(str), ' ', ''), ',', '')
FROM  test;

Na minha máquina doméstica, criei uma testtabela com 10 milhões de registros e a solução do EB foi consistentemente ~ 25% mais rápida que a TRANSLATE()solução e ~ 40% mais rápida que REGEXP_REPLACE()aquela - os resultados em db<>fiddle normalmente estão alinhados com isso, mas às vezes ( talvez devido a um aumento no uso), isso pode mudar. Os perigos do benchmarking em sistemas onde não se está no controle.

Solução onde qualquer sequência de espaços é reduzida a 1.

No entanto, para remover vários espaços - deixando um. Na minha experiência, nunca se deve subestimar o quão estúpidas/preguiçosas/distraídas/entediadas as pessoas podem ser ao inserir texto livre em um aplicativo - por acidente, um telefone é colocado em um teclado e você tem 100 vírgulas em pouco tempo! Certa vez, eu tinha um campo de endereço que dizia (algo como - "Joe encontrará você para um sanduíche mais tarde.")!

Então, o que fiz foi o seguinte (todo o código abaixo está disponível no violino aqui ):

CREATE TABLE test
(
  id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
  str TEXT NULL
);

Preencha-o com algumas strings:

INSERT INTO test (str) VALUES

('A normal string'),     -- No commas, starts with a letter, unaccented
('    first  ,  second   ,     third   '), -- starts with spaces, commas, more spaces, no accents
(',,,  A Náid ,,,   ,,,      ,    a h-Aon    , ,,, a Dó ,,,,   A Trí   ,  ' );

-- the last string has commas, accents and spaces all over the place

Agora, se você não pode e/ou não tem permissão para instalar extensões do PostgreSQL, você pode fazer o seguinte usando a função REPLACE( manual ):

SELECT
  REPLACE
  (
    REPLACE
    (
      REPLACE(str, 'á', 'a'), 
      'í', 'i'
    ), 'ó', 'o' 
  )  -- &c. for all the accents in your languages
  str
FROM 
  test;

Resultado (para a string acentuada):

A Naid ,,,   ,,,      ,    a h-Aon    , ,,, a Do ,,,,   A Tri

Os acentos desapareceram - nem é preciso dizer que isso é propenso a erros e cansativo. É muito melhor usar a UNNACCENTextensão - de qualquer maneira, ela faz parte dos módulos de contribuição - isso é quase "essencial"!

Então o seguinte código é executado:

SELECT
  REGEXP_REPLACE 
  (
    BTRIM(REPLACE(UNACCENT(str), ',', '')), 
    '[ ]{2,}', ' ', 'g'
  ),

  LENGTH(
    REGEXP_REPLACE 
  (
    BTRIM(REPLACE(UNACCENT(str), ',', '')), 
    '[ ]{2,}', ' ', 'g'
  ))
FROM
  test;

A razão pela qual o REGEXP_REPLACE()tem que ser executado é porque pode haver mais de um espaço (pode ser 2, 3 ou mais) em sequência (lembre-se das observações sobre a entrada de texto livre acima). A BTRIM()função remove quaisquer caracteres de espaço antes ou depois das strings - a LENGTH()função é usada apenas para verificar os resultados.

regexp_replace              length
A normal string                 15
first second third              18
A Naid a h-Aon a Do A Tri       25

O padrão regexp explicado:

• [ ]- os colchetes indicam uma classe de caracteres - neste caso, contém apenas o caractere de espaço

• {2,}', ' 'é o quantificador e a substituição - isso significa REGEXP_REPLACE()substituir 2 ou mais caracteres de espaço por um único espaço

• 'g'especifica a opção global - faça isso em toda a string

No violino (não mostrado por questões de brevidade), costumava REPLACE()remover espaços suplementares, mas não funciona corretamente como pode ser visto nas próprias strings (entre as palavras) e nos comprimentos.

Existe outra maneira de fazer isso (um pouco artificial - mas útil para ver algumas das outras funcionalidades do PostgreSQL):

WITH change1 AS 
(
  SELECT
    id,
    REGEXP_SPLIT_TO_TABLE
    (
      REPLACE(UNACCENT(BTRIM(str)), ',', ''), ' '
    ) AS str1
  FROM
    test
 
)
SELECT
  id,
  STRING_AGG(str1, ' ' ORDER BY id),
  LENGTH(STRING_AGG(str1, ' ' ORDER BY id))
FROM change1
WHERE 
  LENGTH(str1) >= 1
GROUP BY id
ORDER BY id;

Resultado - o mesmo.

As duas funções interessantes aqui são REGEXP_SPLIT_TO_TABLE()e STRING_AGG(). Tente desmontar a consulta e ver como tudo se encaixa.

A partir da análise de desempenho que fiz, parece que a primeira consulta é (sem surpresa) mais rápida que a segunda (mais longa) - mas YMMV - teste em seus próprios sistemas e dados. As advertências usuais sobre testar o desempenho em um sistema com apenas 3 registros e sobre o qual você não tem controle em nenhum momento se aplicam aqui.

para o apóstrofo:

select replace (compar,CHR(39),'_') dans my_table ;

funciona