We support "match" for PG directly, though that's full text matching.
For the general form of <somename>(arg1, arg2, ...) typically the func
construct is used unless some more specific construct exists:
func.regexp_matches(email, 'somestring')
The array index you have there, not supported directly yet, here's a recipe,
will give you the as_indexed() function:
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PGArrayIndex
for the ~ operator, that can be done ad hoc using col.op():
table.c.email.op('~')('expression')
put it all together, and you'd get:
session.query(as_indexed(func.regexp_matches(table.c.email,
'expression'))[1].label('domain')).filter(email.op('~')('expression')).distinct()
another way to do the first part, you can use literal_column:
session.query(literal_column("(REGEXP_MATCHES(email,
'@(.+)$'))[1]").label('domain'))...
and of course the @compiles extension lets you build whatever you want but
that's probably not needed here. Hopefully either of those will work !
On Sep 11, 2011, at 2:02 PM, James Hartley wrote:
> I'm needing to extract domain information from stored email addresses --
> something akin to the following:
>
> SELECT DISTINCT (REGEXP_MATCHES(email, '@(.+)$'))[1] AS domain
> FROM tablename
> WHERE email ~ '@.+$'
>
> While I was able to gather the information through session.execute(), I
> didn't find an equivalent filter (?) in the code for regular expression
> related functions. Is this too database specific, or did I miss something?
>
> Thanks, and thank you for SQLAlchemy.
>
> Jim
>
> --
> 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.
--
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.
