Convert PostgreSQL query to SQL Server query

Question

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?

Storing name/phones/emails as comma separated values violates 1NF(atomicity). I would consider refactoring the table. Anyway STRING_SPLIT may be helpful — Lukasz Szozda
– Lukasz Szozda, Commented Nov 10, 2019 at 10:10

Gordon Linoff · Accepted Answer · 2019-11-10 13:28:03Z

2

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;

edited Nov 10, 2019 at 13:28

answered Nov 10, 2019 at 12:00

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

NoyRaz Over a year ago

Msg 402, Level 16, State 1, Line 31 The data types text and varchar are incompatible in the add operator. this is what I get

SMor Over a year ago

@noyraz No one, including you, should be using the text datatype at this late date. Change your schema now, before you get so far that this change requires even more work.

Collectives™ on Stack Overflow

Convert PostgreSQL query to SQL Server query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related