We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Extract substring from string before the first comma — Vertica Forum

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