Re: Function Still Not Working
you not ADD Binary, you need to remove BINARY ... ;-) Sorry, I misunderstood. convert the string to latin1 or utf8 o.s.s. LOWER() (and UPPER()) are ineffective when applied to binary strings (BINARY, VARBINARY, BLOB). To perform lettercase conversion, convert the string to a non-binary string did you tried? Well, I thought I had tried this, but with all the other things that I'd tried, I guess I had everything mixed up. I started with a clean routine, used Convert(... using UTF8), and it works perfectly now. That's what it was. Thanks for the help! Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Function Still Not Working
-Original Message- From: Jesse [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2008 1:40 PM To: Martijn Tonies; MySQL List Subject: Re: Function Still Not Working Any difference in default collation? Not sure what that is. I'm using a visual tool (EMS) to create my function, and it doesn't offer that option. I could update it using the command prompt, however. I may try that later. I think what he means is... in one instance of the function, the data is collated as latin-iso-blahblah, perhaps, and a different collation (one without case sensitivity, eh?) in the other table... As I don't deal with letters/characters outside of the 'standard' Latin-iso-asdfasdf collation, I'm afraid there's not much else I can explain using my limited knowledge. Hopefully, though, that helped to give you an idea of what he was driving at. ;) Todd Boyd Web Programmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function Still Not Working
Hey,
Sorry for posting this again, but I got only one response last time, and
I'm
still having the problem. I spent HOURS the other day manually going
through the data and Properizing these things by hand. I don't want to do
that again if I can avoid it. If anyone has any clues on this one, I
would
appreciate it.
The only difference in this and what I have now is that someone suggested
changing it to Deterministic, which I did, and that didn't change the
output. I also changed SQL SECURITY DEFINER to SQL SECURITY INVOKER,
and that didn't make a difference either.
I have the following function on two servers:
CREATE FUNCTION `ProperCase`(cInput TEXT)
RETURNS text
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
Declare cReturn Text;
Set cReturn =
CONCAT(UPPER(SUBSTRING(cInput,1,1)),LOWER(SUBSTRING(cInput
FROM 2)));
RETURN cReturn;
END;
It's a very simple function used to properize a string sent to it. When I
do
a simple SELECT ProperCase('JESSE'); it returns JESSE on our server that
is running 5.0.17-nt-log. On another server that I've got, running
5.0.51a-community-nt, this function returns Jesse as it should.
The only difference that I can think of is the version. Is there a problem
with the older version that would cause this function not to work
properly?
Any difference in default collation?
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function Still Not Working
Jesse schrieb:
Sorry for posting this again, but I got only one response last time, and
I'm still having the problem. I spent HOURS the other day manually
going through the data and Properizing these things by hand. I don't
want to do that again if I can avoid it. If anyone has any clues on
this one, I would appreciate it.
The only difference in this and what I have now is that someone
suggested changing it to Deterministic, which I did, and that didn't
change the output. I also changed SQL SECURITY DEFINER to SQL
SECURITY INVOKER, and that didn't make a difference either.
I have the following function on two servers:
CREATE FUNCTION `ProperCase`(cInput TEXT)
RETURNS text
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
Declare cReturn Text;
Set cReturn = CONCAT(UPPER(SUBSTRING(cInput,1,1)),LOWER(SUBSTRING(cInput
FROM 2)));
RETURN cReturn;
END;
It's a very simple function used to properize a string sent to it. When
I do
a simple SELECT ProperCase('JESSE'); it returns JESSE on our server that
is running 5.0.17-nt-log. On another server that I've got, running
5.0.51a-community-nt, this function returns Jesse as it should.
does it work outside the function?
did you tried SUBSTRING(cInput, 2)?
did you tried with converting?
from the manual: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
LOWER() (and UPPER()) are ineffective when applied to binary strings
(BINARY, VARBINARY, BLOB). To perform lettercase conversion, convert the
string to a non-binary string:
mysql SET @str = BINARY 'New York';
mysql SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1));
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Function Still Not Working
-Original Message-
From: Martijn Tonies [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 12, 2008 6:23 AM
To: MySQL List
Subject: Re: Function Still Not Working
Hey,
Sorry for posting this again, but I got only one response last time,
and
I'm
still having the problem. I spent HOURS the other day manually
going
through the data and Properizing these things by hand. I don't want
to do
that again if I can avoid it. If anyone has any clues on this one,
I
would
appreciate it.
---8--- snip
It's a very simple function used to properize a string sent to it.
When I
do
a simple SELECT ProperCase('JESSE'); it returns JESSE on our
server
that
is running 5.0.17-nt-log. On another server that I've got, running
5.0.51a-community-nt, this function returns Jesse as it should.
The only difference that I can think of is the version. Is there a
problem
with the older version that would cause this function not to work
properly?
Any difference in default collation?
I am curious about that, as well. It brings to mind a discussion that
happened on this list last week (I believe) about case
sensitive/insensitive use of LIKE. I believe the synopsis was that
tables are either created as case-insensitive, or the search needs to be
specified as case sensitive (with BINARY).
Could this be a similar issue, perhaps? One table is specifically
case-insensitive with regard to the function, and the other is not?
Just spit-balling...
Todd Boyd
Web Programmer
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function Still Not Working
I am curious about that, as well. It brings to mind a discussion that happened on this list last week (I believe) about case sensitive/insensitive use of LIKE. I believe the synopsis was that tables are either created as case-insensitive, or the search needs to be specified as case sensitive (with BINARY). Could this be a similar issue, perhaps? One table is specifically case-insensitive with regard to the function, and the other is not? In this particular case, we're not dealing with any tables. I have also tried adding BINARY to the mix as well, and it didn't make any difference. Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function Still Not Working
Any difference in default collation? Not sure what that is. I'm using a visual tool (EMS) to create my function, and it doesn't offer that option. I could update it using the command prompt, however. I may try that later. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function Still Not Working
does it work outside the function?
Yes, If I run:
select CONCAT(UPPER(SUBSTRING('JESSE',1,1)),LOWER(SUBSTRING('JESSE',2)));
replacing cInput with 'JESSE', it returns Jesse as it should.
did you tried SUBSTRING(cInput, 2)?
Tried replacing SUBSTRING(cInput FROM 2) with SUBSTRING(cInput, 2) and it
didn't make any difference.
did you tried with converting?
I have had issues with this in other areas before, but didn't think about it
this time. However, I tred CONVERT with UTF8 and latin1 as you suggested.
LOWER() (and UPPER()) are ineffective when applied to binary strings
(BINARY, VARBINARY, BLOB). To perform lettercase conversion, convert the
string to a non-binary string:
mysql SET @str = BINARY 'New York';
mysql SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1));
I converted the function over to use a variable, and got the same results.
Here's the new function:
CREATE FUNCTION `ProperCase`(cInput TEXT)
RETURNS text
DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
COMMENT ''
BEGIN
Declare str Text;
Declare cReturn Text;
Set @str=BINARY cInput;
Set @cReturn =
CONCAT(UPPER(SUBSTRING(@str,1,1)),LOWER(SUBSTRING(@str,2)));
RETURN @cReturn;
END;
Still doesn't work. This is driving me NUTz 8-p
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
