Search all fields for a particular character

Question

Search all fields for a particular character

Plateau

SSC Veteran

Points: 268
More actions
December 21, 2006 at 4:30 am

#170791

Hi,
Has anyone got a good query to search ALL fields in 1 table or even all user tables in one database for any occurence of one particular character (e.g. single quote mark).
Thanks
David

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

David

You will need to build a script dynamically. Something like this rough-and-ready effort, which generates a script that returns all rows where at least one of the columns has a Z in it. Obviously you can change Z for any character(s) you like.

select 'select * from [' + table_name + ']

where [' + column_name + '] like ''%Z%'''

from information_schema.columns

where data_type like '%char%'

John

Plateau SSC Veteran Points: 268 More actions · Answer 2

Hi John,

Cool thanks for that. Like your way because I want to remove the offending character, so I could change that easily to an update.

I found the data by

1. Scripting the table as a slect to the clipboard

2. Splitting the fields using "text to columns" in Excel (using an array and a split in VB would have worked)

3. Transposed the split data (so data goes vertical as opposed to horizontal)

4. Added the following as a formula to the right of my split, transposed fields =B37 & " LIKE '%''%' OR"

Cheers

D

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Dave

Glad you got it working. Just one more thing. If you use my method, and any of your tables have text columns, you will need to add the following:

select 'select * from [' + table_name + ']

where charindex(''Z'', [' + column_name + ']) > 0'

from information_schema.columns

where data_type like '%text'

John

Plateau SSC Veteran Points: 268 More actions · Answer 4

Hi John,

Noticed you'vre removed the "Like" and replaced with "Charindex" is this for performance?

Thanks

David

PS - might even make the undesired character a parameter, in case something else like this occurs at a later date. Maybe should allow a pattern too...

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

David

No, it's because LIKE doesn't work with text data types. The performance of this isn't going to be good, because none of the searches will be able to use indexes. Let's hope it's a one-off! Making your character into a parameter is a good idea.

John

Plateau SSC Veteran Points: 268 More actions · Answer 6

All my "string" fields are VARCHAR so I'll remove that charindex. Performance has been bearable 15,000 scanned in 10 seconds.

David Burrows SSC Guru Points: 65142 More actions · Answer 7

...because LIKE doesn't work with text data types...

That is not correct, it will

Far away is close at hand in the images of elsewhere.
Anon.