CODEWITHSUNDEEP
sql-servert-sql
Kev Laws
Kev Laws

Reputation: 23

Search Entire Database To find extended ascii codes in sql

We have issues with extended ascii codes getting in our database (128-155)

Is there anyway to search the entire database and display the results of any of these characters that may be in there and where they are located within the tables and columns.

Hope that makes sense.

I have the script to search entire DB, but having trouble with opening line.

DECLARE @SearchStr nvarchar(100)

SET @SearchStr != between char(32) and char(127)

I have this originally that works, but I need to extend the range I'm looking for.

SET @SearchStr = '|' + char(9) + '|' + char(10) + '|' + char(13)

Thanks

Upvotes: 2

Views: 4012

Answers (2)

Rick
Rick

Reputation: 21

... where dodgyColumn is your column with questionable data ....

WHERE(patindex('%[' + char(127) + '-' + char(255) + ']%', dodgyColumn COLLATE Latin1_General_BIN2) > 0)

This works for us, to identify extended ASCII characters in our otherwise normal ASCII data (characters, numbers, punctuation, dollar and percent signs, etc.)

Upvotes: 2

Pondlife
Pondlife

Reputation: 16240

It's very unclear what your data looks like, but this might help you to get started:

declare @TestData table (String nvarchar(100))

insert into @TestData select N'abc'
insert into @TestData select N'def'
insert into @TestData select char(128)
insert into @TestData select char(155)

declare @SearchPattern nvarchar(max) = N'%['
declare @i int = 128
while @i <= 155
begin
    set @SearchPattern += char(@i)
    set @i += 1
end
set @SearchPattern += N']%'

select @SearchPattern

select String
from @TestData
where String like @SearchPattern

Of course you'll need to add some code to loop over every table and column that you want to query (see this question), and it's possible that this code will behave differently on different collations.

Upvotes: 3

Related Questions