Efficient iteration means in SQL server, to retrieve data after certain operation on each row

Question

I've a simple table i.e.

ID int,
value varchar(100)

value column consists of delimited strings e.g.

- ID - Value

1 - a,b,c,d
2 - b,e
3 - c
4 - r

I need to retrieve all the values from this after splitting each value based on the delimiter i.e. '.'

I've a user defined function to split the string. What could be the correct/efficient way to do it? i am looking to avoid iterating over each row, and then splitting the string.

@RedDevil a b c d e r - each distinct val from delimited string after splitting the value — Jack Sparrow, Nov 28 '19 at 06:43
What have you tried till now? Please go through[minimal-reproducible-example](https://stackoverflow.com/help/minimal-reproducible-example) and post an attempt. — ankyskywalker, Nov 28 '19 at 06:43
The correct and efficient way to solve this problem is to normalize your wrong database structure - and stop storing delimited strings as column values. For more information, read [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutely yes!** — Zohar Peled, Nov 28 '19 at 06:44
@ZoharPeled Agree, that could be done, but some how if this mess is there so just looking how this could come across. — Jack Sparrow, Nov 28 '19 at 06:55

score 2 · Accepted Answer · edited Nov 28 '19 at 08:07

Using cross apply you can retrieve all the Rows. (Assuming dbo.Split_fun is your split function & value is return column name)

DROP TABLE IF EXISTS #CityList
GO

CREATE TABLE #CityList
(Country VARCHAR(100),
City VARCHAR(5000))
GO

DROP TABLE IF EXISTS #contacts;
CREATE TABLE #contacts (
    id INT PRIMARY KEY IDENTITY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    phones VARCHAR(500)
);

INSERT INTO 
    #contacts(first_name, last_name, phones)
VALUES
    ('John','Doe','(408)-123-3456,(408)-123-3457'),
    ('Jane','Doe','(408)-987-4321,(408)-987-4322,(408)-987-4323');


    SELECT id,
    first_name, 
    last_name,
    value phone
FROM 
    #contacts
    CROSS APPLY dbo.Split_fun(phones, ',');

WOW! this looks to be totally what i expect, any comments on efficiency of CROSS APPLY, i am browsing this though. — Jack Sparrow, Nov 28 '19 at 07:27

score 0 · Answer 2 · answered Nov 28 '19 at 07:45

Please use below query for detail understanding.

USE [Your Database]
--create a table 'xc' to insert the data--
CREATE TABLE xc
(
Id INT,
[value] VARCHAR(100)
)
--Insert the data into table xc--
INSERT INTO dbo.xc
VALUES (1,'a,b,c,d')
(2,'b,e'),(3,'c'),(4,'r')

--Now run Update statement to get records a '.'(dot) delimited
UPDATE dbo.xc SET [value]=Replace([value],',','.')
--Now See the data---
Select * from dbo.xc

Please check snapshot as well.

Efficient iteration means in SQL server, to retrieve data after certain operation on each row

- ID - Value

2 Answers2