Using a Like condition

[jeepxo]

It's a pretty simple question but I'm stumped as to why I can't get a result.

We have a database with various software titles in it. We have a field for the operating system. If the software can be run on multiple OSes then they are listed comma separated. For Example Win 9x, Win NT, Mac OS 9, Mac OS X

I've been asked to create a list of all the software titles that will run under Win NT.

I thought that

Select Title from SoftwareList where operatingSystem like '%XP%'

Would give me this list, however, I get no matches. I know that there are entries for Win XP.
Am I missing something?

"Every day is like a precious gift, you have to make it count" James Birrell 1993-2001

 

[royjimenez]

hi!

you should make a new table to keep OS data, 'cause u will avoid more problems later.

Regards

The life is too short to cry and long enough to try it... God bless us.

 

[SQLSister]

Comma separated lists in a field are very POOR database design. Your query looks correct except you said you were searching for NT and you used XP in the query string. But this type of query will never be efficient because using like with % as the first character means that indexes cannot be used. First step is a database redesign.

 

[Golom]

Remove the quotes as in

Select Title from SoftwareList where operatingSystem like %XP%

 

[SQLSister]

Golum, the quotes are necessary.

 

[jeepxo]

It's not my database so I can't redesign it.
There are only 500 records, and it is unlikely that it will grow significantly larger so being inefficient in the search isn't a big deal, but I do appreciate those comments.

Yes, I had a brain fart when I posted the question, I'm searching for XP not NT.

"Every day is like a precious gift, you have to make it count" James Birrell 1993-2001