| Author |
Topic |
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
 Posted - 2002-09-20 : 09:17:24
|
select 1 where 'a' = 'a ' Can someone remind me how I make this not return one?Jay White{0} |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-09-20 : 09:20:20
|
| LIKE (except it's not very good if the second string contains LIKE metachars)Or you could append a non-space character to the string on each side of the comparison.Edited by - Arnold Fribble on 09/20/2002 09:30:55 |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-09-20 : 09:31:40
|
hum... lemme try againwhy is is that on one server ...select substring('hello',0,2) returns 'h'but on another server the same thing returns NULL ???edit: Did SUBSTRING change between SQL7 and SQL2K?Jay White{0}Edited by - Page47 on 09/20/2002 09:34:07 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-20 : 09:34:20
|
Did you check the compatibility level?SET ANSI_PADDING doesn't affect it either, in case you haven't tried it yet.Casting each value as varbinary is the best I came up with so far. Using Datalength to compare them might also help (hmmmmmmm...who did I get that idea from????)  I *think* that you used to be able to make this comparison in SQL 6.5 and earlier (possibly due to the fact that they didn't support zero-length strings, but that's a WAG)Arnold is right about LIKE, but the order of comparison is important:select 1 where 'a' like 'a ' --returns nothingselect 1 where 'a ' like 'a' --this will return 1 |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-09-20 : 09:37:45
|
| I apologize. My prob was with substring, no the comparo ...Looks like SUBSTRING behaviour changed in SQL 2K ...Sorry for the noise ...Jay White{0} |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-09-20 : 09:38:03
|
| SELECT 1 WHERE CAST('a' AS VARBINARY) = CAST('a ' AS VARBINARY)works ... if you want case insenstive just useSELECT 1 WHERE CAST(UPPER('a') AS VARBINARY) = CAST(UPPER('a ') AS VARBINARY)Tip of the Month: Don't ever let a contractor install SQL Server on your servers, not even if they are fully qualified. Just imagine when your applications go down and people ask you why and you don't know because the stupid contractor specified different coalations between the servers and they both broke when trying to replicate data... then you find out the ****ing contractor left the SA password blank! |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-09-20 : 09:40:38
|
quote:
edit: Did SUBSTRING change between SQL7 and SQL2K?
Yup. Just tried it here on a 2k and 7 system and I get the same as you: 7 returns NULL, 2k returns (bizarrely) 'h'.Try this on 2k: select substring('hello',-1,3)Edit: Blimey, that's a feature?Edited by - Arnold Fribble on 09/20/2002 09:42:18 |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-09-20 : 10:17:33
|
quote:
Tip of the Month: Don't ever let a contractor install SQL Server on your servers...
Crap! I just installed SQL on my new laptop. I had better uninstall and find someone else to do the install for me  ...Jay White{0} |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-20 : 10:21:36
|
Not just ANYONE else, make sure they're an FTE...  |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2002-09-20 : 10:30:17
|
FTE...  Voted best SQL forum nickname...."Tutorial-D" |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-09-20 : 10:33:14
|
| ...if I could only find an FTE that would treat me like a real person...Jay White{0} |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-09-20 : 11:04:36
|
| Florida Tomato Exchange |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-20 : 11:15:17
|
Forked-Tongue EngineerFull-T!tted Engénue (yeah, I know, it's really spelled ingénue, so shoot me!)Full Term EmbryoFreaky Tiki Eniwetok-iFederal Trial EngagementFull Time Employee (nahhhhh, that's not it) quote:
Florida Tomato Exchange
Otherwise known as a General Election Campaign...Edited by - robvolk on 09/20/2002 11:17:36 |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2002-09-20 : 13:56:04
|
| ThanksI'm one, temporarily...Voted best SQL forum nickname...."Tutorial-D" |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-09-20 : 14:59:08
|
| I just find it funny how our company has an INSTALL team that does installs on all types of platforms and all software installed on our servers and workstations ... then we have a seperate team that manages both server and workstations ... then we have the developers who then use the system and then complain to the administrators of the system and then the admins complain to the installers and then i get all the complaints when they think its database or application related that isn't installed by the install team ... can't tell these numb *u*s anything ... boy do I love blaming it on user error though ... UPPER MANAGEMENT: IT'S YOUR GODDAMN APPLICATION SPAMMING OUR GLOBAL GROUPS!Me: No it's not... its user error some how...UPPER MANAGEMENT: PROVE IT!Me: Well my application don't send mail to global groups ... here's the email history for the past XX-daysUPPER MANAGEMENT: You still have some more convincing to do buddy!Me: *me growls at upper management*UPPER MANAGEMENT: Oh never mind, Joe Smoe (the VP) had an email rule that was forwarding all his emails from your application to the group and never turned it off ...Me: *me growls at upper management* not even a sorry? :(these are the good days |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-20 : 15:06:56
|
| Could any of this possibly explain the recent rise in outsourcing IT operations...?Makes ya wonder, don't it? |
|
|
|
|
Trailing Blanks in VARCHAR comparison