How to split a string in postgresql and return another string if empty-CodePudding

I have one column that I need to split into two based on what is before and after a ':'. If there isn't a ':', I need the second column to read 'None'.

Example:

original_a: Sleepy in America: Why We Are Tired And What To Do About It headline: Sleepy in America subtitle: Why We Are Tired And What To Do About It

original_b: Meditation vs Talking Therapy headline: Meditation vs Talking Therapy subtitle: 'None'

I tried a subquery

SELECT headline, 
       CASE WHEN subtitle = ' ' THEN 'None' ELSE subtitle END as subtitle
FROM ( 
     SELECT split_part(headline, ':', 1) as headline, split_part(headline, ':', 2) as subtitle
     FROM table) as subquery

This splits the original column just fine but won't return 'None' when there isn't a second part.

How do I return 'None'? Thanks

CodePudding user response：

How about a simple COALESCE with a NULLIF which includes empty results?

COALESCE returns the first non-null value in its parameters.

NULLIF will return NULL if the two arguments match.

SELECT 
 SPLIT_PART(headline, ':', 1) AS headline
,COALESCE(NULLIF(SPLIT_PART(headline, ':', 2), ''), 'None') AS subtitle
FROM table

You can see it working HERE