Nov 8, 2013

How to check whether a given data is numeric or not in sql server?

In sql server we can test or check whether a given data is type of numeric or not by using function ISNUMERIC

Syntax: ISNUMERIC(Expression)

Where Expression can be of any tpype of data like int,varchar,nvarchar,bit etc. It can be also column name of a table.

Return type: If Expression is numeric it returns 1 otherwise 0

Numeric data type on the basis of ISNUMERIC function
1. bit
2. int
3. tinyint
4. smallint
5. bigint
6. decimal
7. numeric
8. float
9. real
10. money
11. smallmoney
12. Some characters at specific position like +,-,$ etc.

Examples:
(a)

SELECT ISNUMERIC(456)
Output: 1


SELECT ISNUMERIC('456')
Output: 1

(2)

SELECT ISNUMERIC(+45.56)
Output: 1

SELECT ISNUMERIC('+45.56')
Output: 1

SELECT ISNUMERIC('45.56+')
Output: 0
   
(3)
   
SELECT ISNUMERIC($34)
Output: 1

SELECT ISNUMERIC('$34')
Output: 1

SELECT ISNUMERIC('34$')
Output: 0


(4)
SELECT ISNUMERIC('-$')
Output: 1

SELECT ISNUMERIC(6+5)
Output: 1

SELECT ISNUMERIC('6+5')
Output: 0

(5)
DECLARE @Num AS BIT
SET @Num = 'True'
SELECT ISNUMERIC(@Num)
Output: 1

DECLARE @Num AS BINARY
SET @Num = 1
SELECT ISNUMERIC(@Num)
Output: 0

SELECT ISNUMERIC('True')
Output: 0

(6)
SELECT ISNUMERIC(+$45.56)
Output: 1

SELECT ISNUMERIC($+45.56)
Output: 1

No comments:

Post a Comment