SQLite Forum

Split an Extract column
Login

Split an Extract column

(1) By Matias (matiaskpo) on 2021-02-09 21:30:43 [link] [source]

Hello, iam trying to split and extract the phone number FROM this COLUMN:

{1,1,20210206000704,,,,,1772285065,10,(10)4045420,100230,N,1}
{1,1,20210126033937,,,,,1772285065,10,(317)5757554,100236,N,1}
{1,1,20210202030039,,,,,1772285065,10,(325)4092770,100208,N,1}
{1,1,20210202170400,,,,,1772285065,10,(377)4040420,100230,N,1}
{1,1,20210203031334,,,,,1772285065,10,(45)4098070,100208,N,1}

To a new column like:
104045420
3175757554
3254092770
454098070

can you help me?
Thanks!

(2) By John Dennis (jdennis) on 2021-02-10 02:22:20 in reply to 1 [link] [source]

One solution:

select replace(replace(phone_number,'(',''),')','') from your_table

(3) By jake on 2021-02-10 02:49:28 in reply to 1 [source]

If your column value is literally as you have shown, then you could combine John's solution with something like this:

sqlite> SELECT json_extract(replace(replace(replace(your_csv_column, '{', '["'), '}', '"]'), ',', '","'), '$[9]')
   ...>   FROM (SELECT '{1,1,20210206000704,,,,,1772285065,10,(10)4045420,100230,N,1}' your_csv_column
   ...>   UNION ALL SELECT '{1,1,20210126033937,,,,,1772285065,10,(317)5757554,100236,N,1}'
   ...>   UNION ALL SELECT '{1,1,20210202030039,,,,,1772285065,10,(325)4092770,100208,N,1}'
   ...>   UNION ALL SELECT '{1,1,20210202170400,,,,,1772285065,10,(377)4040420,100230,N,1}'
   ...>   UNION ALL SELECT '{1,1,20210203031334,,,,,1772285065,10,(45)4098070,100208,N,1}');
(10)4045420
(317)5757554
(325)4092770
(377)4040420
(45)4098070

(4) By Keith Medcalf (kmedcalf) on 2021-02-10 03:15:04 in reply to 1 [link] [source]

with the_table (this_column)
  as (
      values ('{1,1,20210206000704,,,,,1772285065,10,(10)4045420,100230,N,1}'),
             ('{1,1,20210126033937,,,,,1772285065,10,(317)5757554,100236,N,1}'),
             ('{1,1,20210202030039,,,,,1772285065,10,(325)4092770,100208,N,1}'),
             ('{1,1,20210202170400,,,,,1772285065,10,(377)4040420,100230,N,1}'),
             ('{1,1,20210203031334,,,,,1772285065,10,(45)4098070,100208,N,1}')
     )
select this_column,
       replace(replace(json_extract(replace(replace(replace(this_column, '{', '["'), ',', '","'), '}', '"]'), '$[9]'), '(', ''), ')', '') as new_column
  from the_table
;

(5) By Mark Lawrence (mark) on 2021-02-10 09:03:48 in reply to 1 [link] [source]

Because I find it challenging to keep track of deeply nested function calls I'll offer the following riff based on Keith's solution:

WITH the_table(id,this_column) AS (
    VALUES
        (1, '{1,1,20210206000704,,,,,1772285065,10,(10)4045420,100230,N,1}'),
        (2, '{1,1,20210126033937,,,,,1772285065,10,(317)5757554,100236 ,N,1}'),
        (3, '{1,1,20210202030039,,,,,1772285065,10,(325)4092770,100208 ,N,1}'),
        (4, '{1,1,20210202170400,,,,,1772285065,10,(377)4040420,100230 ,N,1}'),
        (5, '{1,1,20210203031334,,,,,1772285065,10,(45)4098070,100208, N,1}')
  ), front(id,a) AS (
    SELECT id, substr(this_column, instr(this_column, '(') + 1)
    FROM the_table
  ), back(id,b) AS (
    SELECT id, substr(a, 1, instr(a, ',') - 1)
    FROM front
  ), clean(id, c) AS (
    SELECT id, replace(b, ')', '')
    FROM back
  )
SELECT
    the_table.this_column,
    clean.c
FROM clean
INNER JOIN the_table
ON the_table.id = clean.id;

It requires SQLite to perform an extra scan, but it saves me from having to perform multiple scans over the SQL.

(6) By Matias (matiaskpo) on 2021-02-10 12:04:19 in reply to 3 [link] [source]

This works perfect!!

SELECT 
BILLCHARGE_ID,
EXT_ATTR,
json_extract(replace(replace(replace(EXT_ATTR, '{', '["'), '}', '"]'), ',', '","'), '$[9]') AS EXPRESION
FROM BILL_CHARGE WHERE BILLCHARGE_ID=751000000157777658

BILLCHARGE_ID	       EXT_ATTR	                                EXPRESION
751000000157777658	{,,,,,,,,,(19)22595418,100230,,1}	(11)24598458

Thank you all!!

(7) By Matias (matiaskpo) on 2021-02-10 12:21:44 in reply to 5 [link] [source]

Works perfect !!

SELECT 
BILLCHARGE_ID,
EXT_ATTR,
json_extract(replace(replace(replace(EXT_ATTR, '{', '["'), '}', '"]'), ',', '","'), '$[9]') AS EXPRESION,
   replace( substr( substr(EXT_ATTR, instr(EXT_ATTR, '(') + 1), 1, instr( substr(EXT_ATTR, instr(EXT_ATTR, '(') + 1), ',') - 1), ')', '') EXPRESION2
FROM BILL_CHARGE WHERE BILLCHARGE_ID=751000000157777658

BILLCHARGE_ID	EXT_ATTR	EXPRESION	EXPRESION2
751000000157777658	{,,,,,,,,,(11)24598458,100230,,1}	(11)24598458	1124598458


Thanks you all!!