TSQL, how do you repeat rows while parsing them?

Question

Sorry for the incorrect wording of the question. I was not sure how to describe it. I want to iterate over each row of the table and at the same time retrieve the column, analyze the varchar located in it and depending on what it finds, inserts the rows into another table. Something like that:

DECLARE @string varchar(max); foreach row in (select * from Table) { set @string = row[column]; while (len(@string) > 0) { -- Do all the parsing in here if (found what was looking for) insert into Table2 values(row[column2], row[column3]); } } 

It would be very nice if it were a stored procedure, so you need to do this in SQL. I'm just not too sure how to approach him. Thanks.

Edit:

This is basically the functionality I was hoping for:

 Table 1 | id_number | text | 1 Hello, test 532. Yay oh and test 111 2 test 932. 3 This is a test 315 of stuff test 555. 4 haflksdhfal test 311 sadjhfalsd 5 Yay. 

I want to go through this table and analyze all the text columns to look for instances of "test #", where # is a number. When he finds something inside the text in this format, he inserts this value into another table, for example:

 Table 2 | id_number | number 1 532 1 111 2 932 3 315 3 555 4 311 

Answer 1

In SQL Server 2008 you can do it

 WITH testTable AS ( SELECT 1 AS id_number, N'Hello, test 532. Yay oh and test 111' AS txt UNION ALL SELECT 2, N'test 932.' UNION ALL SELECT 3, N'This is a test 315 of stuff test 555.' UNION ALL SELECT 4, N'haflksdhfal test 311 sadjhfalsd' UNION ALL SELECT 5, N'Yay.' ) SELECT id_number,display_term FROM testTable CROSS APPLY sys.dm_fts_parser('"' + REPLACE(txt,'"','""') + '"', 1033, 0,0) WHERE TXT IS NOT NULL and display_term NOT LIKE '%[^0-9]%' /*Or use LIKE '[0-9][0-9][0-9]' to only get 3 digit numbers*/ 

Returns

 id_number display_term ----------- ------------------------------ 1 532 1 111 2 932 3 315 3 555 4 311 

Answer 2

You are thinking of a procedure, not a set. You can probably write all this as a single query:

 INSERT INTO target_table (column list) SELECT (column list) FROM source_table WHERE (parse your column) = (some criterion) 

Much easier to write and probably much faster.

If your parsing function is complex, you can use it in a custom function instead of inserting it directly into the query.

Answer 3

Something like this, you always have "Test (number)". It runs on SQL Server 2005+

 DECLARE @Table1 TABLE (id_number int, textcol nvarchar(MAX)) INSERT @Table1 VALUES (1, 'Hello, test 532. Yay oh and test 111') INSERT @Table1 VALUES (2, 'test 932.') INSERT @Table1 VALUES (3, 'This is a test 315 of stuff test 555.') INSERT @Table1 VALUES (4, 'haflksdhfal test 311 sadjhfalsd') INSERT @Table1 VALUES (5, 'Yay.') ;WITH cte AS ( SELECT TOP 9999 CAST(ROW_NUMBER() OVER (ORDER BY c1.OBJECT_ID) AS varchar(6)) AS TestNum FROM sys.columns c1 CROSS JOIN sys.columns c2 ) SELECT id_number, TestNum FROM cte JOIN @Table1 ON PATINDEX('%Test ' + TestNum + '[^0-9]%', textcol) > 0 OR textcol LIKE '%Test ' + TestNum ORDER BY id_number 

Answer 4

The function you are looking for is called CURSOR - here is an article on how to use them.

They are considered poor for performance and difficult to use.

Rethink your problem and repeat it so that you can solve it in a set-based mode.

Look at using table variables or sub query for your complex state.

Answer 5

You are behind the cursor - see MSDN docs here . Please note that cursors should be avoided wherever possible - there are very few places that they fit and can lead to slow inefficient code - usually you would better try a dial-based solution.

Answer 6

To do this as you request, using iteration, you can do this with the cursor, using the information below on examples of how to lay out the cursor. You put your step-by-step process where my comment is.

 DECLARE @CurrentRecord VARCHAR(MAX) DECLARE db_cursor CURSOR FOR SELECT Column FROM Table OPEN db_cursor FETCH NEXT FROM db_cursor INTO @CurrentRecord WHILE @@FETCH_STATUS = 0 BEGIN --Your stuff here FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor 

However, depending on what you do, and if that's what you do on a regular basis. I would recommend to see if parsing can be inferred to a user-defined function, then you can configure it based on, rather than using a cursor. Because the cursor should be the "last ditch".