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.
| Author |
Topic |
|
funketekun
Constraint Violating Yak Guru
491 Posts |
 Posted - 2006-12-14 : 15:52:31
|
| After reading performance tuning articles and stuffs. Im getting confussed about writing my queries. One point says don't use 'not in' instead use 'not exists'. But looks i cant implement that in my query.I'm trying to get only the decimal numbers. not the fractions.my question is if this is the best query? Inputs will be appreaciated.declare @table table (tableid int identity, ad_str1 varchar(100))insert @table select '2 NORTHPORT AVENUE' union all select '22 NORTHPORT AVENUE' union all select '233 NORTHPORT AVENUE' union all select '2433 NORTHPORT AVENUE' union all select '2 1/2 NORTHPORT AVENUE' union all select '22 1/3 NORTHPORT AVENUE' union all select '233 1/4 NORTHPORT AVENUE' union all select '2433 1/8 NORTHPORT AVENUE' union all select '2a NORTHPORT AVENUE' union all select '22a NORTHPORT AVENUE' union all select '233a NORTHPORT AVENUE' union all select '2433a NORTHPORT AVENUE' union all select '2 a NORTHPORT AVENUE' union all select '22 a NORTHPORT AVENUE' union all select '233 a NORTHPORT AVENUE' union all select '2433 a NORTHPORT AVENUE' union all select 'a NORTHPORT AVENUE' union all select '15 e NORTHPORT AVENUE' union all select '15 n NORTHPORT AVENUE' union all select '15 s NORTHPORT AVENUE' union all select '15 w NORTHPORT AVENUE' union all select '15 c & k north avenue' union all select '1/3 NORTHPORT AVENUE' union all select '93 H ROAD' select tableid, ad_str1, left(ad_str1, charindex(' ', ad_str1))from @table where left(ad_str1, charindex(' ', ad_str1)) not in ('1/2','1/3','1/4','1/8','2/3','3/4')and isnumeric (left(ad_str1, charindex(' ', ad_str1))) = 1 |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-12-14 : 15:55:04
|
| IN is fine in your query, EXISTS is preferable when you are using a subquery, but you are just using a fixed list of values not a subquery. |
 |
|
|
|
|
|
|
|
get numbers in front of street.