| Author |
Topic |
|
karuna
Aged Yak Warrior
582 Posts |
 Posted - 2002-02-09 : 02:26:55
|
| Is it possible to compare the values given by the user with the record in the table maintaing the case sensitiveness....When I tried the following query:select * from tbl1 where username='Karuna'I got the result with the usernames Karuna and karuna...But I want the result only with username Karuna...What I understood from the BOL is, the case sentiveness issue has to be taken care while the installation of SQL SERVER,And it will be applicable to all objects in the server...Its not a good solution i think...Is there any other way to retrieve the records using comparison while maintaining case sensitivity...KarunakaranDon't wait for things to happen,Make them to happen... |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-02-09 : 03:04:27
|
| Check this link http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=9219 . Rob teaches xactly what you are looking for.--------------------------------------------------------------Dont Tell God how big your Problem is , Tell the Problem how Big your God is |
 |
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2002-02-09 : 03:28:20
|
| Hi Nazim,I tried that one..but its not producing the desired result...select * from recruiterinfo where convert(varbinary(150),firstname)=convert(varbinary(150),'Karunakaran')The above query didnt produced any results while the query below gave me 2 records. one with firstname karunakaran and the other with firstname KarunakaranSELECT * FROM recruiterinfo where firstname='Karunakaran'The Datatype for lastname is nvarchar with length 150...I feel like i'am making mistake some where....KarunakaranDon't wait for things to happen,Make them to happen... |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-02-09 : 07:35:20
|
| if you are using Sql 2000 . this should help you.CREATE function DetermineAscii( @nstring varchar(300)) ASset nocount ondeclare @position intdeclare @munivalstr varchar(600)select @position=1select @munivalstr=''WHILE @position <= DATALENGTH(@nstring)BEGIN select @munivalstr=@munivalstr+ltrim(str(ascii(CONVERT(char(1), SUBSTRING(@nstring, @position, 1))))) SELECT @position = @position + 1ENDreturn @munivalstr----select * from recruiterinfo where DetermineAscii(firstname)=DeterMineAscii('Karunakaran')--------------------------------------------------------------Dont Tell God how big your Problem is , Tell the Problem how Big your God is |
|
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2002-02-09 : 07:46:31
|
| Hi Nazim,I'am using SQL 7.0 on Windows NT....I created a table test from query analyser, when i executed that query it worked fine for that.But the same query is not working the tables i created test1 from Enterprise Manager.select * from test where convert(varbinary(20),lname)=convert(varbinary(20),'Karuna')select * from test1 where convert(varbinary(20),lname)=convert(varbinary(20),'Karuna')KarunakaranDon't wait for things to happen,Make them to happen...Edited by - karuna on 02/09/2002 08:01:02 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-09 : 08:21:15
|
| The problem is than nvarchar is a double-byte storage, and you need to pass the string to the CONVERT function that way:select * from recruiterinfo where convert(varbinary(150),firstname)=convert(varbinary(150),N'Karunakaran')See if that works. |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-02-09 : 08:28:56
|
| Am Sorry. not able to figure out what could be the problem.Except that using Enterprise Manager is never a good idea. it is inconsitent at times.you can create a temp table using select.. into. .drop your original table and rename the temp table to your original table. and try Rob's solution.if your data is limited to checking proper case words. this should do it.select * from recruiterinfo where ascii(firstname)=Ascii('Karunakaran') and convert(varbinary(150),substring(firstname,2,len(firstname))=convert(varbinary(150),'arunakaran')HTH--------------------------------------------------------------Dont Tell God how big your Problem is , Tell the Problem how Big your God is |
|
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2002-02-09 : 08:36:20
|
| Thanks a Lot Rob...select * from recruiterinfo where convert(varbinary(150),firstname)=convert(varbinary(150),N'Karunakaran')This query worked well as the way I needed....Thank you Guys...KarunakaranDon't wait for things to happen,Make them to happen... |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-02-09 : 08:38:56
|
Rob, i have often wondered about the Use of N. Can you explain me more on that. just checked BOL too couldnt locate much info.quote:
select * from recruiterinfo where convert(varbinary(150),firstname)=convert(varbinary(150),N'Karunakaran')
--------------------------------------------------------------Dont Tell God how big your Problem is , Tell the Problem how Big your God is |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-09 : 09:08:35
|
| It's a shorthand way of saying:SELECT Convert(nvarchar(10), 'Hello') equivalent to SELECT N'Hello'The problem with nvarchar is that you have to remember it uses 2 bytes for each character. So if you have a 10 character string, and a 10 character national/unicode string, they store as 10 and 20 bytes respectively. This isn't too bad, unless you're CONVERTing nvarchar to varbinary (or another type), like we are here. Nvarchar(50) can't be converted to varbinary(50) without possibly losing some bytes. |
|
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2002-02-09 : 09:20:05
|
Thanks for the explanation Rob.quote:
Except that using Enterprise Manager is never a good idea. it is inconsitent at times.
Nazim,I have created all my tables from Enterprise Manager only...Should I drop the tables and recreate it from Query Analyser???KarunakaranDon't wait for things to happen,Make them to happen... |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-02-09 : 09:51:03
|
| Nah! i didnt mean that Karuna. i suggested dropping this particular table coz as u told it acted little weird .--------------------------------------------------------------Dont Tell God how big your Problem is , Tell the Problem how Big your God is |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-09 : 09:54:28
|
quote:
Nazim,I have created all my tables from Enterprise Manager only...Should I drop the tables and recreate it from Query Analyser???
NO!If you can create tables from query analyzer and feel comfortable doing it, then do it that way. Pick a method and stick with it. Don't drop tables and recreate them unless they are completely hosed up and don't work (you would've done it by now if that was the case).I use EM to create tables quite often. The only "problem", if you can call it that, are the default settings that EM will use (nullable columns, index names, etc.) Sometimes these settings aren't obvious and can cause your table to act in unexpected ways. Just keep it in mind if you see behavior like that. |
|
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2002-02-09 : 10:08:25
|
As of now I didnt faced any problem with tables.They are working fine...Always I have problems with my queries...And I always find answers here  ...Thank you very much guys...And I'am comfortable in both QueryAnalyser and EnterpriseManager in creating tables I prefer EM because its more easy and I'am bit lazy to type in QA  ...KarunakaranDon't wait for things to happen,Make them to happen...Edited by - karuna on 02/09/2002 10:12:53 |
|
|
|
|
Possible to compare with Case Sensitiveness ???