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 ‘|’

One thought on “SQL – Search for special characters

  • 9 November 2022 at 4:35 PM
    Permalink

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

    Reply

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.