performance of Where In ABC - 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

SQL Server 2005 Forums

Transact-SQL (2005)

performance of Where In ABC

Author

Topic

stroek
Starting Member

18 Posts

Posted - 2009-04-27 : 04:08:17

Hello All
I have the next where-statement in my query
Is this the best whay to check if the first character of my contact is a alphabethic character, and return the selection?

If I want to select a contact (ex. "4 ever company"), then I use 'Other' as @OrderChar

select * from ContactGroup
where
(@OrderChar='Other' OR ContactGroup.Bedrijfsnaam like @OrderChar+'%')
and (@OrderChar<>'Other' OR Left(ContactGroup.Bedrijfsnaam,1) not in ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z') )

Regards Stroek

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2009-04-27 : 04:45:59

Hi,

select * from ContactGroup
where
(@OrderChar='Other' OR ContactGroup.Bedrijfsnaam like @OrderChar+'%')
and (@OrderChar<>'Other' OR PATINDEX('[0-9]%',ContactGroup.Bedrijfsnaam)=1)

Look out for patindex i have used in place of ur hard-coded aplhabet search.

Iam a slow walker but i never walk back

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-27 : 05:01:06

quote:
Originally posted by dineshrajan_it

Hi,

select * from ContactGroup
where
(@OrderChar='Other' OR ContactGroup.Bedrijfsnaam like @OrderChar+'%')
and (@OrderChar<>'Other' OR PATINDEX('[0-9]%',ContactGroup.Bedrijfsnaam)=1)

Look out for patindex i have used in place of ur hard-coded aplhabet search.

Iam a slow walker but i never walk back

It should be

select * from ContactGroup
where
(@OrderChar='Other' OR ContactGroup.Bedrijfsnaam like @OrderChar+'%')
and (@OrderChar<>'Other' OR PATINDEX('^[a-zA-Z]%',ContactGroup.Bedrijfsnaam)=1)

Madhivanan

Failing to plan is Planning to fail

stroek
Starting Member

18 Posts

Posted - 2009-04-28 : 09:06:53

tks
I will try

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources