If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > Database Server Software > Microsoft SQL Server > where not an integer

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Registered User
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?
Reply With Quote
  #2 (permalink)  
Resident Curmudgeon
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,815
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)?

In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #3 (permalink)  
Registered User
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.
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On