>Vehicle
>Car

>Vehicle

> I have a table "aa" like this:
>
> create table aa
> (
> cell char(10),
> desc char(10)
> )
> insert aa values ("*", "Vehicle");
> insert aa values ("*car*", "Car");
>
>
> I want to achieve the following results:
>
> a) select desc from aa where cell like "blue car"
>>Vehicle
>>Car
>
> b) select desc from aa where cell like "red lorry"
>>Vehicle
>
> But both these statements return 0 rows.
>
> Is it possible to keep wildcards in the database, and get the results I
> show above, using some other syntax ?

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns972AE6782F7FYazorman@127.0.0.1...
> Adrian Dev (adrian.owen@spam.btconnect.com) writes:
> > I have a table "aa" like this:
> >
> > create table aa
> > (
> > cell char(10),
> > desc char(10)
> > )
> > insert aa values ("*", "Vehicle");
> > insert aa values ("*car*", "Car");
> >
> >
> > I want to achieve the following results:
> >
> > a) select desc from aa where cell like "blue car"
> >>Vehicle
> >>Car
> >
> > b) select desc from aa where cell like "red lorry"
> >>Vehicle
> >
> > But both these statements return 0 rows.
> >
> > Is it possible to keep wildcards in the database, and get the results I
> > show above, using some other syntax ?
>
> Three things are wrong:
>
> 1) In SQL Server (and any ANSI compatible databas) * is not a wildcard,
> instead this role is served by %.
> 2) You have the LIKE operators in the wrong order.
> 3) The data type for cell is char(10) which is a fixed-length data type.
> and thus includes trailing spaces. You need to use varchar.
>
> Furthermore, whilc not directly related to this problem, there are two
> more issues with you syntax:
>
> 4) In SQL, strings are delimited with ', not ", which is used to delimit
> identfiers with special characters in them. There is a legacy setting
> in SQL Server which permits the use of ", but you should not use it.
> 5) DESC is a reserverd word in SQL, and must thus be quoted.
>
> All this gives us:
>
>
> create table aa
> (
> cell varchar(10),
> "desc" char(10)
> )
> insert aa values ('%', 'Vehicle');
> insert aa values ('%car%', 'Car');
> go
> select "desc" from aa where 'blue car' LIKE cell
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx