2016年5月5日 星期四

PostgreSQL 字串切割

PostgreSQL 要做字串分割,可以使用參考以下方法
建立Function:

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;
$$;

use case:
SELECT unnest(string_to_array('the quick lazy fox', ' '));


Reference:
http://stackoverflow.com/questions/1986491/sql-split-string-by-space-into-table-in-postgresql
http://stackoverflow.com/questions/8584967/split-comma-separated-column-data-into-additional-columns
http://stackoverflow.com/questions/7524445/split-a-column-value-into-two-columns-in-a-select

Other SQL Architecture:
http://sqlperformance.com/2012/07/t-sql-queries/split-strings

沒有留言:

張貼留言

© ERIC RILEY , 自由無須告知轉貼
Background Japanese Sayagata by Olga Libby