2

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?

2
  • Not according to Microsoft! SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, , General rules #3) on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings 'abc' and 'abc ' to be equivalent for most comparison operations. – Andis59 Mar 14 '17 at 13:25
  • 1
    You must deal with dynamic queries. – McNets Mar 14 '17 at 13:39
1

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.

2
  • 2
    You probably should not use the INFORMATION_SCHEMA views; use the system DMVs instead, such as sys.columns. Also, if a column is defined as NOT NULL, this code will generate an error. – Hannah Vernon Mar 14 '17 at 13:54
  • I just turned null safe, didn't attempt to cover all the bases but to deal with the main subject 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 : ) – Nelson Mar 14 '17 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.