Thread: What is the difference?

This
SELECT 1 WHERE '95b5a221aeba15c' IN (E'\'' || replace('95b5a221aeba15c+fb4e7219ab898ce+b08586cb81059f9','+',''',''') || E'\'') returns no row while

SELECT 1 WHERE '95b5a221aeba15c' IN ('95b5a221aeba15c','fb4e7219ab898ce','b08586cb81059f9') returns a row.

What is the difference between E'\'' || replace('95b5a221aeba15c+fb4e7219ab898ce+b08586cb81059f9','+',''',''') || E'\'' and '95b5a221aeba15c','fb4e7219ab898ce','b08586cb81059f9'?

Cheers,
Chris

---

Get news, entertainment and everything you care about at Live.com. Check it out!

Oluwatope Akinniyi wrote:
> This
> SELECT 1 WHERE '95b5a221aeba15c' IN (E'\'' || replace('95b5a221aeba15c+fb4e7219ab898ce+b08586cb81059f9','+',''',''')
||E'\'') returns no row while 

This returns a string with text '95b...', '...', '...'

> SELECT 1 WHERE '95b5a221aeba15c' IN ('95b5a221aeba15c','fb4e7219ab898ce','b08586cb81059f9') returns a row.

This is three values.

You could either use strpos() on the raw string (as long as you know you
 won't get partial/multiple matches). OR split the string to an array:


SELECT 1
WHERE
'95b5a221aeba15c' = ANY(
  string_to_array(
    '95b5a221aeba15c+fb4e7219ab898ce+b08586cb81059f9',
    '+'
  )
);

--
  Richard Huxton
  Archonet Ltd

On Fri, 6 Mar 2009 09:50:27 +0100
Oluwatope Akinniyi <topeakinniyi@hotmail.com> wrote:

>
> This
> SELECT 1 WHERE '95b5a221aeba15c' IN (E'\'' ||
> replace('95b5a221aeba15c+fb4e7219ab898ce+b08586cb81059f9','+',''',''')
> || E'\'') returns no row while
>
> SELECT 1 WHERE '95b5a221aeba15c' IN
> ('95b5a221aeba15c','fb4e7219ab898ce','b08586cb81059f9') returns a
> row.
>
> What is the difference between E'\'' ||
> replace('95b5a221aeba15c+fb4e7219ab898ce+b08586cb81059f9','+',''',''')
> || E'\'' and '95b5a221aeba15c','fb4e7219ab898ce','b08586cb81059f9'?

You assume that the statement is interpreted twice.
The first is comparing your needle with an array/recordset of just
one element
"'95b5a221aeba15c','fb4e7219ab898ce','b08586cb81059f9'"

--
Ivan Sergio Borgonovo
http://www.webthatworks.it