0

I have a column which has a data type of varchar(25), while the values in the column could be something like '89009', 'HS123','4491AU', '44989EA'.

How do I select values of which the LAST TWO digits are not numbers but letters?

In this case how do I select only '4491AU' and '44989EA'

Please advise.

Thank you all. Problem solved

PURWU
  • 397
  • 1
  • 8
  • 22

3 Answers3

4

Use like:

where col like '%[a-zA-Z][a-zA-Z]'

or, if you just want non-digits:

where col like '%[^0-9][^0-9]'
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1
select * from your_table
where some_column like '%[A-Z][A-Z]'
juergen d
  • 201,996
  • 37
  • 293
  • 362
1

How about this? http://rextester.com/ZEO59984

SELECT *
    ,isnumeric(right(value, 2)) AS IsNumericCheck
FROM table1
    where isnumeric(right(value,2)) = 0

The Isnumeric function checks whether the input is numeric. Returns 1 if it is numeric and 0 if it is not. In the code above, the are using the right 2 characters of the Value column to perform the check.

Aron
  • 765
  • 6
  • 14
  • 3
    This only kind of works. IsNumeric returns a LOT of false positives. Here are a few 2 characters strings that will evaluate to true with IsNumeric. .8, $4, 2. I am pretty sure the OP wants where the last two characters are numbers. – Sean Lange Nov 10 '16 at 21:12
  • Learn something new everyday! Dmitriy said the same thing, but I am reading the OP's question as "not numbers". Either way, the regex answers are also good – Aron Nov 10 '16 at 21:14
  • 1
    Oh and one of my favorites that evaluates to true "$.". No clue how a dollar sign and a period (decimal point) alone can be numeric. But it is because you can convert it to money. select convert(money, '$.') Bizarre huh?? – Sean Lange Nov 10 '16 at 21:43