SQL Split String by Space into Table in Postgresql

sql split string by space into table in postgresql

You can split an array to a resultset by using the unnest function, and you can turn a string literal into an array by using the string_to_array function. Combine both and you get this:

alvherre=# select unnest(string_to_array('the quick lazy fox', ' '));
 unnest 
--------
 the
 quick
 lazy
 fox
(4 filas)

Since 8.2 does not have UNNEST, you can write it in PostgreSQL like this:

create or replace function unnest(anyarray) returns setof anyelement
language sql as $$
   select $1[i] from generate_series(array_lower($1, 1),
                                     array_upper($1, 1)) as i;
$$; 

How can I split a value with a space from into two columns in SQL?

UPDATE
    Sometable
SET
    Firstname = REGEXP_REPLACE(' [^ ]*$', '', Fullname),
    Lastname = REGEXP_REPLACE('.* ', '', Fullname)

Be very careful when pasting code above not to mess up the spaces in regexps

postgresql 9.5: trying to split a string into two fields based on spaces

split_part() doesn't support regular expressions. You can only specify a "simple" string as the delimiter.

To split on a regular expression you need regexp_split_to_array()

UPDATE intersection_table_wi 
   SET wv002 = (regexp_split_to_array(BTRIM(whhid), '\s+'))[1], 
       wv003 = (regexp_split_to_array(BTRIM(whhid), '\s+'))[2];

Postgres SQL - Split string at Deliminator and replace with substring

There is an obvious match on column facebook_campaigns(campaign), but I assumed this is not what you expect.

To join both tables, it looks like it would be sufficient to remove the spaces from the order_line_items table, surround the value with hyphens (-), and then do pattern matching against column ad_name in facebook_campaigns:

select ...
from facebook_campaigns fc
inner join order_line_items oli
    on fc.ad_name like '%-' || replace(oli.product_name, ' ', '') || '-%'

How to split the row by a comma and unpivot the table in PostgreSQL?

you can use string_to_array() and unnest()

SELECT t.birthday_id , 
       t.child_birthday_id,
       p.place
FROM sh.test t
  CROSS JOIN LATERAL unnest(string_to_array(place, ',')) as p(place);

If you are using Postgres 14 you can also use string_to_table(t.place, ',')

Split column into multiple rows in Postgres

In Postgres 9.3+ use a LATERAL join. Minimal form:

SELECT token, flag
FROM   tbl, unnest(string_to_array(subject, ' ')) token
WHERE  flag = 2;

The comma in the FROM list is (almost) equivalent to CROSS JOIN, LATERAL is automatically assumed for set-returning functions (SRF) in the FROM list. Why "almost"? See:

• "invalid reference to FROM-clause entry for table" in Postgres query

The alias "token" for the derived table is also assumed as column alias for a single anonymous column, and we assumed distinct column names across the query. Equivalent, more verbose and less error-prone:

SELECT s.token, t.flag
FROM   tbl t
CROSS  JOIN LATERAL unnest(string_to_array(subject, ' ')) AS s(token)
WHERE  t.flag = 2;

Or move the SRF to the SELECT list, which is allowed in Postgres (but not in standard SQL), to the same effect:

SELECT unnest(string_to_array(subject, ' ')) AS token, flag
FROM   tbl
WHERE  flag = 2;

The last one seems acceptable since SRF in the SELECT list have been sanitized in Postgres 10. See:

• What is the expected behaviour for multiple set-returning functions in SELECT clause?

If unnest() does not return any rows (empty or NULL subject), the (implicit) join eliminates the row from the result. Use LEFT JOIN ... ON true to keep qualifying rows from tbl. See:

• What is the difference between LATERAL JOIN and a subquery in PostgreSQL?

We could also use regexp_split_to_table(), but that's typically slower because regular expressions cost a bit more. See:

• SQL select rows containing substring in text field
• PostgreSQL unnest() with element number

How to split a string and select nth part

Use split_part():

with my_table(str) as (
    values 
    ('asdsc:dcdes:dcd'),
    ('dcfd:drfe:regf')
)

select 
    str, 
    split_part(str, ':', 2) as part_no_2, 
    split_part(str, ':', 3) as part_no_3
from my_table

       str       | part_no_2 | part_no_3 
-----------------+-----------+-----------
 asdsc:dcdes:dcd | dcdes     | dcd
 dcfd:drfe:regf  | drfe      | regf
(2 rows)    

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

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

---

Related Topics