How do I combine Right and Like functions in a query?

Question

Given the following field and entries, how to I find the 3 entries that have a non-numeric character within the last 4 letters?

Field1
------
MA1234
DC4567
OHABCD
CAWXYZ
FLLMNO
MI9876

I'm guessing it would be a combination of Right and Like "[A-Z]*", such as Right([Field1], 4) = Like "[A-Z]*" but that doesn't work.

EDIT: I'm looking for any entries where any of the last 4 characters is non-numeric.

Thanks!

sounds like regex and access do not play real well together. see this question for some more information - http://stackoverflow.com/questions/5539141/microsoft-office-access-like-vs-regex — Matt Busche, Mar 06 '13 at 15:19
@HansUp, AB123C would satisfy the 'non-numeric within the last 4 letters' condition. I updated my original question to clarify. Thanks! — Jeff Brady, Mar 07 '13 at 16:07

LittleBobbyTables - Au Revoir · Accepted Answer · 2013-03-06T15:34:05.947

1

You can grab the last four characters, and compare them one character at a time, like this:

WHERE RIGHT(Field1, 4) LIKE '[A-Z][A-Z][A-Z][A-Z]'

I know the example is in SQL 2008, but here's a SQL Fiddle that demonstrates it just as well.

EDIT:

If you want to the fields that have at least one non-numeric character (i.e. aren't all 0-9), you can search for a non-numeric value (LIKE '*[^0-9]*') and make sure all four characters aren't numbers (NOT LIKE '[0-9][0-9][0-9][0-9]') :

WHERE RIGHT(Field1, 4) LIKE '*[^0-9]*' AND 
    RIGHT(Field1, 4) NOT LIKE '[0-9][0-9][0-9][0-9]'

edited Mar 06 '13 at 15:34

answered Mar 06 '13 at 15:19

LittleBobbyTables - Au Revoir

32,008
25
109
114

that worked .. I ended up using `WHERE RIGHT(Field1, 4) LIKE '[A-Z]'` which seems to look for only 1 of the 4 letters being within A-Z, which I didn't account for in my original question. – Jeff Brady Mar 06 '13 at 15:24
1

I'm confused, did you only want to find 1 letter in the last four, or the last four all had to be letters? If the former, I would thinnk you would want `LIKE '*[A-Z]*'` (note the asterisks) – LittleBobbyTables - Au Revoir Mar 06 '13 at 15:27
I didn't realize until just now that the data is invalid if only 1 of the last 4 characters is A-Z. I edited my original question, and my solution in the previous comment didn't work after all. – Jeff Brady Mar 06 '13 at 15:29
I think `Right([NABP],4) Like "*[A-Z]*"` is working .. adding the `*` before the range as well as the end – Jeff Brady Mar 06 '13 at 15:32

score 0 · Answer 2 · edited Mar 06 '13 at 16:35

0

Here is the answer:

SELECT *
FROM 
(
    VALUES
        ('MA1234'),
        ('DC4567'),
        ('OHABCD'),
        ('CAWXYZ'),
        ('FLLMNO'),
        ('MI9876')
) v(Code)
WHERE Code like '*[A-Z][A-Z][A-Z][A-Z]'

edited Mar 06 '13 at 16:35

iDevlop

24,841
11
90
149

answered Mar 06 '13 at 15:21

Yaugen Vlasau

2,148
1
17
38

How do I combine Right and Like functions in a query?

2 Answers2