I have some dirty data in what is supposed to be an integer field.


What query would I do to find those rows that aren't an integer?

That depends. How is the data stored now (text, binary, real, decimal, other)? What version of SQL Server are you running? What do you mean by "integer" (integer data type, in16, int32, uint64, numeric with a zero precision, other)?

-PatP

Ok, I found the solution for myself.

I was looking for the rows that were killing the insert into an INT field.

Turns out for the first some files...no issue,
but when we get to one month...it starts with something that "looks" like a Zero, but it's not seeing it as such.

The query I used to find it was,

SELECT PATINDEX('%[^0-9]%',[Column 0]) as Find, count(*) as sum
FROM [dbo].[TABLE01]

The caret telling it to not find the number set

Yet, if I use the UNICODE function on it...both come back as the zero id number.

Could be a case of the source application putting in something,
or it could be a case of the system not recognizing a certain styling.
(ex. sometimes even though a " is the same, it doesn't like it from Word, but it does from Notepad)

Oh well, I'm going to simply reconfigure it and the staging table for now as a string/varchar...perhaps convert it later.