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?](https://stackoverflow.com/questions/30778444/what-is-the-charindex-sql-server-equivalent-in-postgresql) — TheMisir, Oct 22 '19 at 09:50

score 1 · Answer 1 · answered Oct 22 '19 at 09:47

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 '19 at 09:47

Tim Biegeleisen

502,043
27
286
360

score 1 · Answer 2 · answered Oct 22 '19 at 14:00

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;

Split string into words using Postgres

2 Answers2

Demo