Select based on data type 2

[ProfReynolds]

Is there a way to select rows based on the type of data that is in them???

For instance:
SELECT * FROM Table
WHERE FieldName.DataType NOT AN INTEGER

I would settle for something like
SELECT * FROM Table
WHERE CAST(FieldName AS int) == ErrorCode

Thanks!

 

[ca8msm]

Try looking at the isnumeric function. There's an FAQ you should also look at here:

faq183-6423


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[SQLSister]

Ideally the column should only contain integer data if that is what it is used for and should then be an int datatype.

If you have a design that permits both types of data and are stuck with the design, the ISNUMERIC () function can get you started. Be aware though that when you start looking for things this way, you end up with code that is much less effficient than if the design has the correct datatype to begin with. There is also an isdate() function for date fileds which are stored in character type columns. Again, this is something to be avoided if possible. It is far better to have a datatime column that you can directly fdo date matho on thatn to work around by findiong only the valid dates and then converting those to datetime so you can do date math.

If you only liked to worry about a few characters to check you could consider using some variation of like '[0-9]%'

"NOTHING is more important in a database than integrity." ESquared

 

[ProfReynolds]

Perfect! Thanks!

 

[ProfReynolds]

SQLSister,

I am trying to correct a flawed DB design. To do so, I had to isolate and remove some non-int values before changing the data type to allow only int values.

I couldn't find any way to do that, hence the post.



ca8msn,
Thanks! That did exactly what I needed.

 

[SQLSister]

Good for you trying to find and fix the bad values. Just makes sure you read the FAQ ca8sm linked to because there are some issues with those functions where you get false results.

"NOTHING is more important in a database than integrity." ESquared