How to search for bad/special/non-printable characters in MS SQL Server

Document ID : KB000009725
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

Sometimes it is necessary to search for and eliminate bad data in tables such as S_QOS_DATA, NAS_ALARMS, etc. especially when the UMP/USM cannot display the data or remains at "Retrieving data..." or the interface/module simply never finishes loading data or hangs. The queries can be run in the SLM portlet or a database tool such as MS SQL Server studio.

Instructions:

For MSSQL:

 

declare @str varchar(255)

declare @i int
set @str = ''
set @i = 32
while @i <= 127
begin
set @str = @str + '|' + char(@i)
set @i = @i + 1
end

SELECT * from s_qos_data
WHERE target LIKE '%[^' + @str + ']%' escape '|'


Other examples for the end of the query include:

SELECT * from nas_alarms
WHERE message LIKE '%[^' + @str + ']%' escape '|'

 

For Oracle:

 

SELECT qos,source,target 

FROM s_qos_data, 

TABLE((SELECT COLLECT(LEVEL) 

FROM DUAL 

 

CONNECT BY LEVEL <= LENGTH(target))) WHERE ASCII(SUBSTR(target, COLUMN_VALUE, 1)) > 127