Find the column(s) with values which has special characters using SQL

Question

I have some tables in a DB as mentioned below.,

**Table1**
+-------+--------------+-------------+
|**ID** |**Name**      |**Country**  |
+-------+--------------+-------------+
|  1    |Avinash Kumar |India        |
|  2    |Sat!sh@S      |USA          |
|  3    |$32kjs        |UK           |
|  4    |#$@@@         |AFRICA       |
|  5    |Krishnas_has  |USA          |
+-------+--------------+-------------+

**Table2**
+-------+--------------+-------------+-----------+
|**ID** |**Name1**     |**Country1** |**Region1**|
+-------+--------------+-------------+-----------+
|  1    |Avinash Kumar |India        |EMEA       | 
|  2    |Sat!sh@S      |USA          |ASIA@@     |
|  3    |$32kjs        |UK           |EU._A      |
|  4    |#$@@@         |AFRICA       |HAS&#      |
|  5    |Krishnas_has  |USA          |KALc!!     |
+-------+--------------+-------------+-----------+

**Table3**
+-------+--------------+-------------+-----------+-----------+
|**ID** |**Name2**     |**Country2** |**Region2**|**State2** |
+-------+--------------+-------------+-----------+-----------+
|  1    |Avinash.Kumar |India$       |EMEA       |BANG_&     |
|  2    |Sat!sh@S      |US!!A        |ASIA@@     |SO$TH      |
|  3    |$32kjs        |U@K          |EU._A      |TRUTH      |
|  4    |#$@@@         |AFRICA       |HAS        |HAPPY      |
|  5    |Krishnas_has  |USA#         |KALc!!     |!ASDF#     |
+-------+--------------+-------------+-----------+-----------+

And one more thing is like I want to pass the table name as variable. Something like this

Declare @Table Select * from @Table What ever I pass in @Table the code has to work dynamically

Now how can I find the special character columns if i pass @Table = Table3

For example: My output should be like this when I want to find special characters in Table3

**EXPECTED OUTPUT**
+-------+--------------+-------------+-----------+-----------+
|**ID** |**Name2**     |**Country2** |**Region2**|**State2** |
+-------+--------------+-------------+-----------+-----------+
|  1    |Avinash.Kumar |India$       |           |BANG_&     |
|  2    |Sat!sh@S      |US!!A        |ASIA@@     |SO$TH      |
|  3    |$32kjs        |U@K          |EU._A      |           |
|  4    |#$@@@         |             |           |           |
|  5    |Krishnas_has  |USA#         |KALc!!     |!ASDF#     |
+-------+--------------+-------------+-----------+-----------+

Can someone please help me with this?

Thanks in advance!

Answer 1

Have a look at this approach:

CREATE TABLE Test (
  ID INT,
  Name2 NVARCHAR(100),
  Country2 NVARCHAR(100),
  Region2 NVARCHAR(100),
  State2 NVARCHAR(100)
)
GO

DELETE test

INSERT INTO Test VALUES(1, 'Avinash.Kumar', 'India$', 'EMEA', 'BANG_&'), (2, 'Sat!sh@S', 'US!!A', 'ASIA', 'SO$TH'), (3, 'Test1', 'Test2', 'Test3', 'Test4')
GO

CREATE FUNCTION chkStrg4Chars(@InputString NVARCHAR(4000)) RETURNS NVARCHAR(4000)
AS
BEGIN
  DECLARE @RetVal NVARCHAR(4000);
  SET @RetVal = CASE WHEN @InputString LIKE '%[^A-Za-z0-9 .]%' THEN @InputString ELSE NULL END;
  RETURN @RetVal
END;
GO

CREATE PROCEDURE chkTableColumns4Chars(@InputTable NVARCHAR(500))
AS
BEGIN
  DECLARE @ColList NVARCHAR(MAX);
  DECLARE @ColListNonC NVARCHAR(MAX);
  DECLARE @ColListRes NVARCHAR(MAX);
  DECLARE @ColListPiv NVARCHAR(MAX);

  SELECT @ColList = (SELECT CASE WHEN t.name IN ('nvarchar','varchar') THEN 'dbo.chkStrg4Chars(' + c.name + ') AS ' + c.name ELSE c.name END + ','
                       FROM sys.columns c
                       JOIN sys.types t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
                       WHERE c.object_id = OBJECT_ID(@InputTable)
                       ORDER BY c.column_id
                       FOR XML PATH('')
                     );

  SET @ColList = SUBSTRING(@ColList, 1, LEN(@ColList)-1);

  SELECT @ColListRes = (SELECT 'MAX(' + c.name + ') AS ' + c.name + ','
                       FROM sys.columns c
                       JOIN sys.types t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
                       WHERE c.object_id = OBJECT_ID(@InputTable)
                         AND t.name IN ('nvarchar','varchar')
                       ORDER BY c.column_id
                       FOR XML PATH('')
                     );

  SET @ColListRes = SUBSTRING(@ColListRes, 1, LEN(@ColListRes)-1);

  SELECT @ColListNonC = (SELECT c.name + ','
                       FROM sys.columns c
                       JOIN sys.types t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
                       WHERE c.object_id = OBJECT_ID(@InputTable)
                         AND t.name NOT IN ('nvarchar','varchar')
                       ORDER BY c.column_id
                       FOR XML PATH('')
                     );

  SELECT @ColListPiv = (SELECT c.name + ','
                       FROM sys.columns c
                       JOIN sys.types t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
                       WHERE c.object_id = OBJECT_ID(@InputTable)
                         AND t.name IN ('nvarchar','varchar')
                       ORDER BY c.column_id
                       FOR XML PATH('')
                     );

  SET @ColListPiv = SUBSTRING(@ColListPiv, 1, LEN(@ColListPiv)-1);

  DECLARE @stmt NVARCHAR(MAX) = 'SELECT ' + @ColList + ' INTO #TTest1 FROM ' + @InputTable + ';
DECLARE @SubCol nvarchar(max) = (SELECT ColName + ' + CHAR(39) + ',' + CHAR(39) + ' FROM (SELECT ' + @ColListRes + ' FROM #TTest1) x UNPIVOT (StrVal FOR ColName in (' + @ColListPiv + '))u FOR XML PATH('+CHAR(39)+CHAR(39)+'));
SET @SubCol = SUBSTRING(@SubCol, 1, len(@SubCol)-1)
DECLARE @SubStmt nvarchar(max) = ' + CHAR(39) + 'SELECT + ' + @ColListNonC + char(39) + ' + @SubCol + ' + CHAR(39) + ' FROM #TTest1 WHERE COALESCE( ' + CHAR(39) + ' + @SubCol + '+ CHAR(39) + ') IS NOT NULL' + CHAR(39) + ';
EXEC sp_executesql @SubStmt;'

EXEC sp_executesql @stmt;

END
GO


EXEC chkTableColumns4Chars 'dbo.Test'

Answer 2

As you said if you have lot of columns in your table, then writing the sql query to check each column would be a hectic work. For easiness we can do it by executing dynamic sql query.

Query

declare @sql as varchar(max);

select @sql = stuff((
        select ', case when [' + [column_name] + '] like ' 
        + char(39) + '%[^A-Za-z0-9 ]%' + char(39) 
        + ' then [' + [column_name] + '] else ' 
        + char(39) + char(39) + ' end as [' + [column_name] + '] '
        from information_schema.columns
        where table_name = 'your_table_name'
        and [column_name] <> 'ID'
        for xml path('')
    )
    , 1, 1, ''
);

select @sql = 'select [ID], ' + @sql + ' from [your_table_name];';

exec(@sql);