Обсуждение: SELECT question (splitting a field)
Hi all, Hopefully a quick question... Why does: nmc=> SELECT 'Y' AS local FROM domains WHERE dom_name='test.com'; local ------- Y (1 row) Work but: nmc=> SELECT 'Y' AS local FROM domains WHERE '@'||dom_name IN ('mkelly@test.com'); local ------- (0 rows) Not work? I am sure I am missing something simple. :) Thanks!! Madi
On 9/4/07, Madison Kelly <linux@alteeve.com> wrote: > I am sure I am missing something simple. :) Yeah... 'mkelly@test.com' <> '@test.com'
On 9/4/07, Madison Kelly <linux@alteeve.com> wrote: > Hi all, > > Hopefully a quick question... > > Why does: > > nmc=> SELECT 'Y' AS local FROM domains WHERE dom_name='test.com'; > local > ------- > Y > (1 row) > > Work but: > > nmc=> SELECT 'Y' AS local FROM domains WHERE '@'||dom_name IN > ('mkelly@test.com'); > local > ------- > (0 rows) > > Not work? because @dom_name isn't IN mkelly@test.com? I think you're looking for pattern matching. select 'Y' as local from domains where dom_name ilike '%test.com%'
Madison Kelly wrote: > nmc=> SELECT 'Y' AS local FROM domains WHERE '@'||dom_name IN > ('mkelly@test.com'); > local > ------- > (0 rows) > > Not work? I don't think IN does what you think it does. It's not a substring-test, but a set test: SELECT 1 WHERE 'x' IN ('a','b','c','x'); SELECT a FROM foo WHERE b IN (SELECT z FROM bar WHERE frozzled<>wamble); You could mess around with substring() and length() or I'd use LIKE. If it's just a domain you're looking for though, might be most efficient to strip the leading part off your value with regexp_replace(). -- Richard Huxton Archonet Ltd
Rodrigo De León wrote: > On 9/4/07, Madison Kelly <linux@alteeve.com> wrote: >> I am sure I am missing something simple. :) > > Yeah... > > 'mkelly@test.com' <> '@test.com' Well now, don't I feel silly. *sigh* Thanks! Madi
Richard Huxton wrote: > Madison Kelly wrote: >> nmc=> SELECT 'Y' AS local FROM domains WHERE '@'||dom_name IN >> ('mkelly@test.com'); >> local >> ------- >> (0 rows) >> >> Not work? > > I don't think IN does what you think it does. It's not a substring-test, > but a set test: > > SELECT 1 WHERE 'x' IN ('a','b','c','x'); > SELECT a FROM foo WHERE b IN (SELECT z FROM bar WHERE frozzled<>wamble); > > You could mess around with substring() and length() or I'd use LIKE. > > If it's just a domain you're looking for though, might be most efficient > to strip the leading part off your value with regexp_replace(). Yeah, that was my problem. I thought I was using the section following the '@'. =/ I've been using Postgres for a while now, but only recently getting into some of the fancier stuff. Until now, I've usually written the program using PgSQL so I could manipulate the data as I needed. Now I am using PgSQL as a backend for a few other applications so I am restricted to using PgSQL to manipulate the data. It's all left me feeling quite n00bish again. ;) I did figure out a query that worked: SELECT 'Y' AS local FROM domains d, users u WHERE u.usr_dom_id=d.dom_id AND u.usr_email||'@'||d.dom_name IN ('mkelly@test.com'); Though this may not be the most efficient. In my case, the 'usr_email' is the LHS of the '@' sign and 'dom_name' is the domain name. If I wanted to use (I)LIKE, how would I have matched just the domain section of 'mkelly@test.com' in 'dom_name'? I'll go read up, now that I've got some key words to search the docs on. Thanks kindly! Madi
Madison Kelly wrote: > SELECT 'Y' AS local FROM domains d, users u WHERE u.usr_dom_id=d.dom_id > AND u.usr_email||'@'||d.dom_name IN ('mkelly@test.com'); > > Though this may not be the most efficient. In my case, the 'usr_email' > is the LHS of the '@' sign and 'dom_name' is the domain name. If I > wanted to use (I)LIKE, how would I have matched just the domain section > of 'mkelly@test.com' in 'dom_name'? Something like: SELECT ... FROM domains d WHERE ('%@' || d.dom_name) LIKE 'mkelly@test.com'; -- Richard Huxton Archonet Ltd