Extract substring from string before the first comma

slc1axj

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

Jim_Knicely

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)

marcothesane

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_Knicely

@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.

marcothesane

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?