Split word with condition

shashank_pshashank_p Vertica Customer
edited June 2020 in General Discussion

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.

Tagged:

Answers

  • marcothesanemarcothesane - Select Field - Administrator

    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_pshashank_p Vertica Customer

    It helped! and to identify such data I used REGEXP_COUNT

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file