SQL – Search for special characters

Sometimes it may happen that by importing data from external sources (even with Web Services), some special characters are written and then uploaded to NAV \ Business Central.

These characters (even if accepted) could then give problems to searches, XML exports, blocking the sending of documents.

How to find them quickly?

It is possible to execute an SQL query that checks all the ASCII characters and reports the special ones; in this way field-records containing these characters can be found easily.

SQL

— Start with tab, line feed, carriage return

declare @str varchar(1024)

set @str = ‘|’ + char(9) + ‘|’ + char(10) + ‘|’ + char(13)

— Add all normal ASCII characters (32 -> 127)

declare @i int

set @i = 32

while @i <= 127

begin

    — Uses | to escape, could be any character

set @str = @str + ‘|’ + char(@i)

set @i = @i + 1

end

After execute:

select *

from yourtable

where yourfield like ‘%[^’ + @str + ‘]%’ escape ‘|’

Example

select *

from dbo.[Company1$Customer]

where [Description] like ‘%[^’ + @str + ‘]%’ escape ‘|’