Re: [GENERAL] SELECT question (splitting a field)

2007-09-04 Thread Richard Huxton 

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 ('[EMAIL PROTECTED]');


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 '[EMAIL PROTECTED]' in 'dom_name'?


Something like:

SELECT ... FROM domains d WHERE ('%@' || d.dom_name) LIKE '[EMAIL PROTECTED]';

--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] SELECT question (splitting a field)

2007-09-04 Thread Madison Kelly 

Richard Huxton wrote:

Madison Kelly wrote:
nmc=> SELECT 'Y' AS local FROM domains WHERE '@'||dom_name IN 
('[EMAIL PROTECTED]');

 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 ('[EMAIL PROTECTED]');


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 '[EMAIL PROTECTED]' in 'dom_name'?


I'll go read up, now that I've got some key words to search the docs on.

Thanks kindly!

Madi

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] SELECT question (splitting a field)

2007-09-04 Thread Madison Kelly 

Rodrigo De León wrote:

On 9/4/07, Madison Kelly <[EMAIL PROTECTED]> wrote:

   I am sure I am missing something simple. :)


Yeah...

'[EMAIL PROTECTED]' <> '@test.com'


Well now, don't I feel silly. *sigh*

Thanks!

Madi

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] SELECT question (splitting a field)

2007-09-04 Thread Richard Huxton 

Madison Kelly wrote:
nmc=> SELECT 'Y' AS local FROM domains WHERE '@'||dom_name IN 
('[EMAIL PROTECTED]');

 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

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] SELECT question (splitting a field)

2007-09-04 Thread Scott Marlowe 
On 9/4/07, Madison Kelly <[EMAIL PROTECTED]> 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
> ('[EMAIL PROTECTED]');
>   local
> ---
> (0 rows)
>
>Not work?

because @dom_name isn't IN [EMAIL PROTECTED]

I think you're looking for pattern matching.

select 'Y' as local from domains where dom_name ilike '%test.com%'

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] SELECT question (splitting a field)

2007-09-04 Thread Rodrigo De León 
On 9/4/07, Madison Kelly <[EMAIL PROTECTED]> wrote:
>I am sure I am missing something simple. :)

Yeah...

'[EMAIL PROTECTED]' <> '@test.com'

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] SELECT question (splitting a field)

2007-09-04 Thread Madison Kelly 

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 
('[EMAIL PROTECTED]');

 local
---
(0 rows)

  Not work?

  I am sure I am missing something simple. :)

Thanks!!

Madi

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly