How to loop through CTE and read record

Question

I am using CTE to get records and loop through the CTE and perform some operations on the data.

Here is the table

Table A

Id | Name
---+-----------------------------
1  | Samuel,Jack
2  | Williams,In: Edison,Thomas
3  | Wick,John : Isac, Newton

I am creating a CTE to filter records which has ':' in data.

;WITH cte
( 
    SELECT Name AS filteredNames 
    FROM TableA 
    WHERE Name LIKE '%:%'
)

SELECT * FROM cte will return rows similar to the one below

filteredNames
--------------
 Williams,In: Edison,Thomas
 Wick,John : Isac, Newton

I want to read row by row from the CTE and perform logic to swap the names separated by ',' keeping ':' on the name.

Final output should be like this:

   finalOutput
   --------------
   In,Williams: Thomas,Edison
   John, Wick  :  Newton,Isac

I need to set each row to variable some thing like

DECLARE @tempName nvarchar(500)

SET @tempName = SELECT filteredNames FROM cte

I want to loop through cte and set each row value to @tempName variable and perform logic on the @tempname.

Please let me know how can we read row by row. Is there any chance to do without using a cursor?

If you explain better what `some operations` means, maybe we can see if it can be done in one `update`. It is possible usually to feed procedural logic through `cross apply`s but we need to see your code first — Charlieface, Jul 20 '21 at 00:36
As already mentioned this may be as simple as `UPDATE TableA SET Name = REPLACE(Name,'something','somethingelse') where Name like '%:%'`. No CTE or cursor required. — Nick.Mc, Jul 20 '21 at 00:57
Normalize your schema. See ["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) (Spoiler: Yes, it is.). — sticky bit, Jul 20 '21 at 01:12
Lookup cursors. Though when you "loop" over a set in a relational database you're likely on the wrong path. You should try and find a set based solution. (But again, to get help further with that, you need to severely improve the question.) — sticky bit, Jul 20 '21 at 01:15
@Charlieface ,@sticky bit- Updated the operations which i need to perform. Please suggest an approach you have and let me know how can we do cross apply with the name column — AMDI, Jul 20 '21 at 03:10
you are telling two things: 1. update table 2. set value to variable and work on the name. post them as two different questions — Venkataraman R, Jul 20 '21 at 03:14
@VenkataramanR- removed update part.Please suggest if you have any approach to achieve result or let me know how to lopp through cte — AMDI, Jul 20 '21 at 03:17
So you simply want to reverse the names? You can likely do that without a CTE, variables or cursor. — Nick.Mc, Jul 20 '21 at 04:18
You still haven't explained what you want to do *with the variable* once you have assigned it, what are you *actually* trying to achieve? `perform logic on the @tempname` what logic? Please elaborate and be clear, help us to help you — Charlieface, Jul 20 '21 at 09:40

score 0 · Answer 1 · answered Jul 20 '21 at 04:46

Here is an example of what you can do. Please put some additional test cases in and see if it works for you. If it does I'll come up with an update statement that updates the data

DECLARE 
@Table TABLE (
    ID INT,
    Nme VARCHAR(100)
)

INSERT INTO @Table (ID,Nme)
VALUES 
(1,'Samuel,Jack'),
(2,'Williams,In: Edison,Thomas'),
(3,'Wick,John : Isac, Newton')


-- Shows input and output
SELECT 
ID,
Nme,
LName1 + ',' + FName1 + ':' + LName2 + ',' + FName2 Result
FROM
(
SELECT 
ID,
Nme,
TRIM(LEFT(Nme,FirstComma-1)) FName1,
TRIM(SUBSTRING(Nme,FirstComma+1,Colon-FirstComma-1)) LName1,
TRIM(SUBSTRING(Nme,Colon+1, SecondComma-Colon-1)) FName2,
TRIM(RIGHT(Nme,LEN(Nme)-SecondComma)) LName2
FROM
(
SELECT
CHARINDEX(':',Nme) Colon,
CHARINDEX(',',Nme) FirstComma,
CHARINDEX(',',Nme,CHARINDEX(',',Nme)+1) SecondComma,
*
FROM @Table WHERE Nme LIKE '%:%'
) as A
) as B

-- Actually do the update

@Nick.McDermaid- Thanks for suggestion. Please let me know if the data has more than 2 colon and how can we make it dynamic like it should get colon info based on data fo that specific row and perform the logic. — AMDI, Jul 20 '21 at 05:29
Your test data doesn't have this. Can you edit your question with this info. Note this is a bad design and if you have the opportunity you should fix it properly. — Nick.Mc, Jul 20 '21 at 05:37

How to loop through CTE and read record

1 Answers1