Regular Expression (RegEx) to find a particular word in any part of a string but has a character limit

Question

I'm wanting to use a Regular Expression in SQLs 'LIKE' function to be able to search for strings containing a particular word.

For example, let's say I have a list of folder names (from a laptop) and I want to search for all of the folders that contain the word "Old". Example of results I want:

Folder Name
Old Templates
old Archives
temp - old

However, when I use:

WHERE folder LIKE '%Old%'

Obviously, I get the following results:

Folder Name
Old Templates
old Archives
temp - old
Harold Jenkins
Jerry Oldfield

I just want to use a Regular Expression which searches for the word "Old" at any stage of the string but it searches for only the 3 characters and retrieves the full string.

Just wondering if anyone can help me out - thanks!

I don't understand the question. Can you show the desired result? — Barmar, Nov 23 '21 at 17:35
BTW, `LIKE` is not regular expressions. Regular expressions are matched with `REGEX` or `RLIKE`. — Barmar, Nov 23 '21 at 17:36
Are you asking how to only match whole words, not parts of words, so it won't match `harold`? — Barmar, Nov 23 '21 at 17:37
@Barmar Thanks for your response. Ah okay, I was told you can include a RegEx in the LIKE statement if you open with '[a-z]' for example. Basically, the table at the top is my desired results but I'm getting the results of the bottom table. I want to search for the word "old" but not have it return "old" within "Harold" for example. — Adam Craven, Nov 23 '21 at 17:40
@Barmar Yes, that's what I'm trying to say but I haven't done it very well haha — Adam Craven, Nov 23 '21 at 17:40

score 1 · Accepted Answer · answered Nov 23 '21 at 17:41

1

Generally, the like operator has limited functionality, certainly less than regexp. However, what you need can be done by

Appending a space to the beginning and end of your search string to ensure it's a different word
Doing the same to the target string, so that it will also be matched in the edges:

where ' '+folder+' ' like '% old %'

answered Nov 23 '21 at 17:41

George Menoutis

6,894
3
19
43

Eventually, you can use negated character classes instead of spaces in the pattern as a polyfill for regex word boundaries: `like '%[^a-zA-Z]old[^a-zA-Z]%'`. (or `[^a-zA-Z0-9]`) – Casimir et Hippolyte Nov 23 '21 at 17:50
@Barmar: the question is tagged tsql. – Casimir et Hippolyte Nov 23 '21 at 19:00
Oops, sorry, didn't notice that. I usually follow mysql. – Barmar Nov 23 '21 at 19:03
@Barmar: It doesn't matter, but to make you forgive, take a look at my last answer. – Casimir et Hippolyte Nov 23 '21 at 19:08
Great, thank you! – Adam Craven Nov 23 '21 at 20:59

Regular Expression (RegEx) to find a particular word in any part of a string but has a character limit

1 Answers1