SQL How to search text field for 2nd without matching 22nd, etc?

Question

I want to query for numbered street names that can occur anywhere within a text column, and filter out matches for numbered street names with more digits, i.e. 2nd but not 42nd, 182nd, etc. Is there any method more graceful or simplified than combination of:

WHERE col LIKE '2nd%' OR col LIKE '% 2nd%'

FYI, There is a space character after the first % in the second parameter. — Spike Eikenbery, Feb 07 '20 at 05:27
https://stackoverflow.com/questions/34542022/how-to-use-like-and-in-operator-at-the-same-like-name-like-in-alex-raj/34543158#34543158 — gotqn, Feb 07 '20 at 06:33
@SpikeEikenbery . . . Your query is using SQL Server syntax conventions (which are standard). I removed the MS Access tag. — Gordon Linoff, Feb 07 '20 at 13:04

score 1 · Answer 1 · answered Feb 07 '20 at 05:34

As long as the 2nd doesn't occur at the beginning of the string, you can just check that the character before it is not a digit using

col LIKE '%[^0-9]2nd%'

For example:

select col, case when  col like '%[^0-9]2nd%' then 'second' else 'not' end as test
from (values ('12 2nd st'), ('45 42nd st'), ('128 22nd st')) test(col)

Output:

col             test
12 2nd st       second
45 42nd st      not
128 22nd st     not

score 0 · Answer 2 · answered Feb 07 '20 at 13:05

0

Nick's answer is very good, but it doesn't handle the case when '2nd' appears at the beginning of a string. This is easily handled by pre-pending a character on the column being compared:

' ' + col LIKE '%[^0-9]2nd%'

answered Feb 07 '20 at 13:05

Gordon Linoff

1,242,037
58
646
786

SQL How to search text field for 2nd without matching 22nd, etc?

2 Answers2