mardi 30 décembre 2014

Checking if data violates SQL decimal constraint

I have a table that is getting a column changed from a VARCHAR to DECIMAL(20,7) data type. I need find out what rows have a value in the column that violates the DECIMAL(20,7) data type and I believe I am close.


What I have so far is this:



SELECT *
FROM tableName
WHERE
(ISNUMERIC(columnName) = 0 and columnName IS NOT NULL)
OR (columnName LIKE '%,%')
OR (LEN(columnName) > 20)


I think I am mostly hung up on the the LEN check, as I can still miss results. Is there a way to get deeper into the length so I can check with precision, instead of just a length of 20?


Aucun commentaire:

Enregistrer un commentaire