Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Location
    Near D.C.
    Posts
    54

    where not an integer

    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?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,906
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2003
    Location
    Near D.C.
    Posts
    54
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •