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;
CodePudding user response:
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% |
CodePudding user response:
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% |