How to Change Space Only Columns to NULL in SQL Server

sql servert-sql

I have a database with four tables that has a number of columns (VARCHAR) that contains one or more spaces. I would like to replace these spaces with NULL.

I can do it if I specify the table and each column

UPDATE LaserData 
SET Kant = NULL 
WHERE Kant= ''

But is there a way that I can use a list of tables and a list of columns and do this for all without writing a query for each?

Best Answer

That's an odd question, but you could go with something like this:

USE YourDatabaseName
GO

CREATE TABLE #TempColumns (
    id int, 
    tname varchar(50), 
    cname varchar(50))

INSERT INTO #TempColumns (
    id, 
    tname, 
    cname) 
SELECT 
    ROW_NUMBER() OVER (ORDER BY t.name) AS ColId,
    t.name,
    c.name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

DECLARE @ColumnCount INT =
(
    SELECT COUNT(*) FROM #TempColumns
),
@i INT = 1,
@TableName VARCHAR(MAX),
@ColumnName VARCHAR(MAX),
@DataType VARCHAR(20),
@AllowNull INT;

WHILE (@i <= @ColumnCount)
BEGIN
    SET @TableName = (
        SELECT t.tname 
        FROM #TempColumns t 
        WHERE t.id = @i);
    SET @ColumnName = (
        SELECT t.cname 
        FROM #TempColumns t 
        WHERE t.id = @i);
    SET @DataType = (
        SELECT t.name 
        FROM sys.columns AS c INNER JOIN sys.types AS t ON c.user_type_id=t.user_type_id 
        WHERE OBJECT_NAME(c.OBJECT_ID) = @TableName AND c.name = @ColumnName);
    SET @AllowNull = (
        SELECT COLUMNPROPERTY(OBJECT_ID(@TableName), @ColumnName, 'AllowsNull'));

    IF (@DataType = 'varchar')
    BEGIN
        IF (@AllowNull = 1)
        BEGIN
            EXEC('UPDATE ' + @TableName + ' SET ' + @ColumnName + ' = NULL WHERE ' + @ColumnName + '= ''''')
        END
    END
    SET @i = @i + 1;
END

This will create a temporary table to store all the table names and their respective columns, it will then perform an update (in this case) per every column on every table only when datatype is VARCHAR.

Related Question