Suspected bad data in table

Question

Suspected bad data in table

krypto69

SSChampion

Points: 13591
More actions
May 12, 2017 at 1:36 pm

#337834

HI,
I think I have bad data getting into one of my tables.
I have searched and found some scrub/check related queries that look at one column at a time.
Is there a way to search all columns in a table for 'non standard' and/or data that should be there?

Viewing 3 posts - 1 through 2 (of 2 total)

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

Phil Parkin SSC Guru Points: 246713 More actions · Answer 1

krypto69 - Friday, May 12, 2017 1:36 PM
HI,
I think I have bad data getting into one of my tables.
I have searched and found some scrub/check related queries that look at one column at a time.
Is there a way to search all columns in a table for 'non standard' and/or data that should be there?

You mean like
SELECT COLUMNS FROM TABLE WHERE DATA LOOKS STRANGE OR OUT OF PLACE
?
I really don't think so.

Perhaps if you take the time to rewrite your question more carefully, someone will respond more helpfully.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Greg Goss SSCrazy Points: 2160 More actions · Answer 2

I'm only guessing at your intent based on the vagueness of the question, but I took it to mean that you are seeing some strange behavior in an application of some sort and you want to pinpoint the rows of data that could be causing this.

If you're using a new-ish version of SQL, you should research the try_convert and try_cast functions. If you suspect a text column named COL1 that you expect to be some sort of date value (i.e. 20170517) then you could test for non date values by using something like

SELECT COL1, TRY_CONVERT(datetime,COL1,105)
WHERE TRY_CONVERT(datetime,COL1,105) IS NULL

...in order to figure out which rows contain "bad" data.

If that is your intention, then you could write such a query against each column. It requires that you know exactly how the data is supposed to be used and would be time consuming to write for wide tables, but I've used this method reliably in the past. If I'm way off on my guess at what you need, please clarify the question and provide a specific example of what you suspect is happening.

Regards,
-G