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

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/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.

Environment

Release: Any

Resolution

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 searching NAS alarm messages:

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