Test if all of the last 8 characters of a string are numbers

1

I have a case statement that is testing various conditions on a varchar column. The correct format for the data should start with two letters and be followed by eight numbers. Here is a piece of code from my case statement.
WHEN RIGHT(QRNumber, 8) NOT LIKE '%[0-9]%' THEN 'Validation Failure' Else Valid Quote Number

So QR123456AA is returning Valid Quote Number when it is not as the last two digits are non numeric characters.

I want to say if any of the last 8 characters are not numbers then the data is invalid.

Can anybody help me with the code I need?

Thanks

Vinnie

2

WHEN RIGHT(QRNumber, 8) LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN 'Valid Quote Number' Else 'Validation Failure' END

3

This is brilliant. Thank you so much for your help.

4

Or:

WHEN RIGHT(QRNumber, 8) NOT LIKE '%[^0-9]%' THEN 'Valid Quote Number' ELSE 'Validation Failure' NMD

5

hi

the data should start with two letters and be followed by eight numbers
is the requirement

i have used a different approach ... using tSQL function ... ISNUMERIC ....

please click arrow to the left for Drop Create SAMPLE Data 
drop table #data 
go 

create table #data 
(
test varchar(100)
) 
go 

insert into #data select 'AB12345678'
insert into #data select 'ABCC345678'
insert into #data select '113345678'
go

image

select   'SQL using ISNUMERIC function'
       , test          as Column_Test 
	   , left (test,2) as first_2_chars
	   , right(test,8) as last_8_chars 
from 
   #data 
      where ISNUMERIC( left(test,2)) <> 1 
	    and ISNUMERIC(right(test,8)) =  1 ;

image