Extract substring from string before the first comma
slc1axj
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:
0
Answers
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@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?