Sql Query Exact Pharse issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sql Query Exact Pharse issue

Author

Topic

visa123
Yak Posting Veteran

54 Posts

Posted - 2010-12-01 : 02:57:08

Hi

In my table the below multiple values with comma separated

Asset Protection Lead,Corporate 1
Asset Protection Lead Post,Corporate 2
Corporate,Asset Protection Lead 3
Coporate 4
Asset Protection Lead 5

Here i have to retrieve Asset Protection Lead and Corporate.
So i have used the below query.

SELECT * FROM table1
WHERE CONTAINS(field1 ,'"Asset Protection Lead"OR"Corporate"')

So it should retrieve

Asset Protection Lead,Corporate 1
Corporate,Asset Protection Lead 3
Coporate 4
Asset Protection Lead 5

But i am retrieving Asset Protection Lead Post also.

can u please any help me for this issue

Visa.G

chadmat
The Chadinator

1974 Posts

Posted - 2010-12-01 : 03:07:29

You should use IN, not CONTAINS

SELECT * FROM table1
WHERE field1 IN ("Asset Protection Lead","Corporate"')

-Chad

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-01 : 03:09:04

Why are using using CONTAINS?

SELECT * FROM table1
WHERE field1 ='Asset Protection Lead' or field1='Corporate'

Madhivanan

Failing to plan is Planning to fail

visa123
Yak Posting Veteran

54 Posts

Posted - 2010-12-01 : 03:14:58

Hi chadmat and madhivanan

Thanks for your replies

field1 is comma separated values.if i use both the queries i can retrieve one value.like below

Coporate 4
Asset Protection Lead 5

But i need the combination also,like below

Asset Protection Lead,Corporate 1
Corporate,Asset Protection Lead 3
Coporate 4
Asset Protection Lead 5

can anyone help me for this?

Thanks

Visa.G

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-01 : 04:28:28

1 Read about Normalization
2 You need to split the column value and search for it

Madhivanan

Failing to plan is Planning to fail

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-01 : 05:03:40

quote:
Originally posted by madhivanan

You need to split the column value and search for it

Madhi,

But how to do it ?
I am not even getting idea about this.

Vaibhav T

If I cant go back, I want to go fast...

visa123
Yak Posting Veteran

54 Posts

Posted - 2010-12-01 : 06:10:39

Hi

any one guide me for my issue ?

Thanks

Visa.G

visa123
Yak Posting Veteran

54 Posts

Posted - 2010-12-01 : 09:03:41

Hi ALL

i got the solution

DECLARE @param NVARCHAR(MAX)
SET @param='Asset Protection Lead,Coporate'
Select field1 from table1
where (',' + replace(@param, ', ', ',') + ',')
like ('%,' + field1 + ',%')

This is the method do it.

Thanks to all to see this thread!may be help for anyone in future.

Visa.G

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources