Skip to main content
Question

Split word with condition

  • June 30, 2020
  • 2 replies
  • 9 views

shashank_p
Forum|alt.badge.img+1

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.

2 replies

marcothesane
Forum|alt.badge.img+1
  • Participating Frequently
  • June 30, 2020

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
Forum|alt.badge.img+1
  • Author
  • Participating Frequently
  • June 30, 2020

It helped! and to identify such data I used REGEXP_COUNT