CODEWITHSUNDEEP
sql-serverpostgresqlt-sqlsplitdatabase-migration
NoyRaz
NoyRaz

Reputation: 80

Convert PostgreSQL query to SQL Server query

I need to convert my query 

select 
    unnest(string_to_array(names, ',')) as "Admin Name",
    unnest(string_to_array(phones, ',')) as "Admin Phone",
    unnest(string_to_array(emails, ',')) as "Admin Emails"
from 
    metadata_Table

to an equivalent SQL Server query.

Any suggestions?

Upvotes: 0

Views: 2305

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

You cannot exactly this easily in SL Server. There is no "string_split() with ordinality". And, the ordering is not guaranteed with string_split().

Until Microsoft enhances this functions, my suggestion is a recursive subquery:

with cte as (
      select convert(varchar(max), null) as name, convert(varchar(max), null) as phone, convert(varchar(max), null) as email,
             convert(varchar(max), names + ',') as names_rest,
             convert(varchar(max), phones + ',') as phones_rest,
             convert(varchar(max), emails + ',') as emails_rest,
             0 as lev
      from metadata_Table
      union all
      select left(names_rest, charindex(',', names_rest) - 1),
             left(phones_rest, charindex(',', phones_rest) - 1),
             left(emails_rest, charindex(',', emails_rest) - 1),
             stuff(names_rest, 1, charindex(',', names_rest), ''),
             stuff(phones_rest, 1, charindex(',', phones_rest), ''),
             stuff(emails_rest, 1, charindex(',', emails_rest), ''),
             lev + 1
      from cte
      where emails_rest like '%,%'
     )
select *
from cte
where lev > 0;

Upvotes: 2

Related Questions