Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2012
    Posts
    6

    Unanswered: Need this Oracle logic converted to SQL Server

    Hi all,
    I have written following query in Oracle.
    Here is the code:
    Code:
    (SELECT replace(REGEXP_SUBSTR (domainList001, '[^,]+', 1, LEVEL),'''','' ) FROM DUAL CONNECT BY REGEXP_SUBSTR (domainList001, '[^,]+', 1, LEVEL) IS NOT NULL)


    Here domainList001 is a string like 'domain1,domain2,domain3,domain4'
    and the above code breaks individual elements and I get following output:
    domain1
    domain2
    domain3
    domain4
    i.e one row for each element.

    Now I have to port it to SQL Server.

    Any help is appreciated.


    Thanks,
    Raghvendra

  2. #2
    Join Date
    Sep 2011
    Location
    Greenville, SC USA
    Posts
    34

    RE: Need this Oracle logic converted to SQL Server

    A possible solution for shorter lists would be...

    Code:
    select d.value('.[1]','varchar(32)') as domain
    from (select convert(xml,'<d>'+replace(@domainList001,',','</d><d>')+'</d>') as dx) list
    cross apply list.dx.nodes('/d') X(D)
    where rtrim(d.value('.[1]','varchar(32)')) != ''
    For a much broader solution, consider the following from Jeff Moden:
    Tally OH! An Improved SQL 8K
    Last edited by homerow; 09-03-12 at 10:39.

  3. #3
    Join Date
    Aug 2008
    Posts
    147
    You'll have to use some sort of array parser - on the SELECT statement. type "sql parse comma delimited list" into google
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •