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!

Hey @Oscar-Thanks for your response. As mentioned above, I want to find special characters in table3. How can I do that? — Akhil, Nov 23 '17 at 10:29
Possible duplicate of [Find all special characters in a column in SQL Server 2008](https://stackoverflow.com/questions/12897886/find-all-special-characters-in-a-column-in-sql-server-2008) — Gimly, Nov 23 '17 at 10:31
But I want to search for all columns where ever we have special characters. In the above example, I can not put `Where Name2 like '%[^a-Z0-9]%'`. Because I do not want to search only one column right? Please have a look at above mentioned _source_ **Table3** and **EXPECTED OUTPUT** — Akhil, Nov 23 '17 at 10:34

Tyron78 · Accepted Answer · 2017-11-23T15:41:11.557

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'

Thanks @Tyron78. But In this example we have only 5 columns. Let's say if we have 50+ columns in a table. It is difficult to use this approach right? 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 — Akhil, Nov 23 '17 at 10:49
Thanks @Tyron78 - Code is working as fine as expected. But only one thing is, it is also showing the columns which doesn't have special characters in entire column. We don't need to display any columns if that column(s) doesn't have at least one special character. — Akhil, Nov 23 '17 at 12:50
I don't think that this is that easy to implement: This would mean you would have to know which columns don't have special chars at all in order to exclude them from the query statement - which on the other hand is used in order to perform the query... I guess here you will have to follow another approach, like storing the first result and then evaluating from this result what has to be displayed and what has to be faded... — Tyron78, Nov 23 '17 at 13:28
however, you might want to add further datatypes (like ntext and so on), but this depends on your DB Design — Tyron78, Nov 23 '17 at 14:20
Thank you so much @Tyron78 - Sorry, I'm not able to move to discussion or chat. System is showing an error. One last help. Can we also eliminate the rows if it doesn't have any special characters in the entire row as same as you did for column (s)? If yes, can you please help me with that? — Akhil, Nov 23 '17 at 14:46
It is working as expected. Thank you so much. Much appreciated your time and work. — Akhil, Nov 24 '17 at 08:30

score 0 · Answer 2 · answered Nov 23 '17 at 11:04

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);

Thanks @Wanderer - Code is working as fine expected. But only one thing is, it is also showing the columns which doesn't have special characters in entire column. We don't need to display any columns if that columns doesn't have at least one special character. — Akhil, Nov 23 '17 at 11:19

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

2 Answers2