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

Answers


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.


Need Your Help

Patch MediaWiki, skip minor version?

mediawiki patch

I want to patch MediaWiki from 1.23.1 to latest 1.23.4 (on Ubuntu 12.04.5) I plan to use patch: https://www.mediawiki.org/wiki/Manual:Upgrading#Using_patch

mysql get data into array show double data

php mysql

I am trying to retrieve data from MySQL. But my result shows one result 2 time.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.