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

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 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)
    
  • 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 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. :)

  • 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

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