I have searched in the forum but I didn't find any solution related to my problem. I have a table which has ID and links. One Id will have multiple http links. I wanted them to Separate by ID. I am using Sql Server 2014. Please see below for the sample data.

| ID  | Links                          |
|:----|:-----------------------------------
|12   |https://yahoo.com  https://edu.org
|13   |https://hotmail.com
|14   |https://cnn.com  https://www.wikipedia.org/

My Expected output 

| ID  | Links                          |
|:----|:-----------------------------------
|12   |https://yahoo.com
|12   |https://edu.org
|13   |https://hotmail.com
|14   |https://cnn.com
|14   |https://www.wikipedia.org/

Thanks, Naveen

CodePudding user response：

Not sure how many spaces you may have between the URLs, so this has been wrapped to avoid showing blank URLs in the results.

select *
  from (
   select id, value as url
     from my_data
    cross apply string_split(links, ' ')
  )z
where url <> ''

Fiddle here.

CodePudding user response：

Use STRING_SPLIT with CROSS APPLY then remove the empty strings in the WHERE clause.

 SELECT a.ID, 
           value AS Links
    FROM sample a
    CROSS APPLY STRING_SPLIT(a.Links, ' ') b
    WHERE b.value <> ''

Fiddle here.

Result: