Sql how to

Question

Post reply

Sql how to

sameer-367521

SSC Rookie

Points: 41
More actions
September 20, 2006 at 9:03 am

#174233

ok, i have a bit of a tricky problem that I am trying to avoid using stored procedure for..but I am rapidly approching my wits end with T-Sql.
Here is a script
CREATE TABLE [dbo].[KeyWordTest] (
[ID] uniqueidentifier DEFAULT newid(),
[AccessField] nvarchar(128))
INSERT INTO KeyWordTest (AccessField) VALUES ('Appels, Oranges')
INSERT INTO KeyWordTest (AccessField) VALUES ('')
INSERT INTO KeyWordTest (AccessField) VALUES ('Appels, Oranges, Bananas')
END
I want to create a query where if I have a where clause which looks like this ..
select * from
KeyWordTest where AccessField = '%Appels%' and '%Oranges%'
only the rows with 'Appels, Oranges' and not the row with 'Appels, Oranges, Bananas'
and similarly if I say the following
select * from
KeyWordTest where AccessField = '%Appels%' and '%Oranges%' and '%Bananas%'
the row with 'Appels, Oranges, Bananas' should come up and not the row with 'Appels, Oranges'.

Any thoughts.

Viewing 15 posts - 1 through 15 (of 18 total)

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

First thought is - don't do it like that.

Why do you have 'apples, oranges, bananas' as a single string in a single column?

This is where your problem lies. Would it be possible to instead store each fruit as it's own row? Try it and see if you can get your query to work.

If this for some reason isn't the way to go, then from what I can read from your query, is that you need relational division.

The onecolumn-string-of-fruits, however, will make every attempt to manipulate the data a kludge, at it's best. (ie pre-work like split functions etc..)

/Kenneth

sameer-367521 SSC Rookie Points: 41 More actions · Answer 2

No unfortunatly i have to keep them in one row. like i described in my query. I am not sure what you meant by relational diviosn..but i look forwrd to more posts on this matter.

Sameer

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 3

Can you keep them in different columns? You are breaking the basic rule of Relational database Normalization(Normal Form&nbsp by have multi value column.

Thanks

Sreejith

sameer-367521 SSC Rookie Points: 41 More actions · Answer 4

I understand that..but DOD requirements dictate this type of storage.

Senthil T Hall of Fame Points: 3367 More actions · Answer 5

May be you can create a temp table @keywords with an id column and an accessfield column. Split the comma seperated accessfield string from the keywordtest table and insert one row for each value. Then perform the search on this temp table. May not be efficient if you have too many rows in the keywordtest table.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

DOD eh? Haven't they heard of ISO standards for data? Sheesh! Where's Joe Celko when you need a good rant from him? He'd have a bloody fit.

Here's the "brute force" method...

SELECT *

FROM dbo.KeyWordTest

WHERE AccessField LIKE '%Apples%'

AND AccessField LIKE '%Oranges%'

AND AccessField NOT LIKE '%Bananas%'

SELECT *

FROM dbo.KeyWordTest

WHERE AccessField LIKE '%Apples%'

AND AccessField LIKE '%Oranges%'

AND AccessField LIKE '%Bananas%'

Now... in order to come up with something a bit more sophisticated, we probably need to know how many different items the AccessField can contain... any thoughts there, Sameer?

--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)

Vladan SSC-Insane Points: 22072 More actions · Answer 7

What's DOD? The only thing that comes into my mind is Dead On Delivery... which somehow seems to be appropriate as I read what you are asked to do. We'll try to come up with something, but most probably it will not perform well if you have many rows in this table.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Ninja's_RGR'us

SSC Guru

Points: 294069

September 21, 2006 at 7:08 am

#661717

DOD > Dead on Dear Vladan .

sameer-367521 SSC Rookie Points: 41 More actions · Answer 9

DOD = Department of Defense. It sets standards just like ISO etc..

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

So they think they overthrow data modeling standards???

I wouldn't like to be in your position right now... I hope this is the only sample of this problem you'll find !!

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

That's why I was going on about the ISO standards... they were supposed to be a key in developing the ISO standards for SQL and Data. Guess they forgot everything they knew when Celko left the ISO committee

--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)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 12

Sameer,

You all set with the brute force method I gave you or do you need something more sophisticated? If the latter, I need some more details 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)

sameer-367521 SSC Rookie Points: 41 More actions · Answer 13

Yes indeed I am all set with the brute force method. Actually more like a stored proc. While I work on implementing or perhaps reinventing the data rules within standards of course.

Vladan SSC-Insane Points: 22072 More actions · Answer 14

Thanks Rémi 🙂

That Ninja part is a new addition, isn't it? I didn't see your posts for a while though, so it may be some time since you've renamed yourself.

Oh, Department of Defense. Well, then it isn't too surprising. In my country, they buy parachutes that don't always open, fire guns in an airplane by mistake etc. No wonder they don't know how to design a DB.