TSQL how do you iterate through rows while parsing

Question

Sorry for the poor question wording I wasn't sure how to describe this. I want to iterate through every row in a table and while doing so, extract a column, parse the varchar that is in it and depending on what it finds insert rows into another table. Something along the lines of this:

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 really nice for this to be a stored procedure so for it to be done in SQL. I'm just not too sure on how to approach it. 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 parse all of the text columns to look for instances of 'test #' where # is a number. When it finds something inside of the text in that format it will insert that value into another table like:

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

Answer 1

Something like this is you always have "Test (number)". It works 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 2

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

They are considered bad for performance and difficult to use correctly.

Rethink your problem and restate it so it can be solved in a set based operation.

Look at using table variables or sub queries for your complex condition.

Answer 3

You are thinking procedurally instead of set based. You can probably write the whole thing as a single query:

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

It is much easier to write, and probably a lot faster too.

If your parsing function is complicated, you can use put it into a user defined function instead of embedding it directly into the query.

Answer 4

You're after a cursor - see the MSDN docs here. Note that cursors should be avoided wherever possible - there are very few places that they're appropriate and can result in slow inefficient code - you're usually better off trying a set-based solution.

Answer 5

To do this as you request, with iteration you can do it using a Cursor, using your sample information below is how a cursor is laid-out. You put your row-by-row 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 are doing, and if this is something that you do on a regular basis. I would recommend seeing if you can extract the parsing out to a User Defined Function, then you could make it set based, and not use a cursor. As a cursor should be a "last ditch" effort.

Answer 6

In SQL Server 2008 you can do this

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