Michael,
I hope I'm not misunderstanding what your trying to achieve, but isn't
a combination of like and not like want you want to do here? As in:
create table test (
t varchar2(255)
);
insert into test values ('AA123');
insert into test values ('A0123');
select * from test where t like 'A%' and t not like 'AA%';
-> A0123
Or do you want to match "Any character, but only once" rather than "A"
explicitly? The regex you posted ('A[0-9]+) looks like it's really
'A', you're looking for (unless that's just simplified for testing
purposes.
-sas
On Sep 20, 4:38 pm, Michael Hipp <[email protected]> wrote:
> On 9/17/2010 10:12 AM, Michael Bayer wrote:
>
>
>
>
>
>
>
> > On Sep 17, 2010, at 10:58 AM, Michael Bayer wrote:
>
> >> On Sep 17, 2010, at 9:14 AM, Michael Hipp wrote:
>
> >>> On 9/14/2010 2:23 PM, Michael Hipp wrote:
> >>>> Is it possible to use a regexp in a like() clause? Or some other way to
> >>>> achieve
> >>>> something similar?
>
> >>> Can anyone suggest an approach to search a field with a regexp?
>
> >> if you were using Postgresql, you could use
> >> somecolumn.op("~")(someregexp)
>
> >>http://www.postgresql.org/docs/8.4/interactive/functions-matching.htm...
>
> >> cant speak for other platforms though you'd have to consult their
> >> documentation.
>
> > PG also supports MATCH since I see we have some unit tests for that, i.e.
> > column.match(other). I'm not seeing offhand in PG's docs what it
> > interprets the "MATCH" operator as, i.e. is it a "~", "SIMILAR TO", not
> > sure.
>
> > I guess the reasons I've never had interest in regexp matching in databases
> > are:
>
> > 1. its always bad to search through tables without being able to use indexes
> > 2. if you're needing to dig into text, it suggests the atoms of that text
> > should be represented individually in their own column (i.e. normalize)
> > 3. no really, I'm doing flat out full text searching on documents and don't
> > want to reinvent. Well then I'd use a full blown text extension
> > (http://www.postgresql.org/docs/8.3/static/textsearch.html) or a separate
> > search engine.
>
> Thanks for the good suggestions on this. My need is pretty simple. I have a
> column that contains values like this:
>
> A100
> AA309
> B101
>
> I need something to find all the rows that look like Axxx while excluding
> those
> that look like AAxxx. The LIKE operator doesn't seem to be able to do that. I
> am using PostgreSQL so the ~ operator may be my best bet.
>
> Scratch that ... found this message:
> http://www.mail-archive.com/[email protected]/msg18598.html
> which says I should be able to do a 'SIMILAR TO' construct which is perhaps
> somewhat more lightweight than a full regexp.
>
> Thanks,
> Michael
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
