CODEWITHSUNDEEP
sqlsql-servert-sqlforeach
Mike Marks
Mike Marks

Reputation: 10139

Iterating through each column in my SQL table

I have a SQL table with about 50 columns. I want to be able to iterate through each one similar to a foreach loop. When I iterate through each column, I want to check to see if there's a single double quote appended at the end, and if so, remove it.

I have this solution built out but I explicitly define the column names, see below:

select BrandName = 
   (CASE
        WHEN (BrandName like '%"') THEN LEFT(BrandName, LEN(BrandName) - 1)
        ELSE BrandName
    END),
    emailSubject = 
   (CASE
        WHEN (emailSubject like '%"') THEN LEFT(emailSubject , LEN(emailSubject ) - 1)
        ELSE emailSubject 
    END),
    -- and so on...
    from tableName

This works fine, but introduces some possibility of error. So, this is why I want to do something like (pseudocode):

foreach column c in table
    if (c.EndsWith('"'))
        c.RemoveEndCharacter()

Is this possible, and if so can you provide some examples on how I might implement such a solution in T-SQL?

Thanks!

Upvotes: 0

Views: 134

Answers (1)

Dave Mason
Dave Mason

Reputation: 4936

You can generate tsql statements to update your table with the code below. Execute the statement, then copy/paste the output into a new query window and run. Alternatively, you could use the query below for a cursor and execute the output on each iteration.

SELECT 'UPDATE [' + c.TABLE_SCHEMA + '].[' + c.TABLE_NAME + '] SET [' + c.COLUMN_NAME + '] = LEFT([' + c.COLUMN_NAME + '], LEN([' + c.COLUMN_NAME + ']) - 1) WHERE [' + c.COLUMN_NAME + '] LIKE ''%"'''
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.TABLES t
    ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
    AND t.TABLE_NAME = c.TABLE_NAME

--For simple demo purposes, assume tables only.
WHERE t.TABLE_TYPE = 'BASE TABLE'

--Did I miss any data types?
AND c.DATA_TYPE IN ('NVARCHAR', 'VARCHAR', 'NCHAR', 'CHAR')

--Replace values with your corresponding schema/table name.
AND c.TABLE_SCHEMA = 'dbo'
AND c.TABLE_NAME = 'YourTableName'

ORDER BY c.TABLE_SCHEMA, c.TABLE_NAME, c.ORDINAL_POSITION

Upvotes: 3

Related Questions