[thelist] SQL question

[Joshua Olson]

----- Original Message -----
From: "rudy" <r937 at interlog.com>
Sent: Wednesday, January 22, 2003 3:45 PM


> > Could you do:
> > ...
> > where manFirst+manLast+womFirst+womLast like '$searchname'
>
> in practice, i don't see why not, assuming you concatenate properly -- for
> example, in some databases you have to use & and in others || and in yet
> another CONCAT() <sigh />
>
> however, you could theoretically have unexpected results, for example --
and
> you can tell i'm making this up -- if manFirst='Gregor' and
> manLast='Doncaster' and you were searching for '%gordon%'

You may be able to avoid that by insertion of spaces.  Also, if the database
doesn't allow concatenation of NULL values, then you may have to do
something more like this (using SQL Server syntax)

WHERE ' ' + COALESCE(manFirst, '') + ' ' + COALESCE(manLast, '') + ' ' +
COALESCE(womFirst, '') + ' ' + COALESCE(womFirst, '') + ' ' LIKE '
$searchname '

-joshua