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}

No comments:

Post a Comment