DECLARE @digits INT                    SET @digits = 4

DECLARE @foo TABLE (
	[staffNumber] VARCHAR(255)
	)

INSERT @foo ([staffNumber])
      SELECT '000123'
UNION SELECT '123'
UNION SELECT 'x123312'
UNION SELECT '1123~'
UNION SELECT '000001'
UNION SELECT '2131231231'

SELECT
	'PROBLEM CASE' AS [Validation]
	, [staffNumber]
FROM
	@foo
WHERE
	LEN([staffNumber]) <> @digits
	AND (
		[staffNumber] LIKE '%[^0123456789]%'
		OR
		CAST([staffNumber] AS INT) <> CAST(RIGHT([staffNUmber], @digits) AS INT)
	)

quote:

---

# re: Predicate ordering is not guaranteed
Or, better, how about designing tables with properly typed columns so as to avoid bad data issues to begin with?
Friday, May 19, 2006 4:24 PM by AdamMachanic

---

declare @digits int
set @digits = 4

Select
	*
from
	(  -- Test Data
	select staffNumber = '01234'	union all
	select staffNumber = '00123'	union all
	select staffNumber = '51234'	union all
	select staffNumber = '012a4'		union all
	select staffNumber = '1234'
	) a
where
	1 =
	case
	when [staffNumber] LIKE '%[^0123456789]%'
	then 1
	when CAST([staffNumber] AS INT) <> CAST(RIGHT([staffNUmber], @digits) AS INT)
	then 1
	else 0 end 

staffNumber 
----------- 
51234
012a4

(2 row(s) affected)