Is there anywaySql Statement without OR codition.

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

Is there anywaySql Statement without OR codition.

Author

Topic

shukla_dh
Starting Member

9 Posts

Posted - 2010-03-20 : 01:49:01

I have one question about simple sql statement.
My Table structure is simple like below.

EmpID EmpName Age
---------------------------
0001 Jhone Meth 32
0002 Alex Smith 53
0003 Julia Robot 34

Now i want to know that is there any way to get result if i search for work like Jhone Robot.

I don't want to use or Statement. I don't want to write like
Select * from Emp where EmpName like '%Jhone%' or EmpName like '%Robot%'.
Is there any way to get desire result without writing "OR" clause.

Thanks,

Dhaval Shukla
.Net Developer

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-20 : 01:55:17

UNION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-20 : 01:55:22

yup possible. using a udf like


DECLARE @Search varchar(1000)
SET @Search='Jhone Robot'
SELECT EmpID, EmpName, Age FROM Emp e
JOIN dbo.ParseValues(@Search,' ')f
ON e.EmpName LIKE '% '+ f.Val + ' %'

ParseValues can be found below

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shukla_dh
Starting Member

9 Posts

Posted - 2010-03-20 : 02:16:16

quote:
Originally posted by visakh16

yup possible. using a udf like

DECLARE @Search varchar(1000)
SET @Search='Jhone Robot'
SELECT EmpID, EmpName, Age FROM Emp e
JOIN dbo.ParseValues(@Search,' ')f
ON e.EmpName LIKE '% '+ f.Val + ' %'

ParseValues can be found below

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi Visakh,

Thanks for your instant reply. I have tried with your given UDF. But now problem is that when i execute sql statement it goes into infinite loop in process part of UDF. Is there any technical correction in UDF part then please provide me that.

Thanks a lot.

Dhaval Shukla
.Net Developer

shukla_dh
Starting Member

9 Posts

Posted - 2010-03-20 : 02:56:47

quote:
Originally posted by shukla_dh

quote:
Originally posted by visakh16

yup possible. using a udf like

DECLARE @Search varchar(1000)
SET @Search='Jhone Robot'
SELECT EmpID, EmpName, Age FROM Emp e
JOIN dbo.ParseValues(@Search,' ')f
ON e.EmpName LIKE '% '+ f.Val + ' %'

ParseValues can be found below

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi Visakh,

Thanks for your instant reply. I have tried with your given UDF. But now problem is that when i execute sql statement it goes into infinite loop in process part of UDF. Is there any technical correction in UDF part then please provide me that.

Thanks a lot.

Dhaval Shukla
.Net Developer

Hi Visakh,

Finally i reached at my aim with your given solution. Thanks a lot. Your solution makes my work easier.

Regards,

Dhaval Shukla
.Net Developer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-20 : 02:57:36

[code]
SELECT * INTO #Temp
FROM
(
SELECT '0001' AS EmpID, 'Jhone Meth'AS EmpName, 32 AS Age UNION ALL
SELECT '0002', 'Alex Smith', 53 UNION ALL
SELECT '0003', 'Julia Robot', 34
)t

DECLARE @Search varchar(1000)
SET @Search='Jhone Robot'

SELECT EmpID, EmpName, Age FROM #Temp e
JOIN dbo.ParseValues(@Search,' ')f
ON e.EmpName LIKE '%'+ f.Val + '%'

DROP TABLE #Temp

output
----------------------------
EmpID EmpName Age
0001 Jhone Meth 32
0003 Julia Robot 34

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shukla_dh
Starting Member

9 Posts

Posted - 2010-03-20 : 07:00:43

quote:

Originally posted by visakh16


  SELECT * INTO #Temp
  FROM
  (
SELECT '0001' AS EmpID, 'Jhone Meth'AS EmpName, 32 AS Age UNION ALL
SELECT '0002', 'Alex Smith', 53 UNION ALL
SELECT '0003', 'Julia Robot', 34
)t


DECLARE @Search varchar(1000)
SET @Search='Jhone Robot'



SELECT EmpID, EmpName, Age FROM #Temp e
JOIN dbo.ParseValues(@Search,' ')f
ON e.EmpName LIKE '%'+ f.Val + '%'

DROP TABLE #Temp

output
----------------------------
EmpID	EmpName	Age
0001	Jhone Meth	32
0003	Julia Robot	34

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi Visakh,

I have same issue which i post earlier but now at this time scenario is just change that instead of "OR" clause now i have to use "AND" clause. I have tried some basic logic but i think you might have some best solution for that.

Regards

Dhaval Shukla
.Net Developer

Hi,

Sorry to disturb you. I got solution of my question. Thanks

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-20 : 11:03:57

[code]Select * from Emp where EmpName like '%Jhone%'
UNION
Select * from Emp where EmpName like '%Robot%' [/code]

by the way, why are you trying to avoid OR?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources