Want Sql Query

Question

Want Sql Query

VishalVasani

Valued Member

Points: 63
More actions
October 8, 2005 at 8:35 am

#187964

I want a sql query that compare the data in a field. This field contain comma in between two word.

example: Skill Field
field value: Oracle,VB,ASP,PHP
field value: Java,VB,PHP,MYSql
field value: Java,VB,Oracle,Asp.net,MSSQL,Asp
want a query to find the a person have skill 'VB' and 'ASP

what 's query for the same
Vishal

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

A select * from myTable where (fieldValue like '%VB%Asp%') or (fieldValue like '%ASP%VB%') should get your results - just make sure it works even when the value starts or ends with either one of these skills or are right next to each other (think it does!!!)...

also, assuming that your db is case insensitive and it is not a consideration here...

(edited)...

**ASCII stupid question, get a stupid ANSI !!!**

Jo Pattyn SSC-Dedicated Points: 32473 More actions · Answer 2

This asks for a normalisation of the database. http://www.tutorialized.com/tutorial/Normalisation/7433

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 3

Vishal,

If your field list looks like this...

field value: Oracle,VB,ASP,PHP

field value: Java,VB,PHP,MYSql

field value: Java,VB,Oracle,Asp.net,MSSQL,Asp

field value: Windows,HASP,Excel,VBA

Consider the following code to keep from listing the 4rh line when looking for ASP and VB...

SELECT *

FROM yourtable

WHERE ','+FieldValue+',' LIKE '%,ASP,%'

AND ','+FieldValue+',' LIKE '%,VB,%'

Of course, neither Sushilla's or my code examples for this problem will ever use the coveted CLUSTERED INDEX SEEK which is why Jo said this column really needs to be normalized.

If you don't have any 3rd party restrictions preventing the conversion of the column and you want to normalize it, post back... I'm sure Jo will be happy to help

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)