Trim Intermediate spaces in a string in Postgres

user_17772358 0
Viewed 69

In the result, want only intermediate spaces to be removed.

Need to print only first part before hypen (-) along with Percentages.

Can you please help.

Input String: AMAZON - 25%; SAP - XXXXX - 45%; MICROSOFT - XXX&YYY - 30%

Query:

SELECT 
translate(left("S_Name",POSITION(',' IN "S_Name")-1),'(,),{,},"','') as FirstPart,
translate(SUBSTRING ("S_Name",length("S_Name") -4 ,4),'(,),{,},"','')as secondpart;
postgresql function sql
2 Answers

regexp_split_to_table can be used to split the value into strings by the delimiter ;, then you can use split_part to get the first and second parts of the desired result.

Select trim(split_part(t,' - ',1)) As First,
       trim(reverse(split_part(reverse(t),' - ',1))) As Second
From regexp_split_to_table('SUCCESS FACTORS - 25%; SAP - XXXXX - 45%; MICROSOFT - XXX&YYY - 30%', ';') As t;

Data Output:

first second
SUCCESS FACTORS 25%
SAP 45%
MICROSOFT 30%
user_117698590

Split the initial string by ; into a 'table' and then use regular expressions to extract the needed parts into columns.

select 
    trim(substring(s from '^([\w ]+)')) "first",
    substring(s from '([\d]+%)$') "second"
from unnest(string_to_array('SUCCESS FACTORS - 25%; SAP - XXXXX - 45%; MICROSOFT - XXX&YYY - 30%', ';')) s;
first second
SUCCESS FACTORS 25%
SAP 45%
MICROSOFT 30%
user_23020320
Related