Search for substring in database field (Postgresql)

[Tim Cooke]

Hello folks, and a Happy New Year to you!

I'm looking for some SQL query help for something I'm working on just now with a Postgresql database. I have a database field that contains arbitrary text and I need to select rows where that text contains a URL with a certain pattern. The pattern may not be the full URL so some rules are required. I'll start by illustrating with an example:

Pattern: "/user/123"

Required text match:
"/user/123" -> true
"some text /user/123" -> true
"some text example.com/user/123" -> true
"some text example.com/user/123 some other text" -> true
"some text example.com/user/123
some other text" -> true
"some text example.com/user/123/Tim Cooke" -> true
"some text example.com/user/1234" -> false
"some text example.com/user/1234 some other text" -> false
"some text example.com/user/1234/The Moose" -> false
"some text example.com/user/1234/The Moose some other text" -> false

Currently I have the following query:
select * from my_table t where t.text similar to '%/user/123[/ \r\n]%';
This query finds the pattern if it's followed by a forward slash '/', whitespace ' ', newline '\r', or carriage return '\n'. But it does not find the pattern if the pattern is the very last text in the field. So the following texts do not produce a match:

"/user/123" -> true
"some text /user/123" -> true
"some text example.com/user/123" -> true

Any suggestions of how I might achieve this?

[Jeanne Boyarsky]

I tested with a different table name because I wasn't paying attention to what you used .

create table substring_tester (value varchar(255)); insert into substring_tester values('/user/123'); insert into substring_tester values('some text /user/123'); insert into substring_tester values('some text example.com/user/123'); insert into substring_tester values('some text example.com/user/123 some other text'); insert into substring_tester values('ome text example.com/user/123 \nsome other text'); insert into substring_tester values('some text example.com/user/123/Tim Cooke'); insert into substring_tester values('some text example.com/user/1234'); insert into substring_tester values('some text example.com/user/1234 some other text'); insert into substring_tester values('some text example.com/user/1234/The Moose');

With my table/column name, the following SQL does what you need. I checked the explain plan and postgres is smart enough to look at the value in one pass so this doesn't take twice as long to run.
select * from substring_tester t where t.value similar to '%/user/123[/ \r\n]%' or t.value like '%/user/123';

[Tim Cooke]

Oh my goodness. So so simple. You know when you stare at a thing for too long you stop seeing it? Well, that. I was getting too hung up on the Regex to notice I didn't need it.

Thanks Jeanne.