• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Search for substring in database field (Postgresql)

 
Sheriff
Posts: 5555
326
IntelliJ IDE Python Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 5555
326
IntelliJ IDE Python Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic