Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Check input parameter value is integer or not

This question was asked to me in one of the technical interviews I had recently attended. Here we have to accept an input string and check whether it is an integer value or not. I told him about the Try_Convert but he told me to come up the custom logic. Please check out the sample input values and sample expected output below.

Sample Input

DECLARE @ AS VARCHAR(100) = ‘2147483648.1’
DECLARE @ AS VARCHAR(100) = ‘F’
DECLARE @ AS VARCHAR(100) = ‘9F’
DECLARE @ AS VARCHAR(100) = ‘$’
DECLARE @ AS VARCHAR(100) = ’20’

Expected Output

Not an Integer
Not an Integer
Not an Integer
Not an Integer
An Integer

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION 1 | Using Try_Convert


--


DECLARE @ AS VARCHAR(100) = '2147483648.1'
IF TRY_PARSE(@ AS INT) IS NULL 
	PRINT 'Not an Integer'
ELSE
	PRINT 'An Integer'
GO

DECLARE @ AS VARCHAR(100) = 'F'
IF TRY_PARSE(@ AS INT) IS NULL 
	PRINT 'Not an Integer'
ELSE
	PRINT 'An Integer'
GO

DECLARE @ AS VARCHAR(100) = '9F'
IF TRY_PARSE(@ AS INT) IS NULL 
	PRINT 'Not an Integer'
ELSE
	PRINT 'An Integer'
GO

DECLARE @ AS VARCHAR(100) = '$'
IF TRY_PARSE(@ AS INT) IS NULL 
	PRINT 'Not an Integer'
ELSE
	PRINT 'An Integer'
GO

DECLARE @ AS VARCHAR(100) = '20'
IF TRY_PARSE(@ AS INT) IS NULL 
	PRINT 'Not an Integer'
ELSE
	PRINT 'An Integer'
GO




--

SOLUTION 2 | Using Numbers Table


--

	 
DECLARE @ AS VARCHAR(100) = '2147483648.1'
SELECT CASE WHEN SUM(CASE WHEN ASCII(n) BETWEEN 48 AND 57 THEN 1 END) = SUM(1) THEN 'An Integer' ELSE 'Not an Integer' END IsInteger
FROM 
(
	SELECT DISTINCT SUBSTRING(@,Number,1) n
	FROM Master..Spt_Values
	WHERE Number > 0 AND Number <= DATALENGTH(@)
)r
GO

DECLARE @ AS VARCHAR(100) = 'F'
SELECT CASE WHEN SUM(CASE WHEN ASCII(n) BETWEEN 48 AND 57 THEN 1 END) = SUM(1) THEN 'An Integer' ELSE 'Not an Integer' END IsInteger
FROM 
(
	SELECT DISTINCT SUBSTRING(@,Number,1) n
	FROM Master..Spt_Values
	WHERE Number > 0 AND Number <= DATALENGTH(@)
)r
GO

DECLARE @ AS VARCHAR(100) = '9F'
SELECT CASE WHEN SUM(CASE WHEN ASCII(n) BETWEEN 48 AND 57 THEN 1 END) = SUM(1) THEN 'An Integer' ELSE 'Not an Integer' END IsInteger
FROM 
(
	SELECT DISTINCT SUBSTRING(@,Number,1) n
	FROM Master..Spt_Values
	WHERE Number > 0 AND Number <= DATALENGTH(@)
)r
GO

DECLARE @ AS VARCHAR(100) = '$'
SELECT CASE WHEN SUM(CASE WHEN ASCII(n) BETWEEN 48 AND 57 THEN 1 END) = SUM(1) THEN 'An Integer' ELSE 'Not an Integer' END IsInteger
FROM 
(
	SELECT DISTINCT SUBSTRING(@,Number,1) n
	FROM Master..Spt_Values
	WHERE Number > 0 AND Number <= DATALENGTH(@)
)r
GO

DECLARE @ AS VARCHAR(100) = '20'
SELECT CASE WHEN SUM(CASE WHEN ASCII(n) BETWEEN 48 AND 57 THEN 1 END) = SUM(1) THEN 'An Integer' ELSE 'Not an Integer' END IsInteger
FROM 
(
	SELECT DISTINCT SUBSTRING(@,Number,1) n
	FROM Master..Spt_Values
	WHERE Number > 0 AND Number <= DATALENGTH(@)
)r
GO	 

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com