Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 3rd, 2008, 05:46 AM
Authorized User
 
Join Date: Sep 2003
Location: Delhi, Delhi, India.
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to prabodh_mishra
Default Case sensitive search

I need to identify rows with the presence of any upper case character in my varchar(20) column after first character that is in substring(<column name>, 2, len(<column name>)-1).

I've tried COLLATE SQL_Latin1_General_CP1_CS_AS but without much of the success.

Any clue?

Thanks,
Prabodh
__________________
Prabodh
Reply With Quote
  #2 (permalink)  
Old June 3rd, 2008, 06:30 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Try using Latin1_General_BIN... guaranteed to be case sensitive...

--Jeff Moden
Reply With Quote
  #3 (permalink)  
Old June 5th, 2008, 05:40 AM
Authorized User
 
Join Date: Sep 2003
Location: Delhi, Delhi, India.
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to prabodh_mishra
Default

Thanks it works. Along with that I've explored another way of casting into varbinary.


Prabodh


Prabodh
Reply With Quote
  #4 (permalink)  
Old June 5th, 2008, 10:04 AM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Two way street here, Prabodh... tell us more details about your other way of casting into varvinary.

--Jeff Moden
Reply With Quote
  #5 (permalink)  
Old June 6th, 2008, 02:21 AM
Authorized User
 
Join Date: Sep 2003
Location: Delhi, Delhi, India.
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to prabodh_mishra
Default

Sure Jeff. Casting into varbinary actually makes a comparision case sensitive.
Following code will pick all the rows from Authors table where city column have occurrences of CAPS letters after first char. The idea is to put up a self join with one table having city field (or any desired one) converted into lower case and match it with original value of other table.

select auth1.* from authors auth1
join authors auth2
on auth1.au_id = auth2.au_id
where cast((substring(auth1.city, 2, len(auth1.city)-1)) as varbinary(20))
    <> cast((lower(substring(auth2.city, 2, len(auth2.city)-1))) as varbinary(20))



Prabodh
Reply With Quote
  #6 (permalink)  
Old June 6th, 2008, 07:22 AM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Thanks, Prabodh,

I think you'll find there's a pretty stiff performance penalty for doing conversions like that instead of just using a collation option.

Now you've got me cursious... why aren't you checking the first letter of the cities?

--Jeff Moden
Reply With Quote
  #7 (permalink)  
Old June 10th, 2008, 04:54 AM
Authorized User
 
Join Date: Sep 2003
Location: Delhi, Delhi, India.
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to prabodh_mishra
Default

It was just an example and the actual requierment is something else. Since the target table isn't going to have high number of rows and usage are also minimal I guess I can afford it for now. I'll take care of your performance hint anyway.



Prabodh
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to check the password field for case-sensitive zubash73 ASP.NET 2.0 Basics 4 November 27th, 2016 02:47 AM
Case Sensitive hymavathy_kr VB Databases Basics 1 August 10th, 2007 06:43 AM
making login case sensitive with mysql database crmpicco MySQL 0 August 18th, 2006 05:48 AM
How to do case sensitive comparison in SQL SERVER leo_vinay Classic ASP Databases 2 January 24th, 2005 01:46 PM
search string either Upper case or lower case rylemer Beginning VB 6 3 March 24th, 2004 04:23 PM



All times are GMT -4. The time now is 11:30 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.