Creating and looping through a list in an sql server stored procedure

Question

Is there a way to declare a list of items in an sql server stored procedure using T-SQL and then loop through the items?

I'm trying to do something like this:

input_string = 'my dog has fleas.'
list_remove = 'a', 'e', 'i', 'o', 'u'

for each item in list remove
    input_string = replace(input_string, item, '')
end

And in the end input_string would be 'my dg hs fls.'

I know we can create a table in a stored procedure. Is that the best way to do something like this?

Answer 1

Take a look at Arrays and Lists in SQL Server Don't know what version of SQL server you have so pick the one from that page that applies to you

BTW for your problem you can just do a replace and replace the input character with ''

Answer 2

You can use a table variable to temporarily store a set of data.

Answer 3

Use:

DECLARE @input_string NVARCHAR(25)
DECLARE @ascii INT

SET @input_string = 'my dog has fleas.'
SET @ascii = 97

-- 97, 101, 105, 111, 117

WHILE @ascii <= 117
BEGIN

  SET @input_string = REPLACE(@input_string, CHAR(@ascii), '')

  SET @ascii = CASE @ascii 
                 WHEN 97 THEN 101
                 WHEN 101 THEN 105
                 WHEN 105 THEN 111
                 WHEN 111 THEN 117
               END
END

PRINT @input_string

Answer 4

You have a few options. OMG Ponies is one. You can also use a table variable (@) or a temp table (#). The main difference between @ and # is that the @ table is a variable and stores the information in memory and the # table stores it in the temp database (in a way that allows for duplicates if 2 or more copies of the sp are running at the same time). You will find that if your table is larger than a few dozen records the @ table might become slow. This is because of how it is stored. Read this from Stackoverflow for more info. Also, you can use a while loop but if you plan on having a ton of columns in the temp table I would use a cursor, read the msdn article for more information. Cursors also allow you to move back and forth easily in your table. Also, if you want them to be fast just use the FAST FORWARD option which makes them roughly as fast as a WHILE loop in SQL.