The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Extract substring from string before the first comma

slc1axjslc1axj Vertica Customer

I have a field (called members) that separates names by commas (e.g. Amy, Fred, Susan) - can be several names or just 1 name. I need to grab the first name out of the field and stop at the comma - e.g. Amy - what's the best way to do this in Vertica - I don't have the function Split_Part

Tagged:

Answers

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited December 2021

    A combination of SUBSTR and INSTR might work for you?

    dbadmin=> SELECT c, DECODE(INSTR(c, ','), 0, c, SUBSTR(c, 1, INSTR(c, ',')-1)) first_name FROM t;
                  c                                             | first_name
    -----------------------------------+------------
     Dasher,Prancer,Vixon                   | Dasher
     Comet                                                | Comet
     Cupid,Donner,Blitzen,Rudolph | Cupid
    (3 rows)
    
  • marcothesanemarcothesane - Select Field - Administrator

    Or also: ...

    WITH
    indata(c) AS (
              SELECT 'Dasher,Prancer,Vixon'
    UNION ALL SELECT 'Comet'
    UNION ALL SELECT 'Cupid,Donner,Blitzen,Rudolph'
    )
    SELECT
      c
    , SPLIT_PART(c,',',1) AS first_name
    FROM indata;
    -- out               c               | first_name 
    -- out ------------------------------+------------
    -- out  Dasher,Prancer,Vixon         | Dasher
    -- out  Comet                        | Comet
    -- out  Cupid,Donner,Blitzen,Rudolph | Cupid
    
  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    @slc1axj - To @marcothesane post, are you sure you don't have access to the SPLIT_PART function? It has been a built-in function in Vertica for a very long time. :)

  • marcothesanemarcothesane - Select Field - Administrator

    Indeed, I checked. Sorry I missed your point of not having SPLIT_PART() available for you.
    But you can find it as far back as Version 8.1.

    Can you tell us your Vertica Version?, or just run a SELECT VERSION() , and share your output?

Leave a Comment

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