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.


— 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


    — Uses | to escape, could be any character

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

set @i = @i + 1


After execute:

select *

from yourtable

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


select *

from dbo.[Company1$Customer]

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

One thought on “SQL – Search for special characters

  • 9 November 2022 at 4:35 PM

    Thanks for the tip! This helped us solve problem caused by an integration to Business Central.


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.