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 [link] [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 [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!!