CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005decimal
Tim
Tim

Reputation: 11

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?

Upvotes: 1

Views: 202

Answers (3)

Jeff Ogata
Jeff Ogata

Reputation: 57783

You mention SQL Server 2012 in your comment, and for 2012 or later, see if TRY_PARSE will work for you.

declare @table table (Input varchar(50))

insert @table (Input) 
values ('20,000.0001'), ('abc'), ('1234'), ('0.12345678'), ('1234567891234.1234567')

select Input from @table
where 
    TRY_PARSE(Input as decimal(20,7)) <> TRY_PARSE(Input as decimal(21,8)) 
    or TRY_PARSE(Input as decimal(20,7)) is null

/*
   abc
   0.12345678
*/

Upvotes: 1

user3874673
user3874673

Reputation: 16

This should work for you.

SELECT * FROM [tablename] AS t
WHERE LEN([columnname]) > 21
OR LEN([columnname]) - CHARINDEX('.', [columnname]) > 7

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93704

I would suggest you to remove Isnumeric function which is going to fail in many cases. Instead add Like operator and one more condition. Still there might be some gaps to be filled.

SELECT *
FROM   tableName
WHERE  ( columnName LIKE '%[^.0-9]%'
         AND columnName IS NOT NULL )
        OR ( columnName LIKE '%,%' )
        OR (( columnName LIKE '%.%.%' ))
        OR ( Len(columnName) > 20 )

Upvotes: 0

Related Questions