Split string into words using Postgres

Question

I am looking for some help in separating scientific names in my data. I want to take only the genus names and group them, but they are both connected in the same column. I saw the SQL Sever had a CHARINDEX command, but PostgreSQL does not. Does there need to be a function created for this? If so, how would it look?

I want to change 'Mallotus philippensis' to just 'Mallotus' or to just 'philippensis'

I am currently using Postgres 11, 12.

Possible duplicate of What is the CHARINDEX (SQL SERVER) equivalent in POSTGRESQL? — TheMisir
– TheMisir, Commented Oct 22, 2019 at 9:50

Ivan Mogila · Accepted Answer · 2019-10-22 14:00:02Z

3

Probably string_to_array will be slightly more efficient than split_part here because string splitting will be done only once for each row.

SELECT
  val_arr[1] AS genus,
  val_arr[2] AS species
FROM (
  SELECT string_to_array(val, ' ') as val_arr
  FROM (
    VALUES
      ('aaa bbb'),
      ('cc dddd'),
      ('e fffff')
  ) t (val)
) tt;

answered Oct 22, 2019 at 14:00

Ivan Mogila

4672 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2019-10-22 09:47:56Z

1

Use SPLIT_PART:

WITH yourTable AS (
    SELECT 'Mallotus philippensis'::text AS genus
)

SELECT
    SPLIT_PART(genus, ' ', 1) AS genus,
    SPLIT_PART(genus, ' ', 2) AS species
FROM yourTable;

Demo

answered Oct 22, 2019 at 9:47

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Collectives™ on Stack Overflow

Split string into words using Postgres

2 Answers 2

Comments

Demo

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related