• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Search for substring in database field (Postgresql)

 
Tim Cooke
Sheriff
Pie
Posts: 3210
142
Clojure IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:

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
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I tested with a different table name because I wasn't paying attention to what you used .



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.
 
Tim Cooke
Sheriff
Pie
Posts: 3210
142
Clojure IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic