Split word with condition

edited June 30 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

  • 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

  • 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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.