Compare alpha results to INT after get values from a string

Transact-SQL
1

Trying to see if i can check if numeric value returned after getting last digits after a period.
without doing a case statement.

CREATE TABLE [dbo].[EMPL_TEST](
[ORG_ID] nvarchar NULL,
[EMPL_ID] nvarchar NULL
) ON [PRIMARY]
GO

INSERT INTO EMPL_TEST (ORG_ID, EMPL_ID)
SELECT '01.301','12345'

INSERT INTO EMPL_TEST (ORG_ID, EMPL_ID)
SELECT '01.ZZZ','55555'

SELECT * FROM EMPL_TEST

--Works for numeric - ORG ID IS 01.301
SELECT REVERSE(SUBSTRING(REVERSE(A.ORG_ID), 1, CHARINDEX('.', REVERSE(A.ORG_ID)) - 1)), * FROM EMPL_TEST A WHERE
EMPL_ID = '12345'
AND REVERSE(SUBSTRING(REVERSE(A.ORG_ID), 1, CHARINDEX('.', REVERSE(A.ORG_ID)) - 1)) = 301

--Fails if comparing alpha to numeric ORG ID IS 01.ZZZ
--Employee last characters are ZZZ - gives Conversion failed when converting the varchar value 'ZZZ' to data type int
SELECT REVERSE(SUBSTRING(REVERSE(A.ORG_ID), 1, CHARINDEX('.', REVERSE(A.ORG_ID)) - 1)), * FROM EMPL_TEST A WHERE
EMPL_ID = '55555'
AND REVERSE(SUBSTRING(REVERSE(A.ORG_ID), 1, CHARINDEX('.', REVERSE(A.ORG_ID)) - 1)) = 301

2

You can use TRY_CAST to convert the string to an int: if it fails, it will return NULL, which will make the WHERE condition not true

3

You could also use the function ISNUMERIC().

SELECT 
	REVERSE(SUBSTRING(REVERSE(A.ORG_ID), 1, CHARINDEX('.', REVERSE(A.ORG_ID)) - 1)), 
	RIGHT(A.ORG_ID,3) AS ShorterCode,
	* 
FROM EMPL_TEST A 
WHERE ISNUMERIC(REVERSE(SUBSTRING(REVERSE(A.ORG_ID), 1, CHARINDEX('.', REVERSE(A.ORG_ID)) - 1)))=1
4

Why not just compare to the string '301'?

5

Yes i think that is my way to go. Thanks

6

Thanks i will look at this

7

Hi

Hope this helps


CREATE TABLE [dbo].[EMPL_TEST]([ORG_ID] nvarchar NULL,[EMPL_ID] nvarchar NULL) ON [PRIMARY]
GO

INSERT INTO EMPL_TEST (ORG_ID, EMPL_ID)
SELECT '01.301','12345'

INSERT INTO EMPL_TEST (ORG_ID, EMPL_ID)
SELECT '01.ZZZ','55555'

SELECT * FROM EMPL_TEST

-- Works for numeric - ORG ID IS 01.301
SELECT REVERSE(SUBSTRING(REVERSE(A.ORG_ID), 1, CHARINDEX('.', REVERSE(A.ORG_ID)) - 1)), * 
FROM EMPL_TEST A 
WHERE EMPL_ID = '12345' 
AND REVERSE(SUBSTRING(REVERSE(A.ORG_ID), 1, CHARINDEX('.', REVERSE(A.ORG_ID)) - 1)) = 301

-- Fails if comparing alpha to numeric ORG ID IS 01.ZZZ
-- Employee last characters are ZZZ - gives Conversion failed when converting the varchar value 'ZZZ' to data type int
SELECT REVERSE(SUBSTRING(REVERSE(A.ORG_ID), 1, CHARINDEX('.', REVERSE(A.ORG_ID)) - 1)), * 
FROM EMPL_TEST A 
WHERE EMPL_ID = '55555' 
AND REVERSE(SUBSTRING(REVERSE(A.ORG_ID), 1, CHARINDEX('.', REVERSE(A.ORG_ID)) - 1)) = 301
1 Like