0

I'm trying to split a column into multiple columns by a delimiter and recombine the results from last column to first using SQL. I'm looking for a more succinct/efficient version of the enclosed attempt (as in drop the intermediate steps, "part_1" through "part_5", if possible) because I have 100m rows of data. Thanks!

create table my_table (
  domain      text
);

insert into my_table (domain)
values
  ('com.google'),
  ('com.com.google'),
  ('com.com.com.google'),
  ('com.com.com.com.google');

select
  domain,
  split_part(domain, '.', 1) as part_1,
  split_part(domain, '.', 2) as part_2,
  split_part(domain, '.', 3) as part_3,
  split_part(domain, '.', 4) as part_4,
  split_part(domain, '.', 5) as part_5,
  trim(concat(part_5, '.', part_4, '.', part_3, '.', part_2, '.', part_1), '.') as domain_2
from
  my_table;
edpcoughlan
  • 11
  • 2
  • does `STRTOK_TO_ARRAY( [,])` do what you are looking for? –  Mar 22 '22 at 11:18
  • Does this slows down the query performance? Could you please provide an evidence to support it? To me, it looks like a valid solution to what you're trying to achieve. – Clark Perucho Mar 22 '22 at 11:44
  • Something like this would work, but you will have to check on order or perhaps use reverse in someway in case there are distinct values of string in each part. SNOWFLAKE1#COMPUTE_WH@(no database).(no schema)>select listagg(value,'.') within group (order by value desc) as part fr om table(split_to_table('com.com.com.com.google','.')); +------------------------+ | PART | |------------------------| | google.com.com.com.com | +------------------------+ – Pankaj Mar 22 '22 at 12:44
  • You're right - performance didn't take as long as I expected. I just thought there might be a more obvious and elegant way to code the above. Thank you for the suggestions. – edpcoughlan Mar 22 '22 at 12:52
  • You can shorten your `concat` to `concat_ws(',', part_5, part_4, part_3, part_2, part_1)`.But what you have is the best way to go for your problem – Radagast Mar 22 '22 at 16:10

0 Answers0