Split word with condition

shashank_p

As mentioned below is my requirement for one column data.

Example 1
original value: agricultural & manufacturing reforms
required value: agricultural reforms & manufacturing reforms

Example 2
original value: manufacturing & construction sectors
required value: manufacturing sectors & construction sectors

Need below answers-
1. First can we identify data of this sort in Vertica.
2. If we identified data of this sort then can we get required value using formula.
3. How can we generalize this by a formula for a particular column data.
Kindly assist and let me know if you have any questions.

marcothesane

I would try REGEXP_REPLACE() as a function:

WITH input(src) AS (
          SELECT 'agricultural & manufacturing reforms'
UNION ALL SELECT 'manufacturing & construction sectors'
)
SELECT
  src
, REGEXP_REPLACE(src,'(\w+) \& (\w+) (\w+)','\1 \3 & \2 \3') AS tgt
FROM input;

src | tgt |
-------------------------------------+---------------------------------------------
agricultural & manufacturing reforms | agricultural reforms & manufacturing reforms
manufacturing & construction sectors | manufacturing sectors & construction sectors

shashank_p

It helped! and to identify such data I used REGEXP_COUNT