How to avoid function in where clasue

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

SQL Server 2005 Forums

Transact-SQL (2005)

How to avoid function in where clasue

Author

Topic

nvakeel
Yak Posting Veteran

52 Posts

Posted - 2008-01-29 : 10:12:18

Hello,

I have a query select * from tbl1 where ltrim(rtrim(p)) in ( 'r','pd','spd')

I need to avoid using function in the where clause since it is causing perfomance issue.
need your help folks..
Thanks,
Nag

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-29 : 10:13:34

If column p is varchar, there is no need to use trim() functions.

Also, functions cause performance issues provided you have index on that column, since it won't be used in this case.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

nvakeel
Yak Posting Veteran

52 Posts

Posted - 2008-01-29 : 10:32:50

the column 's' is char datatype

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-29 : 10:44:05

change it to varchar. that's why varchar exists.... hope your app doesn't break though

you could do
p in ( 'r' + replicate(' ', charColumnLength-1) ,'pd' + replicate(' ', charColumnLength-2),'spd' + replicate(' ', charColumnLength-3))

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 10:52:49

See this demonstration

DECLARE	@Table1 TABLE (Col1 CHAR(6), Col2 VARCHAR(6))

INSERT	@Table1
SELECT	'r', 'r' UNION ALL
SELECT	' r', ' r' UNION ALL
SELECT	'r  ', 'r  ' UNION ALL
SELECT	'pda', 'pda' UNION ALL
SELECT	'pd', 'pd' UNION ALL
SELECT	'spd', 'spd'

SELECT	*,
	'_' + Col1 + '_',
	'_' + Col2 + '_'
FROM	@Table1
WHERE	Col1 IN ('r', 'pd', 'spd')

SELECT	*,
	'_' + Col1 + '_',
	'_' + Col2 + '_'
FROM	@Table1
WHERE	Col2 IN ('r', 'pd', 'spd')

E 12°55'05.25"
N 56°04'39.16"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-29 : 11:58:19

quote:
Originally posted by harsh_athalye

If column p is varchar, there is no need to use trim() functions.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

If there is TRAILING whitespace, then yes "IN" and "=" will ignore the trailing whitespace. But, if it at the begining then the whitespace has meaning (as demonstrated by Peso's example).

Nvakeel, I just got in to work so maybe I'm not thinking clearly, but I don't know of a good way to avoid using a function in the where clause. You might want to look at cleaning up the data first..?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2008-01-29 : 12:18:50

[code]
select T.*
from tbl1 AS T
INNER JOIN (
SELECT REPLICATE(' ', n) + v AS v
FROM ( SELECT 'r' AS v UNION ALL SELECT 'pd' UNION ALL SELECT 'spd' ) AS V
CROSS JOIN Numbers
WHERE n BETWEEN 0 AND 10
) AS V
ON T.p = V.v
[/code]

nvakeel
Yak Posting Veteran

52 Posts

Posted - 2008-01-31 : 13:27:34

million thanks to all of you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources