Thursday, January 22, 2009
Positive Integer In Transact-SQL
While the IsNumeric Function in Transact-SQL is good for finding out if a varachar is a integer or a deciaml. Sometimes it is nice to find out if the varchar is a positive integer here is how:
CREATE TABLE #Temp (Number varchar(25))
INSERT INTO #Temp (Number) VALUES ('2.4')
INSERT INTO #Temp (Number) VALUES ('4')
INSERT INTO #Temp (Number) VALUES ('-4')
INSERT INTO #Temp (Number) VALUES ('-4a')
INSERT INTO #Temp (Number) VALUES ('4-a')
INSERT INTO #Temp (Number) VALUES ('4-')
INSERT INTO #Temp (Number) VALUES ('4444')
SELECT *
FROM #Temp
WHERE ISNUMERIC(Number) = 1 AND NOT Number LIKE ('%.%') AND NOT Number LIKE '%-%'
DROP TABLE #Temp
With test cases. Modify it to find integers both positive and negative:
CREATE TABLE #Temp (Number varchar(25))
INSERT INTO #Temp (Number) VALUES ('2.4')
INSERT INTO #Temp (Number) VALUES ('4')
INSERT INTO #Temp (Number) VALUES ('-4')
INSERT INTO #Temp (Number) VALUES ('-4a')
INSERT INTO #Temp (Number) VALUES ('4-a')
INSERT INTO #Temp (Number) VALUES ('4-')
INSERT INTO #Temp (Number) VALUES ('4444')
SELECT *
FROM #Temp
WHERE ISNUMERIC(Number) = 1 AND NOT Number LIKE '%.%'
DROP TABLE #Temp
{6230289B-5BEE-409e-932A-2F01FA407A92}
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment