Using @ variables with LIKE,CONCAT
This used to work fine in Mysql 4.3, but no longer works in 5.5.8:
set @txt='needle';
select * from table where field like CONCAT('%',@txt,'%');
--returns the null set. If I substitute like this:
select * from table where field like '%needle%';
it works perfectly (and as it did in 4.x).
How can I get this to work in 5.5.x?
Thanks,
-Hank
Re: Using @ variables with LIKE,CONCAT
Hi, I just tried this on a schema I had laying about and it worked fine:
mysql SET @dude='pilgrim';
Query OK, 0 rows affected (0.00 sec)
mysql SELECT namefield FROM mytable WHERE namefield LIKE
CONCAT('%',@dude,'%');
+---+
| name |
+---+
| Blood Elf Pilgrim |
| Blood Elf Pilgrim |
| Draenei Pilgrim |
| High Elf Pilgrim |
| Pilgrim Gal'ressa |
| Recovering Pilgrim|
| Wounded Blood Elf Pilgrim |
| Young Pilgrim |
+---+
I am running 5.5.6 x64 on Mac OS X.
Rich
On 11 May 2011 20:03, Hank hes...@gmail.com wrote:
This used to work fine in Mysql 4.3, but no longer works in 5.5.8:
set @txt='needle';
select * from table where field like CONCAT('%',@txt,'%');
--returns the null set. If I substitute like this:
select * from table where field like '%needle%';
it works perfectly (and as it did in 4.x).
How can I get this to work in 5.5.x?
Thanks,
-Hank
--
*Richard Bensley*
*Database Administrator*
*
*
richard.bens...@photobox.com
skype: richardbensley
Mobile: 07540878285
CONCAT with IF?
I want to store the product version that an article applies to and a comparison operator in my news system. But I can't wrap my head around the where clause... Here's what I've tried... CREATE TABLE test (version char(10), direction char(2)); select concat(6.0,direction,version) as operation from test; +-+ |operation| +-+ | 6.0=6.0 | 6.0=6.0 | 6.06.1 | 6.06.2 +-+ But when I do select if(concat(6.0,direction,version),Y,N) from test I ALWAYS get Y... which means I wouldn't get any meaningful results if I used it in a where clause... Any ideas on ways I can do this? Thanks! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: CONCAT with IF?
Something like: SET @version = 6.0; SELECT CASE direction WHEN '' THEN IF( @version version, 'Y', 'N') WHEN '' THEN IF (@version version, 'Y','N) ... END AS operation FROM test; -Original Message- From: Matt Neimeyer [mailto:m...@neimeyer.org] Sent: Wednesday, July 08, 2009 2:45 PM To: mysql@lists.mysql.com Subject: CONCAT with IF? I want to store the product version that an article applies to and a comparison operator in my news system. But I can't wrap my head around the where clause... Here's what I've tried... CREATE TABLE test (version char(10), direction char(2)); select concat(6.0,direction,version) as operation from test; +-+ |operation| +-+ | 6.0=6.0 | 6.0=6.0 | 6.06.1 | 6.06.2 +-+ But when I do select if(concat(6.0,direction,version),Y,N) from test I ALWAYS get Y... which means I wouldn't get any meaningful results if I used it in a where clause... Any ideas on ways I can do this? Thanks! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: left joins concat
Hi Phil,
you seem quite a bit confused!
I would not step to use joins before understanding the 'simple' logic
behind,
otherwise you will be always confused by the syntax.
There are many, many, many resources (thanks to Tim!)
I will try to give you a simple overview of joins but please get more
confortable with them!
In relational databases the information is(should!) logically separated into
entities which are no more no less represented by tables,
yes the tables you well know(mathematically speaking they are known as
[relations] the R in RDBMS).
So in some application,like yours, you could have the need to deal with
[books], [authors], [publishers].
These, as said, are the so called entities when we speak from an
abstract-logical point of view,
but eventually turn to be the tables in your database.
So you have a table with all the books, a table with all the authors, and a
table with the publishers.
How could we relate each book with his author?
One way is to have extra information on each book (1 book=1 record/row in
the [books] table),
if an author wrote 100 books you would have 100 times the same information
on each of his books.
another way(better!) is to add the extra information as well, but just an
identifier of the author,
an handle, a key, a UNIQUE value (Social Security Number?) , so that you
have only one place
with the author information (the [author] table) which is also great for
maintenance!
Imagine updating an information about the author on 100 rows of the [books]
table,
and update the same information just in one row of the [authors] table.
I think you can imagine also that the UNIQUE value you add to each book
which identifies the book author,
will be present in the [authors] table to be able to identify the author.
Until now we are just speaking about logic and you could do an excercise
with pen and paper,
drawing a line from the AUTHOR UNIQUE ID from the [books] table to the
AUTHOR UNIQUE ID from the [authors] table.
So you could easily find the author of each book by following the line the
links the two rows/records,
on the left you have the books and on the right you have the authors.
Reading from left to right, for instance, you would be able now to read
consequently the book title and the book author name.
Sit back, the JOIN is the line you have just draw.
It is the connection between two tables to be able to have on just one row
all the information that are split into two(or more) parts/table.
The ON clause that you find in the JOIN syntax is the place where you
specify
ON [books].AUTHOR UNIQUE ID = [authors].AUTHOR UNIQUE ID
( by the way the ID that points to the table with all the informations is
also knows as FOREIGN KEY, in this case the left operand)
The resulting table is a table that have each row like a concatenation of
two rows related from the two different tables.
The WHERE clause is used to FILTER, not to connect the two tables!!
After you connect(join) the two tables you could want to see only certain
rows, ok now you use the WHERE.
Forget about CONCAT/CONCAT_WS this is a string function, and is not related
to JOINS.
Please, let me know if this was useful to you.
Claudio Nanni
2009/2/22 PJ af.gour...@videotron.ca
I have been searching and searching for a clear and logical explanation
of JOINs and have found nothing that can be reasonably understood.
Perhaps I am dense or from another planet, but nothing seems to fall
into place.
I need to display all the books (with their respective authors and
publishers) -
the tables are book, author, publisher and book_author, book_publisher
for linking many to many books-authors and books-publishers.
Here is what I have (and it gives me rather confusing results:
SELECT b.title, b.sub_title, b.descr, b.comment, b.bk_cover,
b.copyright, b.ISBN, b.sellers, c.publisher,
CONCAT_WS(' ', first_name, last_name) AS Author
FROM book AS b, publishers AS c
NATURAL JOIN book_author AS ab
NATURAL JOIN author AS a
NATURAL JOIN book_publisher AS d
WHERE d.bookID = b.id
ORDER BY title ASC
First, I see what CONCAT_WS does (more or less) but I cannot grasp the
logic related to the author and book_author tables.
Second, I don't understand the logic of the linking tables since the
relationships seem to have to be stated outside the tables... (i.e. the
foreign key reference is in the table but seems to be necessary in a
WHERE clause as well ???
And lastly, I don't understand what conditions (ON or WHERE clauses)
need to be included to get the books matched up to their respective
authors and publishers.
One link that I have been trying to fathom is
http://www.java2s.com/Tutorial/MySQL/0100__Table-Join/Catalog0100__Table-Join.htm
but it is not very helpful in any explanations...
I sure would like to hear some clear explanations...
TIA
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives
Re: left joins concat
Claudio Nanni wrote:
Hi Phil,
you seem quite a bit confused!
I would not step to use joins before understanding the 'simple' logic
behind,
otherwise you will be always confused by the syntax.
There are many, many, many resources (thanks to Tim!)
I will try to give you a simple overview of joins but please get more
confortable with them!
In relational databases the information is(should!) logically
separated into entities which are no more no less represented by tables,
yes the tables you well know(mathematically speaking they are known as
[relations] the R in RDBMS).
So in some application,like yours, you could have the need to deal
with [books], [authors], [publishers].
These, as said, are the so called entities when we speak from an
abstract-logical point of view,
but eventually turn to be the tables in your database.
So you have a table with all the books, a table with all the authors,
and a table with the publishers.
How could we relate each book with his author?
One way is to have extra information on each book (1 book=1 record/row
in the [books] table),
if an author wrote 100 books you would have 100 times the same
information on each of his books.
another way(better!) is to add the extra information as well, but just
an identifier of the author,
an handle, a key, a UNIQUE value (Social Security Number?) , so that
you have only one place
with the author information (the [author] table) which is also great
for maintenance!
Imagine updating an information about the author on 100 rows of the
[books] table,
and update the same information just in one row of the [authors] table.
I think you can imagine also that the UNIQUE value you add to each
book which identifies the book author,
will be present in the [authors] table to be able to identify the author.
Until now we are just speaking about logic and you could do an
excercise with pen and paper,
drawing a line from the AUTHOR UNIQUE ID from the [books] table to
the AUTHOR UNIQUE ID from the [authors] table.
So you could easily find the author of each book by following the line
the links the two rows/records,
on the left you have the books and on the right you have the authors.
Reading from left to right, for instance, you would be able now to
read consequently the book title and the book author name.
Sit back, the JOIN is the line you have just draw.
It is the connection between two tables to be able to have on just one
row all the information that are split into two(or more) parts/table.
The ON clause that you find in the JOIN syntax is the place where you
specify
ON [books].AUTHOR UNIQUE ID = [authors].AUTHOR UNIQUE ID
( by the way the ID that points to the table with all the informations
is also knows as FOREIGN KEY, in this case the left operand)
The resulting table is a table that have each row like a concatenation
of two rows related from the two different tables.
The WHERE clause is used to FILTER, not to connect the two tables!!
After you connect(join) the two tables you could want to see only
certain rows, ok now you use the WHERE.
Forget about CONCAT/CONCAT_WS this is a string function, and is not
related to JOINS.
Please, let me know if this was useful to you.
Claudio Nanni
2009/2/22 PJ af.gour...@videotron.ca mailto:af.gour...@videotron.ca
I have been searching and searching for a clear and logical
explanation
of JOINs and have found nothing that can be reasonably understood.
Perhaps I am dense or from another planet, but nothing seems to fall
into place.
I need to display all the books (with their respective authors and
publishers) -
the tables are book, author, publisher and book_author, book_publisher
for linking many to many books-authors and books-publishers.
Here is what I have (and it gives me rather confusing results:
SELECT b.title, b.sub_title, b.descr, b.comment, b.bk_cover,
b.copyright, b.ISBN, b.sellers, c.publisher,
CONCAT_WS(' ', first_name, last_name) AS Author
FROM book AS b, publishers AS c
NATURAL JOIN book_author AS ab
NATURAL JOIN author AS a
NATURAL JOIN book_publisher AS d
WHERE d.bookID = b.id http://b.id
ORDER BY title ASC
First, I see what CONCAT_WS does (more or less) but I cannot grasp the
logic related to the author and book_author tables.
Second, I don't understand the logic of the linking tables since the
relationships seem to have to be stated outside the tables...
(i.e. the
foreign key reference is in the table but seems to be necessary in a
WHERE clause as well ???
And lastly, I don't understand what conditions (ON or WHERE clauses)
need to be included to get the books matched up to their respective
authors and publishers.
One link that I have been trying to fathom is
http://www.java2s.com/Tutorial/MySQL/0100__Table-Join/Catalog0100__Table-Join.htm
Re: left joins concat
Gentlemen,
and all those who care,
THE PROBLEM SEEMS TO BE SOLVED (for now, NEXT is HOW TO QUERY(SELECT)
MULTIPLE AUTHORS AND DISPLAY THAT and then HOW TO DEAL WITH MULTIPLE
CATEGORIES (heh... heh... heh ;-) :
SELECT b.title, b.sub_title, b.descr, b.comment, b.bk_cover,
b.copyright, b.ISBN, c.publisher,
CONCAT_WS(' ', first_name, last_name) AS Author
FROM book AS b
LEFT JOIN book_author AS ab ON b.id = ab.bookID
LEFT JOIN author AS a ON ab.authID=a.id
LEFT JOIN book_publisher AS abc ON b.id = abc.bookID
LEFT JOIN publishers AS c ON abc.publishers_id = c.id
ORDER BY title ASC
This works except I don't fully understand how the CONCAT AS Author
works, nor the AS ab and AS abc.
Does the order of ab and abc matter? Are they related... I think I got
this working by pure experimentation in trying all possible
combinations... kind of primitive, but it seems to work... long hours
and loss of sleep... etc...
And then there is the warning message at the bottom of the displayed web
page:
*Warning*: Unknown: Your script possibly relies on a session side-effect
which existed until PHP 4.2.3. Please be advised that the session
extension does not consider global variables as a source of data, unless
register_globals is enabled. You can disable this functionality and this
warning by setting session.bug_compat_42 or session.bug_compat_warn to
off, respectively. in *Unknown* on line *0
*I guess I'll have to research it on the web... :-)*
*
Claudio Nanni wrote:
Hi Phil,
you seem quite a bit confused!
I would not step to use joins before understanding the 'simple' logic
behind,
otherwise you will be always confused by the syntax.
There are many, many, many resources (thanks to Tim!)
I will try to give you a simple overview of joins but please get more
confortable with them!
In relational databases the information is(should!) logically
separated into entities which are no more no less represented by tables,
yes the tables you well know(mathematically speaking they are known as
[relations] the R in RDBMS).
So in some application,like yours, you could have the need to deal
with [books], [authors], [publishers].
These, as said, are the so called entities when we speak from an
abstract-logical point of view,
but eventually turn to be the tables in your database.
So you have a table with all the books, a table with all the authors,
and a table with the publishers.
How could we relate each book with his author?
One way is to have extra information on each book (1 book=1 record/row
in the [books] table),
if an author wrote 100 books you would have 100 times the same
information on each of his books.
another way(better!) is to add the extra information as well, but just
an identifier of the author,
an handle, a key, a UNIQUE value (Social Security Number?) , so that
you have only one place
with the author information (the [author] table) which is also great
for maintenance!
Imagine updating an information about the author on 100 rows of the
[books] table,
and update the same information just in one row of the [authors] table.
I think you can imagine also that the UNIQUE value you add to each
book which identifies the book author,
will be present in the [authors] table to be able to identify the author.
Until now we are just speaking about logic and you could do an
excercise with pen and paper,
drawing a line from the AUTHOR UNIQUE ID from the [books] table to
the AUTHOR UNIQUE ID from the [authors] table.
So you could easily find the author of each book by following the line
the links the two rows/records,
on the left you have the books and on the right you have the authors.
Reading from left to right, for instance, you would be able now to
read consequently the book title and the book author name.
Sit back, the JOIN is the line you have just draw.
It is the connection between two tables to be able to have on just one
row all the information that are split into two(or more) parts/table.
The ON clause that you find in the JOIN syntax is the place where you
specify
ON [books].AUTHOR UNIQUE ID = [authors].AUTHOR UNIQUE ID
( by the way the ID that points to the table with all the informations
is also knows as FOREIGN KEY, in this case the left operand)
The resulting table is a table that have each row like a concatenation
of two rows related from the two different tables.
The WHERE clause is used to FILTER, not to connect the two tables!!
After you connect(join) the two tables you could want to see only
certain rows, ok now you use the WHERE.
Forget about CONCAT/CONCAT_WS this is a string function, and is not
related to JOINS.
Please, let me know if this was useful to you.
Claudio Nanni
2009/2/22 PJ af.gour...@videotron.ca mailto:af.gour...@videotron.ca
I have been searching and searching for a clear and logical
explanation
of JOINs and have found nothing that can be reasonably understood.
Perhaps I am dense or from another
Re: left joins concat
The 'AS' keyword for tables is just to give a table an alias by which
you can then use it in the rest of the query. In your case, when you
say 'book as b' in your query, it means that you can use b in places
where you need to refer to the book table. eg. 'b.id' refers to the
'id' field of the 'book' table. It is good practice to always use an
alias for a table and refer to the table by that alias anywhere in the
query. It makes your query easier to read and understand.
As a side-effect (which you should probably not focus on just yet ;) )
it means you can use this construction to join a table on itself and
use aliasses in the rest of the query to uniquely identify the table
you are referring to.
The same idea goes for the as keyword on field names. You can use
those aliasses to refer to the column in the gorup by, order by and
having clauses of your query. It is mostly used to display a proper
column name though. In your example, the concat_ws expresion has an
alias so in the queries result this name will be used instead of the
expression used to select.
A propos: The problem with your first query was that you were using
natural joins. Natural joins match up all columns that have the same
name in both tables automatically. I consider it bad practice to use
it as it might have unexpected results, especially when you alter
tables later on.
I recommend you to read up on your SQL knowledge though, as these are
very basic problems and it is imperative to have a good understanding
of SQL to be able to work with (m)any relational databases. I haven't
looked at their specific SQL course, but w3schools generally has very
good basic introductions: http://www.w3schools.com/sql/
Have fun!
Walter
OlinData: Professional services for MySQL
Support * Consulting * Administration
http://www.olindata.com
On Sun, Feb 22, 2009 at 4:15 PM, PJ af.gour...@videotron.ca wrote:
Gentlemen,
and all those who care,
THE PROBLEM SEEMS TO BE SOLVED (for now, NEXT is HOW TO QUERY(SELECT)
MULTIPLE AUTHORS AND DISPLAY THAT and then HOW TO DEAL WITH MULTIPLE
CATEGORIES (heh... heh... heh ;-) :
SELECT b.title, b.sub_title, b.descr, b.comment, b.bk_cover,
b.copyright, b.ISBN, c.publisher,
CONCAT_WS(' ', first_name, last_name) AS Author
FROM book AS b
LEFT JOIN book_author AS ab ON b.id = ab.bookID
LEFT JOIN author AS a ON ab.authID=a.id
LEFT JOIN book_publisher AS abc ON b.id = abc.bookID
LEFT JOIN publishers AS c ON abc.publishers_id = c.id
ORDER BY title ASC
This works except I don't fully understand how the CONCAT AS Author
works, nor the AS ab and AS abc.
Does the order of ab and abc matter? Are they related... I think I got
this working by pure experimentation in trying all possible
combinations... kind of primitive, but it seems to work... long hours
and loss of sleep... etc...
And then there is the warning message at the bottom of the displayed web
page:
*Warning*: Unknown: Your script possibly relies on a session side-effect
which existed until PHP 4.2.3. Please be advised that the session
extension does not consider global variables as a source of data, unless
register_globals is enabled. You can disable this functionality and this
warning by setting session.bug_compat_42 or session.bug_compat_warn to
off, respectively. in *Unknown* on line *0
*I guess I'll have to research it on the web... :-)*
*
Claudio Nanni wrote:
Hi Phil,
you seem quite a bit confused!
I would not step to use joins before understanding the 'simple' logic
behind,
otherwise you will be always confused by the syntax.
There are many, many, many resources (thanks to Tim!)
I will try to give you a simple overview of joins but please get more
confortable with them!
In relational databases the information is(should!) logically
separated into entities which are no more no less represented by tables,
yes the tables you well know(mathematically speaking they are known as
[relations] the R in RDBMS).
So in some application,like yours, you could have the need to deal
with [books], [authors], [publishers].
These, as said, are the so called entities when we speak from an
abstract-logical point of view,
but eventually turn to be the tables in your database.
So you have a table with all the books, a table with all the authors,
and a table with the publishers.
How could we relate each book with his author?
One way is to have extra information on each book (1 book=1 record/row
in the [books] table),
if an author wrote 100 books you would have 100 times the same
information on each of his books.
another way(better!) is to add the extra information as well, but just
an identifier of the author,
an handle, a key, a UNIQUE value (Social Security Number?) , so that
you have only one place
with the author information (the [author] table) which is also great
for maintenance!
Imagine updating an information about the author on 100 rows of the
[books] table,
and update the same information just in one
Re: left joins concat
Thank you gentlemen.
Questions /or comments annotated below:
Walter Heck wrote:
The 'AS' keyword for tables is just to give a table an alias by which
you can then use it in the rest of the query. In your case, when you
say 'book as b' in your query, it means that you can use b in places
where you need to refer to the book table. eg. 'b.id' refers to the
'id' field of the 'book' table. It is good practice to always use an
alias for a table and refer to the table by that alias anywhere in the
query. It makes your query easier to read and understand.
As a side-effect (which you should probably not focus on just yet ;) )
it means you can use this construction to join a table on itself and
use aliasses in the rest of the query to uniquely identify the table
you are referring to.
I do want to know, so I'll look more into it. :)
The same idea goes for the as keyword on field names. You can use
those aliasses to refer to the column in the gorup by, order by and
having clauses of your query. It is mostly used to display a proper
column name though. In your example, the concat_ws expresion has an
alias so in the queries result this name will be used instead of the
expression used to select.
A propos: The problem with your first query was that you were using
natural joins. Natural joins match up all columns that have the same
name in both tables automatically. I consider it bad practice to use
it as it might have unexpected results, especially when you alter
tables later on.
When I had my flash of enlightenment came when I noticed that in my many
concatenations I had used and posted the NATURAL join (which = NATURAL
LEFT join); with a little tweaking I got it to work.
But I still don't understand if aliasing AS a is any different from
aliasing AS ab (or, for that matter, if I substituted a number for the
table. I haven't experimented yet, but is there a difference between
using a or ab, cd, abc and just plain t1, t2, t3 or is this the same:
one two three. It may seem naive to be asking such questions, but they
are not readily answered in the tutorials.
I recommend you to read up on your SQL knowledge though, as these are
very basic problems and it is imperative to have a good understanding
of SQL to be able to work with (m)any relational databases. I haven't
looked at their specific SQL course, but w3schools generally has very
good basic introductions: http://www.w3schools.com/sql/
I have consulted w3shools and do find that they are not clear on a lot
of details... I may be picky about the small details but it is they that
make the whole possible.
Have fun!
Walter
OlinData: Professional services for MySQL
Support * Consulting * Administration
http://www.olindata.com
On Sun, Feb 22, 2009 at 4:15 PM, PJ af.gour...@videotron.ca wrote:
Gentlemen,
and all those who care,
THE PROBLEM SEEMS TO BE SOLVED (for now, NEXT is HOW TO QUERY(SELECT)
MULTIPLE AUTHORS AND DISPLAY THAT and then HOW TO DEAL WITH MULTIPLE
CATEGORIES (heh... heh... heh ;-) :
SELECT b.title, b.sub_title, b.descr, b.comment, b.bk_cover,
b.copyright, b.ISBN, c.publisher,
CONCAT_WS(' ', first_name, last_name) AS Author
FROM book AS b
LEFT JOIN book_author AS ab ON b.id = ab.bookID
LEFT JOIN author AS a ON ab.authID=a.id
LEFT JOIN book_publisher AS abc ON b.id = abc.bookID
LEFT JOIN publishers AS c ON abc.publishers_id = c.id
ORDER BY title ASC
This works except I don't fully understand how the CONCAT AS Author
works, nor the AS ab and AS abc.
Does the order of ab and abc matter? Are they related... I think I got
this working by pure experimentation in trying all possible
combinations... kind of primitive, but it seems to work... long hours
and loss of sleep... etc...
And then there is the warning message at the bottom of the displayed web
page:
*Warning*: Unknown: Your script possibly relies on a session side-effect
which existed until PHP 4.2.3. Please be advised that the session
extension does not consider global variables as a source of data, unless
register_globals is enabled. You can disable this functionality and this
warning by setting session.bug_compat_42 or session.bug_compat_warn to
off, respectively. in *Unknown* on line *0
*I guess I'll have to research it on the web... :-)*
*
Claudio Nanni wrote:
Hi Phil,
you seem quite a bit confused!
I would not step to use joins before understanding the 'simple' logic
behind,
otherwise you will be always confused by the syntax.
There are many, many, many resources (thanks to Tim!)
I will try to give you a simple overview of joins but please get more
confortable with them!
In relational databases the information is(should!) logically
separated into entities which are no more no less represented by tables,
yes the tables you well know(mathematically speaking they are known as
[relations] the R in RDBMS).
So in some application,like yours, you could have the need to deal
with [books
left joins concat
I have been searching and searching for a clear and logical explanation
of JOINs and have found nothing that can be reasonably understood.
Perhaps I am dense or from another planet, but nothing seems to fall
into place.
I need to display all the books (with their respective authors and
publishers) -
the tables are book, author, publisher and book_author, book_publisher
for linking many to many books-authors and books-publishers.
Here is what I have (and it gives me rather confusing results:
SELECT b.title, b.sub_title, b.descr, b.comment, b.bk_cover,
b.copyright, b.ISBN, b.sellers, c.publisher,
CONCAT_WS(' ', first_name, last_name) AS Author
FROM book AS b, publishers AS c
NATURAL JOIN book_author AS ab
NATURAL JOIN author AS a
NATURAL JOIN book_publisher AS d
WHERE d.bookID = b.id
ORDER BY title ASC
First, I see what CONCAT_WS does (more or less) but I cannot grasp the
logic related to the author and book_author tables.
Second, I don't understand the logic of the linking tables since the
relationships seem to have to be stated outside the tables... (i.e. the
foreign key reference is in the table but seems to be necessary in a
WHERE clause as well ???
And lastly, I don't understand what conditions (ON or WHERE clauses)
need to be included to get the books matched up to their respective
authors and publishers.
One link that I have been trying to fathom is
http://www.java2s.com/Tutorial/MySQL/0100__Table-Join/Catalog0100__Table-Join.htm
but it is not very helpful in any explanations...
I sure would like to hear some clear explanations...
TIA
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: CONCAT doesn't work with NULL?
Price, Randall wrote:
Could you use something like this (untried):
SELECT
CONCAT(COALESCE(r.first_name, ''), ' ',
COALESCE(r.last_name,''), '\n',
COALESCE(r.organization, ''), '\n',
COALESCE(r.title,''), '\n',
COALESCE(a.address1, ''), '\n',
COALESCE(a.city, ''), ', ',
COALESCE(a.state,''), ' ',
COALESCE(a.zip, ''), '\n',
COALESCE(r.email,''))
FROM
registrants r,
addresses a
WHERE
r.reg_id=121
this is good. though, if r.title is NULL I'll get an extra empty row on
screen:
john doe
doe, inc.
-- no title, empty row
123 main st.
testtown, TE 12345
also, I would like to hear opinion about the following query:
SELECT o.col1, o.col2, o.col3,
(
SELECT CONCAT_WS('', r.first_name, ' ', r.last_name, '\n',
r.organization, '', r.title, '\n', a.address1, '\n', a.city, ', ',
a.state, ' ', a.zip, '\n', r.email, '\nHome: ', left(r.phone_home, 3),
'-', mid(r.phone_home, 3, 3), '-', right(r.phone_home, 4), '\nWork: ',
left(r.phone_work, 3), '-', mid(r.phone_work, 3, 3), '-',
right(r.phone_work, 4))
FROM registrants r, addresses a
WHERE r.reg_id=o.registered_id
and a.reg_id=r.reg_id
and a.address_type='Business'
LIMIT 1
) as REGISTERED_BY,
pm.payment_method as payment_method_name, f.form_name FORM_NAME
FROM orders o, payment_methods pm, forms f
WHERE o.order_id=.$order_id.
AND pm.pm_id=o.payment_method
AND f.form_id=.$form_id.
is it smart/good way to have subquery this way or solution below is
better one:
SELECT o.col1, o.col2, o.col3, r.first_name, r.last_name,
r.organization, r.title, a.address1, a.city, a.state, a.zip, r.email,
r.phone_home, r.phone_work, pm.payment_method as payment_method_name,
f.form_name FORM_NAME
FROM orders o, payment_methods pm, forms f
WHERE o.order_id=.$order_id.
AND pm.pm_id=o.payment_method
AND f.form_id=.$form_id.
AND r.reg_id=o.registered_id
AND a.reg_id=r.reg_id
AND a.address_type='Business'
in this case I have to create string REGISTERED_BY by php.
reason I did it as example 1 is because I read in few books and online
people saying do whatever you can using query in mysql rather then using
php. it's faster, better, more secure,...
thoughts?
-afan
Randall Price
Secure Enterprise Technology Initiatives
Microsoft Implementation Group
Virginia Tech Information Technology
1700 Pratt Drive
Blacksburg, VA 24060
-Original Message-
From: Afan Pasalic [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 14, 2008 11:53 AM
To: mysql@lists.mysql.com
Subject: CONCAT doesn't work with NULL?
hi,
I have query
SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization,
'\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ',
a.zip, '\n', r.email)
FROM registrants r, addresses a
WHERE r.reg_id=121
if any of columns has value (e.g. title) NULL, I'll get as result 0
records.
If query doesn't have concat() - it works fine.
Why is that?
-afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CONCAT doesn't work with NULL?
hi, I have query SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization, '\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ', a.zip, '\n', r.email) FROM registrants r, addresses a WHERE r.reg_id=121 if any of columns has value (e.g. title) NULL, I'll get as result 0 records. If query doesn't have concat() - it works fine. Why is that? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT doesn't work with NULL?
It doesn't return no rows, it returns row(s) with a single column set to a NULL value. In case one of the arguments is NULL, CONCAT() will return NULL. To replace the value of one of the fields with an empty string when it's NULL, you can use something like: CONCAT(COAESCE(a, ''), ' ', COAESCE(b, '')) On 5/14/08, Afan Pasalic [EMAIL PROTECTED] wrote: hi, I have query SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization, '\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ', a.zip, '\n', r.email) FROM registrants r, addresses a WHERE r.reg_id=121 if any of columns has value (e.g. title) NULL, I'll get as result 0 records. If query doesn't have concat() - it works fine. Why is that? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: CONCAT doesn't work with NULL?
Hi Afan, You can use concat_ws http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws --- CONCAT() returns NULL if any argument is NULL. CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument --- Ewen On Wed, May 14, 2008 at 5:53 PM, Afan Pasalic [EMAIL PROTECTED] wrote: hi, I have query SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization, '\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ', a.zip, '\n', r.email) FROM registrants r, addresses a WHERE r.reg_id=121 if any of columns has value (e.g. title) NULL, I'll get as result 0 records. If query doesn't have concat() - it works fine. Why is that? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT doesn't work with NULL?
On Wednesday 14 May 2008 18:02:42 Olexandr Melnyk wrote: It doesn't return no rows, it returns row(s) with a single column set to a NULL value. In case one of the arguments is NULL, CONCAT() will return NULL. To replace the value of one of the fields with an empty string when it's NULL, you can use something like: CONCAT(COAESCE(a, ''), ' ', COAESCE(b, '')) or CONCAT_WS IIRC W On 5/14/08, Afan Pasalic [EMAIL PROTECTED] wrote: hi, I have query SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization, '\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ', a.zip, '\n', r.email) FROM registrants r, addresses a WHERE r.reg_id=121 if any of columns has value (e.g. title) NULL, I'll get as result 0 records. If query doesn't have concat() - it works fine. Why is that? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: CONCAT doesn't work with NULL?
Could you use something like this (untried): SELECT CONCAT(COALESCE(r.first_name, ''), ' ', COALESCE(r.last_name,''), '\n', COALESCE(r.organization, ''), '\n', COALESCE(r.title,''), '\n', COALESCE(a.address1, ''), '\n', COALESCE(a.city, ''), ', ', COALESCE(a.state,''), ' ', COALESCE(a.zip, ''), '\n', COALESCE(r.email,'')) FROM registrants r, addresses a WHERE r.reg_id=121 Randall Price Secure Enterprise Technology Initiatives Microsoft Implementation Group Virginia Tech Information Technology 1700 Pratt Drive Blacksburg, VA 24060 -Original Message- From: Afan Pasalic [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 14, 2008 11:53 AM To: mysql@lists.mysql.com Subject: CONCAT doesn't work with NULL? hi, I have query SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization, '\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ', a.zip, '\n', r.email) FROM registrants r, addresses a WHERE r.reg_id=121 if any of columns has value (e.g. title) NULL, I'll get as result 0 records. If query doesn't have concat() - it works fine. Why is that? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT doesn't work with NULL?
Thanks Ewen, that's what I was looking for! :D -afan ewen fortune wrote: Hi Afan, You can use concat_ws http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws --- CONCAT() returns NULL if any argument is NULL. CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument --- Ewen On Wed, May 14, 2008 at 5:53 PM, Afan Pasalic [EMAIL PROTECTED] wrote: hi, I have query SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization, '\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ', a.zip, '\n', r.email) FROM registrants r, addresses a WHERE r.reg_id=121 if any of columns has value (e.g. title) NULL, I'll get as result 0 records. If query doesn't have concat() - it works fine. Why is that? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT doesn't work with NULL?
First, I want to thank to everybody on such afast respond. Thank you. Second, what would be difference between concat_ws and the Randalll's solution (bellow)? -afan Price, Randall wrote: Could you use something like this (untried): SELECT CONCAT(COALESCE(r.first_name, ''), ' ', COALESCE(r.last_name,''), '\n', COALESCE(r.organization, ''), '\n', COALESCE(r.title,''), '\n', COALESCE(a.address1, ''), '\n', COALESCE(a.city, ''), ', ', COALESCE(a.state,''), ' ', COALESCE(a.zip, ''), '\n', COALESCE(r.email,'')) FROM registrants r, addresses a WHERE r.reg_id=121 Randall Price Secure Enterprise Technology Initiatives Microsoft Implementation Group Virginia Tech Information Technology 1700 Pratt Drive Blacksburg, VA 24060 -Original Message- From: Afan Pasalic [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 14, 2008 11:53 AM To: mysql@lists.mysql.com Subject: CONCAT doesn't work with NULL? hi, I have query SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization, '\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ', a.zip, '\n', r.email) FROM registrants r, addresses a WHERE r.reg_id=121 if any of columns has value (e.g. title) NULL, I'll get as result 0 records. If query doesn't have concat() - it works fine. Why is that? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT doesn't work with NULL?
actually, this will not work for me (or I got it wrong :D) because I need to have street, state and zip in one line and with separator defined on the beginning it will put everything in separate lines. :D ewen fortune wrote: Hi Afan, You can use concat_ws http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws --- CONCAT() returns NULL if any argument is NULL. CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument --- Ewen On Wed, May 14, 2008 at 5:53 PM, Afan Pasalic [EMAIL PROTECTED] wrote: hi, I have query SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization, '\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ', a.zip, '\n', r.email) FROM registrants r, addresses a WHERE r.reg_id=121 if any of columns has value (e.g. title) NULL, I'll get as result 0 records. If query doesn't have concat() - it works fine. Why is that? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT doesn't work with NULL?
On Wednesday 14 May 2008 18:52:20 Afan Pasalic wrote: actually, this will not work for me (or I got it wrong :D) because I need to have street, state and zip in one line and with separator defined on the beginning it will put everything in separate lines. Use a 'space' as sparator instead of '\n' :D ewen fortune wrote: Hi Afan, You can use concat_ws http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_con cat-ws --- CONCAT() returns NULL if any argument is NULL. CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument --- Ewen On Wed, May 14, 2008 at 5:53 PM, Afan Pasalic [EMAIL PROTECTED] wrote: hi, I have query SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization, '\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ', a.zip, '\n', r.email) FROM registrants r, addresses a WHERE r.reg_id=121 if any of columns has value (e.g. title) NULL, I'll get as result 0 records. If query doesn't have concat() - it works fine. Why is that? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT doesn't work with NULL?
On May 14, 2008, at 10:53 AM, Afan Pasalic wrote:
hi,
I have query
SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization,
'\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ',
a.zip, '\n', r.email)
FROM registrants r, addresses a
WHERE r.reg_id=121
if any of columns has value (e.g. title) NULL, I'll get as result 0
records.
If query doesn't have concat() - it works fine.
Why is that?
That's how CONCAT() is documented to work:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat
CONCAT() returns NULL if any argument is NULL.
You might want to try CONCAT_WS('', ...) instead. CONCAT_WS() isn't
fazed
by NULL values the same way that CONCAT() is. :-)
mysql select CONCAT('a',NULL,'b');
+--+
| CONCAT('a',NULL,'b') |
+--+
| NULL |
+--+
1 row in set (0.07 sec)
mysql select CONCAT_WS('','a',NULL,'b');
++
| CONCAT_WS('','a',NULL,'b') |
++
| ab |
++
1 row in set (0.00 sec)
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT doesn't work with NULL?
i execute follow sql.
select concat_ws('','d','\n','c');
the result is :
++
| concat_ws('','d','\n','c') |
++
| d
c|
++
There are no result as you said.
- Original Message -
From: Afan Pasalic [EMAIL PROTECTED]
To: ewen fortune [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Thursday, May 15, 2008 12:52 AM
Subject: Re: CONCAT doesn't work with NULL?
actually, this will not work for me (or I got it wrong :D) because I
need to have street, state and zip in one line and with separator
defined on the beginning it will put everything in separate lines.
:D
ewen fortune wrote:
Hi Afan,
You can use concat_ws
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws
---
CONCAT() returns NULL if any argument is NULL.
CONCAT_WS() does not skip empty strings. However, it does skip any
NULL values after the separator argument
---
Ewen
On Wed, May 14, 2008 at 5:53 PM, Afan Pasalic [EMAIL PROTECTED] wrote:
hi,
I have query
SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization, '\n',
r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ', a.zip, '\n',
r.email)
FROM registrants r, addresses a
WHERE r.reg_id=121
if any of columns has value (e.g. title) NULL, I'll get as result 0
records.
If query doesn't have concat() - it works fine.
Why is that?
-afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concat alternative
On 10/24/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Gerard wrote: Currently I am running a concat statement to combine a field with a user name and domain to create and email address. In testing it looks like running the concat is a very slow command to run. The select statement currently looks like this. select concat(user,'@',domain),servername,port from database where concat(user,'@',domain)='[EMAIL PROTECTED]'; Why do CONCAT() twice? Couldn't you just do: WHERE user = 'username' AND domain = 'domain.com' Or am i missing something? brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] This is done because the application is not flexible. I can only put one condition in which goes for the where and select statement.
Concat alternative
Currently I am running a concat statement to combine a field with a user name and domain to create and email address. In testing it looks like running the concat is a very slow command to run. The select statement currently looks like this. select concat(user,'@',domain),servername,port from database where concat(user,'@',domain)='[EMAIL PROTECTED]';
Re: Concat alternative
On 10/24/07, Gerard [EMAIL PROTECTED] wrote:
Currently I am running a concat statement to combine a field with a user
name and domain to create and email address. In testing it looks like
running the concat is a very slow command to run. The select statement
currently looks like this.
select concat(user,'@',domain),servername,port from database where
concat(user,'@',domain)='[EMAIL PROTECTED]';
That query will be very slow because mysql will have to examine each
row. You would be far better served to do something like
select concat(user,'@',domain),servername,port
from database
where
user = substring('[EMAIL PROTECTED]',0,LOCATE('@','[EMAIL PROTECTED]'))
AND
domain = substring('[EMAIL PROTECTED]',LOCATE('@','[EMAIL PROTECTED]'))
or something like that, or even better split it outside mysql if possible.
--
Rob Wultsch
(480)223-2566
[EMAIL PROTECTED] (email/google im)
wultsch (aim)
[EMAIL PROTECTED] (msn)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concat alternative
Gerard wrote: Currently I am running a concat statement to combine a field with a user name and domain to create and email address. In testing it looks like running the concat is a very slow command to run. The select statement currently looks like this. select concat(user,'@',domain),servername,port from database where concat(user,'@',domain)='[EMAIL PROTECTED]'; Why do CONCAT() twice? Couldn't you just do: WHERE user = 'username' AND domain = 'domain.com' Or am i missing something? brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT(int_col, string_col) and charset and collation problems
Hi, It is that, what you think a binary is ,...is indeed a binary. As per the manual, If a string input or function result is a binary string, the string has no character set or collation. so the resultant 'binary' is expected. if u want the resultant as: mysql select charset(concat(tt,CONVERT(id USING latin1))) from test; Thanks ViSolve DB Team - Original Message - From: Dušan Pavlica [EMAIL PROTECTED] To: list mysql mysql@lists.mysql.com Sent: Wednesday, January 31, 2007 5:31 PM Subject: CONCAT(int_col, string_col) and charset and collation problems Hi, I'm using MySQL 4.1.15, WinXP and my problem is that SELECT CHARSET(CONCAT(int_column, string_column)) FROM mytable; always returns charset 'binary' and I need resulting charset to be same as a charset of a string_column because I don't want to look for charset of a column whenever I have to call CONCAT function. Do you have any tips how to achieve it? In my opinion, results of concatenating string and numeric columns should always have charset of string column(s) and not binary charset. Thanks in advance for any response Dusan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT(int_col, string_col) and charset and collation problems
I know about CONVERT but I wanted to check character set of 'tt' column and this result use in CONVERT. Something like CONVERT(id USING CHARSET(Name)) which doesn't work. I just wonder why CONCAT(1, ',', Name) works OK even if CHARSET(1) is binary string and CONCAT(id, ',', Name) doesn't Thanks, Dusan ViSolve DB Team napsal(a): Hi, It is that, what you think a binary is ,...is indeed a binary. As per the manual, If a string input or function result is a binary string, the string has no character set or collation. so the resultant 'binary' is expected. if u want the resultant as: mysql select charset(concat(tt,CONVERT(id USING latin1))) from test; Thanks ViSolve DB Team - Original Message - From: Dušan Pavlica [EMAIL PROTECTED] To: list mysql mysql@lists.mysql.com Sent: Wednesday, January 31, 2007 5:31 PM Subject: CONCAT(int_col, string_col) and charset and collation problems Hi, I'm using MySQL 4.1.15, WinXP and my problem is that SELECT CHARSET(CONCAT(int_column, string_column)) FROM mytable; always returns charset 'binary' and I need resulting charset to be same as a charset of a string_column because I don't want to look for charset of a column whenever I have to call CONCAT function. Do you have any tips how to achieve it? In my opinion, results of concatenating string and numeric columns should always have charset of string column(s) and not binary charset. Thanks in advance for any response Dusan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT(int_col, string_col) and charset and collation problems
Hi, I know about CONVERT but I wanted to check character set of 'tt' column and this result use in CONVERT. Something like CONVERT(id USING CHARSET(Name)) which doesn't work. It wont. the syntax is -- CONVERT(expr USING transcoding_name); its the name of the transcode and not an expr. I just wonder why CONCAT(1, ',', Name) works OK even if CHARSET(1) is binary string and CONCAT(id, ',', Name) doesn't It does. mysql select concat(1,',',tt) from test; +--+ | concat(1,',',tt) | +--+ | 1,a | | 1,b | | 1,c | +--+ 3 rows in set (0.00 sec) mysql select concat(id,',',tt) from test; +---+ | concat(id,',',tt) | +---+ | 1,a | | 2,b | | 3,c | +---+ 3 rows in set (0.00 sec) Thanks ViSole DB Team. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CONCAT(int_col, string_col) and charset and collation problems
Hi, I'm using MySQL 4.1.15, WinXP and my problem is that SELECT CHARSET(CONCAT(int_column, string_column)) FROM mytable; always returns charset 'binary' and I need resulting charset to be same as a charset of a string_column because I don't want to look for charset of a column whenever I have to call CONCAT function. Do you have any tips how to achieve it? In my opinion, results of concatenating string and numeric columns should always have charset of string column(s) and not binary charset. Thanks in advance for any response Dusan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT() returns not correct character set
Hi there,
I tried a few other queries:
first, confirm that what you think is a binary is indeed a binary:
mysql SELECT CHARSET(_binary'Binary');
+--+
| CHARSET(_binary'Binary') |
+--+
| binary |
+--+
1 row in set (0.00 sec)
check the regular text (for completeness' sake)
mysql select charset ('binary');
++
| charset ('binary') |
++
| latin1 |
++
1 row in set (0.00 sec)
OK, so concatenating the 2 should result in a binary according to the manual:
mysql SELECT CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1)));
+--+
| CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1))) |
+--+
| latin1 |
+--+
1 row in set (0.00 sec)
Hrm, I got the same result you did.
However,
mysql SELECT CHARSET(CONCAT(_binary'Binary','foo'));
+--+
| CHARSET(CONCAT((_binary'Binary'),'foo')) |
+--+
| binary |
+--+
1 row in set (0.00 sec)
works just fine. Why are you converting the text to latin1? It's
already there. That conversion seems to be messing things up.
Perhaps someone can explain why the conversion messes things up --
seems like a bug to me.
-Sheeri
On 2/26/06, Hirofumi Fujiwara [EMAIL PROTECTED] wrote:
Dear MySQL fans,
I tested CONCAT() with binary strings and I got strange result.
Manual says:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
If the arguments include any binary strings, the result is a binary
string.
But the following test says:
bianry + latin1 latin1 (not bianry)
mysql SELECT CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1)));
+--+
| CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1))) |
+--+
| latin1 |
+--+
1 row in set (0.00 sec)
Hirofumi Fujiwara (Tokyo JAPAN) enjoy JAVA and Puzzle World
[EMAIL PROTECTED]http://www.pro.or.jp/~fuji/index-eng.html
[EMAIL PROTECTED] Puzzle Japanhttp://www.puzzle.jp/
My SUDOKU Probs http://www.pro.or.jp/~fuji/sudoku/problems/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT() returns not correct character set
Hi,
Is there any difference between _binary'Binary' and CONVERT('Binary' USING
binary)
mysql SELECT CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1)));
+--+
| CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1))) |
+--+
| latin1 |
+--+
1 row in set (0.00 sec)
mysql SELECT CHARSET(CONCAT(CONVERT('Bianry' USING binary),CONVERT('abc' USING
latin1)));
+-+
| CHARSET(CONCAT(CONVERT('Bianry' USING binary),CONVERT('abc' USING latin1))) |
+-+
| binary |
+-+
1 row in set (0.00 sec)
In Japan, we have to use many kinds of character set.
eucjpms,ujis for unix
cp932,sjis for Windows
utf8 for Java, MySQL meta data
So, it's a very complicated world.
Hi there,
I tried a few other queries:
first, confirm that what you think is a binary is indeed a binary:
mysql SELECT CHARSET(_binary'Binary');
+--+
| CHARSET(_binary'Binary') |
+--+
| binary |
+--+
1 row in set (0.00 sec)
check the regular text (for completeness' sake)
mysql select charset ('binary');
++
| charset ('binary') |
++
| latin1 |
++
1 row in set (0.00 sec)
OK, so concatenating the 2 should result in a binary according to the manual:
mysql SELECT CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1)));
+--+
| CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1))) |
+--+
| latin1 |
+--+
1 row in set (0.00 sec)
Hrm, I got the same result you did.
However,
mysql SELECT CHARSET(CONCAT(_binary'Binary','foo'));
+--+
| CHARSET(CONCAT((_binary'Binary'),'foo')) |
+--+
| binary |
+--+
1 row in set (0.00 sec)
works just fine. Why are you converting the text to latin1? It's
already there. That conversion seems to be messing things up.
Perhaps someone can explain why the conversion messes things up --
seems like a bug to me.
-Sheeri
On 2/26/06, Hirofumi Fujiwara [EMAIL PROTECTED] wrote:
Dear MySQL fans,
I tested CONCAT() with binary strings and I got strange result.
Manual says:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
If the arguments include any binary strings, the result is a binary
string.
But the following test says:
bianry + latin1 latin1 (not bianry)
mysql SELECT CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1)));
+--+
| CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1))) |
+--+
| latin1 |
+--+
1 row in set (0.00 sec)
Hirofumi Fujiwara (Tokyo JAPAN) enjoy JAVA and Puzzle World
[EMAIL PROTECTED]http://www.pro.or.jp/~fuji/index-eng.html
[EMAIL PROTECTED] Puzzle Japanhttp://www.puzzle.jp/
My SUDOKU Probs http://www.pro.or.jp/~fuji/sudoku/problems/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CONCAT() returns not correct character set
Dear MySQL fans,
I tested CONCAT() with binary strings and I got strange result.
Manual says:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
If the arguments include any binary strings, the result is a binary
string.
But the following test says:
bianry + latin1 latin1 (not bianry)
mysql SELECT CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1)));
+--+
| CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1))) |
+--+
| latin1 |
+--+
1 row in set (0.00 sec)
Hirofumi Fujiwara (Tokyo JAPAN) enjoy JAVA and Puzzle World
[EMAIL PROTECTED]http://www.pro.or.jp/~fuji/index-eng.html
[EMAIL PROTECTED] Puzzle Japanhttp://www.puzzle.jp/
My SUDOKU Probs http://www.pro.or.jp/~fuji/sudoku/problems/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT() And Columns
Shaun [EMAIL PROTECTED] wrote on 01/18/2006 10:57:49 AM: Hi, I am trying to update a field so that it retains its contents plus the contents of another column like this: UPDATE MyTable SET Comments = CONCAT(Comments, 'Old_ID_Field = ', Old_ID_Field) WHERE Table_ID = 1; However I just get a blank Comments field, any ideas why this is happening? Thanks for your advice If either `Comments` or `Old_ID_Field` is null, then the CONCAT() fuction will return as NULL. You should probably wrap both fields in something like COALESCE() to convert any NULLs to an empty string. UPDATE MyTable SET Comments = CONCAT(COALESCE(Comments,''), 'Old_ID_Field = ', COALESCE(Old_ID_Field,'')) WHERE Table_ID = 1; If that's not it, please post what version you are using, the definition of the table you are working with and the results of SELECT Comments, Old_ID_Field from MyTable where Table_ID=1; HTH, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
CONCAT() And Columns
Hi, I am trying to update a field so that it retains its contents plus the contents of another column like this: UPDATE MyTable SET Comments = CONCAT(Comments, 'Old_ID_Field = ', Old_ID_Field) WHERE Table_ID = 1; However I just get a blank Comments field, any ideas why this is happening? Thanks for your advice -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [SPAM] - concat string and update question - Found word(s) remove list in the Text body
Try this UPDATE people SETphone = CASE WHEN LEFT(phone,4) = '405_' THEN MID(phone,5,20) WHEN LEFT(phone,3) = '405' THEN MID(phone,4,20) ELSE phone END FROM people WHERE LEFT(phone,3) = '405' AND LENGTH(phone) 7; This way you don't accidentally replace '405' contained in the rest of the phone number. Also, if the phone numbers contain punctuation you will need to change the '7' in the LENGTH criteria. You will have to replace 'people' and 'phone' with the appropriate table and column name respectively. -Original Message- From: 2wsxdr5 [mailto:[EMAIL PROTECTED] Sent: Friday, January 06, 2006 8:09 PM To: mysql@lists.mysql.com Subject: [SPAM] - concat string and update question - Found word(s) remove list in the Text body I have a table of people and their phone numbers, some have the area code and others do not. Everyone in this table lives in the same area code, so I would like to remove the area code from the phone number field. Basically replace '(405)' or '405-' with '' is there an easy way to do that in a query with out writing code? I know how to do it with code but would like an easier way if some one knows the SQL better than I do. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: concat() function
I'm sorry, my mistake. The type definition is *nloc_num,ins_numero and ins_digi are decimal(5,0) 2005/10/11, Luciano Centeno [EMAIL PROTECTED]: hello, my friends, the query option 1 return the right value, the query option 2 not. Why concat function make the difference? *nloc_num,ins_numero and ins_digi are decimal(3,0) / option 1 .- select max(bi_num) as num from b_inmuebles where concat(nloc_num) = 1 and concat(ins_numero)= 1770 and concat(ins_digi)= 1 return it 1 / option 2 .- select max(bi_num) as num from b_inmuebles where nloc_num = 1 and ins_numero= 1770 and ins_digi= 1 return it NULL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
concat() function
hello, my friends, the query option 1 return the right value, the query option 2 not. Why concat function make the difference? *nloc_num,ins_numero and ins_digi are decimal(3,0) / option 1 .- select max(bi_num) as num from b_inmuebles where concat(nloc_num) = 1 and concat(ins_numero)= 1770 and concat(ins_digi)= 1 return it 1 / option 2 .- select max(bi_num) as num from b_inmuebles where nloc_num = 1 and ins_numero= 1770 and ins_digi= 1 return it NULL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: concat() function
Hello. *nloc_num,ins_numero and ins_digi are decimal(3,0) You're using 1770 for comparison which is not in the type range. In my opinion, for incorrect data you may obtain incorrect answers. Luciano Centeno wrote: hello, my friends, the query option 1 return the right value, the query option 2 not. Why concat function make the difference? *nloc_num,ins_numero and ins_digi are decimal(3,0) / option 1 .- select max(bi_num) as num from b_inmuebles where concat(nloc_num) =3D 1 and concat(ins_numero)=3D 1770 and concat(ins_digi)=3D 1 return it 1 / option 2 .- select max(bi_num) as num from b_inmuebles where nloc_num =3D 1 and ins_numero=3D 1770 and ins_digi=3D 1 return it NULL -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: concat function problems
resuming all above i can say that to my greatest regret nobody even expects what the matter is :( so i'll just try to install a newer version of server hoping the bug will disappear -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: concat function problems
PS after mysql reinstallation (upgrade from 4.1.12 to 4.1.13) from source code with EXACTLY THE SAME ./configure options as before and the SAME configuration file everything is OK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: concat function problems
Hello Averyanov, [EMAIL PROTECTED] wrote: Hello Nuno, Tuesday, July 26, 2005, 8:53:33 PM, you wrote: [EMAIL PROTECTED] wrote: (...) When i try to execute the following query (...) i get this error: ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id:6 Current database: test ERROR 2013 (HY000): Lost connection to MySQL server during query but if i change my query to this one everything is fine SELECT COUNT(DISTINCT field1) as value1, CONCAT(field2, '') as value2 FROM table1, table2 GROUP BY value2 (here CONCAT(field2, '-') is replaced with CONCAT(field2, '') ) does anyone know what the matter is? Did you try to make the first query for a second time, when you execute the second query? It seems that the server abords or closes the connection meanwhile. Did you waited too long to write/send the query? Each time i try to execute the 'buggy' query the server crashet. It crashes IMMEDIATELY (without any human-perceptible timeout) Can you provide the output of the logs? Check this for more help http://dev.mysql.com/doc/mysql/en/gone-away.html Does following the steps in that page helps? Try this query and output the result SHOW VARIABLES LIKE '%time%'; mysql SHOW VARIABLES LIKE '%time%'; +--+---+ | Variable_name| Value | +--+---+ | connect_timeout | 5 | | datetime_format | %Y-%m-%d %H:%i:%s | | delayed_insert_timeout | 300 | | flush_time | 0 | | innodb_lock_wait_timeout | 50| | interactive_timeout | 28800 | | long_query_time | 10| | net_read_timeout | 30| | net_write_timeout| 60| | slave_net_timeout| 3600 | | slow_launch_time | 2 | | sync_replication_timeout | 0 | | system_time_zone | MSD | | time_format | %H:%i:%s | | time_zone| SYSTEM| | wait_timeout | 28800 | +--+---+ 16 rows in set (0.00 sec) The variables seems OK. Next time reply TO THE LIST, others could help. -- Nuno Pereira email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: concat function problems
Hello Nuno, Tuesday, July 26, 2005, 8:53:33 PM, you wrote: [EMAIL PROTECTED] wrote: (...) When i try to execute the following query (...) i get this error: ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id:6 Current database: test ERROR 2013 (HY000): Lost connection to MySQL server during query but if i change my query to this one everything is fine SELECT COUNT(DISTINCT field1) as value1, CONCAT(field2, '') as value2 FROM table1, table2 GROUP BY value2 (here CONCAT(field2, '-') is replaced with CONCAT(field2, '') ) does anyone know what the matter is? Did you try to make the first query for a second time, when you execute the second query? It seems that the server abords or closes the connection meanwhile. Did you waited too long to write/send the query? Each time i try to execute the 'buggy' query the server crashet. It crashes IMMEDIATELY (without any human-perceptible timeout) Can you provide the output of the logs? mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=16777216 read_buffer_size=258048 max_used_connections=1 max_connections=100 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 92783 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 050727 10:39:41 mysqld restarted It is possible that mysqld could use up to 92783 K bytes of memory. I don't think the problem has anything to do with available memory size for a) there is enough free memory b) if not there is a good piece of swap c) even if i suggest the problem is in insufficient memory it is not clear why so much is required for execution of such a simple query Check this for more help http://dev.mysql.com/doc/mysql/en/gone-away.html Does following the steps in that page helps? Try this query and output the result SHOW VARIABLES LIKE '%time%'; mysql SHOW VARIABLES LIKE '%time%'; +--+---+ | Variable_name| Value | +--+---+ | connect_timeout | 5 | | datetime_format | %Y-%m-%d %H:%i:%s | | delayed_insert_timeout | 300 | | flush_time | 0 | | innodb_lock_wait_timeout | 50| | interactive_timeout | 28800 | | long_query_time | 10| | net_read_timeout | 30| | net_write_timeout| 60| | slave_net_timeout| 3600 | | slow_launch_time | 2 | | sync_replication_timeout | 0 | | system_time_zone | MSD | | time_format | %H:%i:%s | | time_zone| SYSTEM| | wait_timeout | 28800 | +--+---+ 16 rows in set (0.00 sec) The variables seems OK. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
concat function problems
i've got a strange problem with concat() function
i have the following data structure:
CREATE TABLE table1 (
field1 int(11) NOT NULL auto_increment,
PRIMARY KEY (field1)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 AUTO_INCREMENT=3;
CREATE TABLE table2 (
field2 varchar(255) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
INSERT INTO table1 VALUES (1);
INSERT INTO table1 VALUES (2);
INSERT INTO table2 VALUES ('test');
When i try to execute the following query
(the query is meaningless -- it is generated just for debug purpose.
the original query where the problem occured is too long and
complicated)
SELECT
COUNT(DISTINCT field1) as value1,
CONCAT(field2, '-') as value2
FROM
table1,
table2
GROUP
BY value2
i get this error:
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:6
Current database: test
ERROR 2013 (HY000): Lost connection to MySQL server during query
but if i change my query to this one everything is fine
SELECT
COUNT(DISTINCT field1) as value1,
CONCAT(field2, '') as value2
FROM
table1,
table2
GROUP
BY value2
(here CONCAT(field2, '-') is replaced with CONCAT(field2, '') )
does anyone know what the matter is?
ps
my mysql version is
mysql Ver 14.7 Distrib 4.1.12, for unknown-freebsd4.6 (i386) using EditLine
wrapper
and my system is FreeBSD 4.6
--
Best regards,
Sergey Averyanov
mailto:[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: concat function problems
[EMAIL PROTECTED] wrote:
i've got a strange problem with concat() function
i have the following data structure:
CREATE TABLE table1 (
field1 int(11) NOT NULL auto_increment,
PRIMARY KEY (field1)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 AUTO_INCREMENT=3;
CREATE TABLE table2 (
field2 varchar(255) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
INSERT INTO table1 VALUES (1);
INSERT INTO table1 VALUES (2);
INSERT INTO table2 VALUES ('test');
When i try to execute the following query
(the query is meaningless -- it is generated just for debug purpose.
the original query where the problem occured is too long and
complicated)
This is an important point. Do you get the reported error for the simplified
query below? If not, it is useless. To find the problem, we must have a
query that produces the problem.
SELECT
COUNT(DISTINCT field1) as value1,
CONCAT(field2, '-') as value2
FROM
table1,
table2
GROUP
BY value2
With 4.1.11 on Mac OS X 10.3.9, I get
+++
| value1 | value2 |
+++
| 2 | test- |
+++
1 row in set (0.09 sec)
i get this error:
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:6
Current database: test
ERROR 2013 (HY000): Lost connection to MySQL server during query
Does the mysql server actually crash, or are you just losing the connection
from your client? Have you checked the error log? Have you checked the
manual http://dev.mysql.com/doc/mysql/en/gone-away.html?
but if i change my query to this one everything is fine
SELECT
COUNT(DISTINCT field1) as value1,
CONCAT(field2, '') as value2
FROM
table1,
table2
GROUP
BY value2
(here CONCAT(field2, '-') is replaced with CONCAT(field2, '') )
does anyone know what the matter is?
Does the simple query
SELECT CONCAT('test', '-');
work or produce the same error?
ps
my mysql version is
mysql Ver 14.7 Distrib 4.1.12, for unknown-freebsd4.6 (i386) using EditLine wrapper
and my system is FreeBSD 4.6
I've not used EditLine wrapper with mysql. Is there any chance it is doing
something with '-'?
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: concat function problems
[EMAIL PROTECTED] wrote: (...) When i try to execute the following query (...) i get this error: ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id:6 Current database: test ERROR 2013 (HY000): Lost connection to MySQL server during query but if i change my query to this one everything is fine SELECT COUNT(DISTINCT field1) as value1, CONCAT(field2, '') as value2 FROM table1, table2 GROUP BY value2 (here CONCAT(field2, '-') is replaced with CONCAT(field2, '') ) does anyone know what the matter is? Did you try to make the first query for a second time, when you execute the second query? It seems that the server abords or closes the connection meanwhile. Did you waited too long to write/send the query? Check this for more help http://dev.mysql.com/doc/mysql/en/gone-away.html Try this query and output the result SHOW VARIABLES LIKE '%time%'; if you don't find a solution. -- Nuno Pereira email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: concat function problems
Hello mysql,
i've got a strange problem with concat() function
i have the following data structure:
CREATE TABLE table1 (
field1 int(11) NOT NULL auto_increment,
PRIMARY KEY (field1)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 AUTO_INCREMENT=3;
CREATE TABLE table2 (
field2 varchar(255) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
INSERT INTO table1 VALUES (1);
INSERT INTO table1 VALUES (2);
INSERT INTO table2 VALUES ('test');
When i try to execute the following query
(the query is meaningless -- it is generated just for debug purpose.
the original query where the problem occured is too long and
complicated)
SELECT
COUNT(DISTINCT field1) as value1,
CONCAT(field2, '-') as value2
FROM
table1,
table2
GROUP
BY value2
i get this error:
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:6
Current database: test
ERROR 2013 (HY000): Lost connection to MySQL server during query
This is an important point. Do you get the reported error for the simplified
query below? If not, it is useless. To find the problem, we must have a
query that produces the problem.
Yes, i have the problem exactly in THIS query
SELECT
COUNT(DISTINCT field1) as value1,
CONCAT(field2, '-') as value2
FROM
table1,
table2
GROUP
BY value2
With 4.1.11 on Mac OS X 10.3.9, I get
+++
| value1 | value2 |
+++
| 2 | test- |
+++
1 row in set (0.09 sec)
i get this error:
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:6
Current database: test
ERROR 2013 (HY000): Lost connection to MySQL server during query
Does the mysql server actually crash, or are you just losing the connection
from your client? Have you checked the error log? Have you checked the
manual http://dev.mysql.com/doc/mysql/en/gone-away.html?
Yes, it really crashes. mysqld restarts after that
but if i change my query to this one everything is fine
SELECT
COUNT(DISTINCT field1) as value1,
CONCAT(field2, '') as value2
FROM
table1,
table2
GROUP
BY value2
(here CONCAT(field2, '-') is replaced with CONCAT(field2, '') )
does anyone know what the matter is?
Does the simple query
SELECT CONCAT('test', '-');
work or produce the same error?
This query works.
This one does NOT:
SELECT
COUNT(DISTINCT field1) as value1,
CONCAT(field2, '-') as value2
FROM
table1,
table2
GROUP
BY value2
All of the following ones WORK fine too:
SELECT
max(field1) as value1,
CONCAT(field2, '-') as value2
FROM
table1,
table2
GROUP
BY value2
(here count is replaced with MAX() for experimental purposes)
SELECT
COUNT(field1) as value1,
CONCAT(field2, '-') as value2
FROM
table1,
table2
GROUP
BY value2
(here distinct is removed)
SELECT
COUNT(field1) as value1,
CONCAT(field2, '-') as value2
FROM
table2,
table1
GROUP
BY value2
(here tables order in FROM is changed)
ps
my mysql version is
mysql Ver 14.7 Distrib 4.1.12, for unknown-freebsd4.6 (i386) using
EditLine wrapper
and my system is FreeBSD 4.6
I've not used EditLine wrapper with mysql. Is there any chance it is doing
something with '-'?
no, i've tried other symbols too
--
Best regards,
averyanov mailto:[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to SELECT something (CONCAT) and search the field
Hey All- Got a fun question - I hit the manual but not much luck on my question. I want to combine 2 fields and then search them SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ', last_name) LIKE '%$user%' Does this make sense? The CONCAT function was the closest I found to try and do what I want to do. I alread tried this: SELECT concat(first_name, ' ', last_name) as fullname FROM user... This did not work. If anyone has any ideas on how to search for users when the first_name and last_name fields are broken up I'm all ears! Thanks, Matt Babineau Criticalcode 858.733.0160 [EMAIL PROTECTED] http://www.criticalcode.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to SELECT something (CONCAT) and search the field
select first_name, lastname from user where first_name like '%$user%' or last_name like '%$user%' ; --- Matt Babineau [EMAIL PROTECTED] wrote: Hey All- Got a fun question - I hit the manual but not much luck on my question. I want to combine 2 fields and then search them SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ', last_name) LIKE '%$user%' Does this make sense? The CONCAT function was the closest I found to try and do what I want to do. I alread tried this: SELECT concat(first_name, ' ', last_name) as fullname FROM user... This did not work. If anyone has any ideas on how to search for users when the first_name and last_name fields are broken up I'm all ears! Thanks, Matt Babineau Criticalcode 858.733.0160 [EMAIL PROTECTED] http://www.criticalcode.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Make Yahoo! your home page http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to SELECT something (CONCAT) and search the field
Hi,
what's your version ? in 4.11 the two forms work :
mysql select concat(firstname,' ','lastname') from names;
+--+
| concat(firstname,' ','lastname') |
+--+
| Jean lastname|
+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where concat(firstname,'
','lastname') like '%J%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where
concat(rtrim(ltrim(firstname)),' ',lastname) like '%Jean Dupond%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.02 sec)
mysql select firstname,lastname from names where concat(firstname,' ',lastname)
like 'Jean Dupond';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
BUT i Think that :
*
to use index on firstname or lastname, it's better to split $user rather than
concat the two columns :
mysql select firstname,lastname from names where firstname like
substring_index('%Jean Dupond%',' ',1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where lastname like
substring_index('%Jean Dupond%',' ',-1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
Mathias
Selon Matt Babineau [EMAIL PROTECTED]:
Hey All-
Got a fun question - I hit the manual but not much luck on my question. I
want to combine 2 fields and then search them
SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ',
last_name) LIKE '%$user%'
Does this make sense? The CONCAT function was the closest I found to try and
do what I want to do. I alread tried this:
SELECT concat(first_name, ' ', last_name) as fullname FROM user...
This did not work. If anyone has any ideas on how to search for users when
the first_name and last_name fields are broken up I'm all ears!
Thanks,
Matt Babineau
Criticalcode
858.733.0160
[EMAIL PROTECTED]
http://www.criticalcode.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to SELECT something (CONCAT) and search the field
sorry for the first select (bad copy of a string 'lastname'):
mysql select concat(firstname,' ',lastname) from names where concat(firstname,'
',lastname) like 'Jean Dupond%';
++
| concat(firstname,' ',lastname) |
++
| Jean Dupond|
++
1 row in set (0.00 sec)
mathias
Selon [EMAIL PROTECTED]:
Hi,
what's your version ? in 4.11 the two forms work :
mysql select concat(firstname,' ','lastname') from names;
+--+
| concat(firstname,' ','lastname') |
+--+
| Jean lastname|
+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where concat(firstname,'
','lastname') like '%J%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where
concat(rtrim(ltrim(firstname)),' ',lastname) like '%Jean Dupond%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.02 sec)
mysql select firstname,lastname from names where concat(firstname,'
',lastname)
like 'Jean Dupond';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
BUT i Think that :
*
to use index on firstname or lastname, it's better to split $user rather than
concat the two columns :
mysql select firstname,lastname from names where firstname like
substring_index('%Jean Dupond%',' ',1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where lastname like
substring_index('%Jean Dupond%',' ',-1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
Mathias
Selon Matt Babineau [EMAIL PROTECTED]:
Hey All-
Got a fun question - I hit the manual but not much luck on my question. I
want to combine 2 fields and then search them
SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ',
last_name) LIKE '%$user%'
Does this make sense? The CONCAT function was the closest I found to try
and
do what I want to do. I alread tried this:
SELECT concat(first_name, ' ', last_name) as fullname FROM user...
This did not work. If anyone has any ideas on how to search for users when
the first_name and last_name fields are broken up I'm all ears!
Thanks,
Matt Babineau
Criticalcode
858.733.0160
[EMAIL PROTECTED]
http://www.criticalcode.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to SELECT something (CONCAT) and search the field
the substring will only work as long as you don't have spaces in the first
and last name columns
'Billy Ray' Smith and John 'Von Hoenhiem' would cause problems
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 23, 2005 4:05 PM
To: Matt Babineau
Cc: mysql@lists.mysql.com
Subject: Re: How to SELECT something (CONCAT) and search the field
Hi,
what's your version ? in 4.11 the two forms work :
mysql select concat(firstname,' ','lastname') from names;
+--+
| concat(firstname,' ','lastname') |
+--+
| Jean lastname|
+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where concat(firstname,'
','lastname') like '%J%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where
concat(rtrim(ltrim(firstname)),' ',lastname) like '%Jean Dupond%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.02 sec)
mysql select firstname,lastname from names where concat(firstname,'
',lastname)
like 'Jean Dupond';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
BUT i Think that :
*
to use index on firstname or lastname, it's better to split $user rather
than
concat the two columns :
mysql select firstname,lastname from names where firstname like
substring_index('%Jean Dupond%',' ',1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where lastname like
substring_index('%Jean Dupond%',' ',-1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
Mathias
Selon Matt Babineau [EMAIL PROTECTED]:
Hey All-
Got a fun question - I hit the manual but not much luck on my question. I
want to combine 2 fields and then search them
SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ',
last_name) LIKE '%$user%'
Does this make sense? The CONCAT function was the closest I found to try
and
do what I want to do. I alread tried this:
SELECT concat(first_name, ' ', last_name) as fullname FROM user...
This did not work. If anyone has any ideas on how to search for users when
the first_name and last_name fields are broken up I'm all ears!
Thanks,
Matt Babineau
Criticalcode
858.733.0160
[EMAIL PROTECTED]
http://www.criticalcode.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to SELECT something (CONCAT) and search the field
yes in therory. But practicaly, you always have business rules and data
knowledge without what you can do nothing.
so the substring must be constructed according to data.
Mathias
Selon Ben Kutsch [EMAIL PROTECTED]:
the substring will only work as long as you don't have spaces in the first
and last name columns
'Billy Ray' Smith and John 'Von Hoenhiem' would cause problems
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 23, 2005 4:05 PM
To: Matt Babineau
Cc: mysql@lists.mysql.com
Subject: Re: How to SELECT something (CONCAT) and search the field
Hi,
what's your version ? in 4.11 the two forms work :
mysql select concat(firstname,' ','lastname') from names;
+--+
| concat(firstname,' ','lastname') |
+--+
| Jean lastname|
+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where concat(firstname,'
','lastname') like '%J%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where
concat(rtrim(ltrim(firstname)),' ',lastname) like '%Jean Dupond%';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.02 sec)
mysql select firstname,lastname from names where concat(firstname,'
',lastname)
like 'Jean Dupond';
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
BUT i Think that :
*
to use index on firstname or lastname, it's better to split $user rather
than
concat the two columns :
mysql select firstname,lastname from names where firstname like
substring_index('%Jean Dupond%',' ',1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
mysql select firstname,lastname from names where lastname like
substring_index('%Jean Dupond%',' ',-1);
+---+--+
| firstname | lastname |
+---+--+
| Jean | Dupond |
+---+--+
1 row in set (0.00 sec)
Mathias
Selon Matt Babineau [EMAIL PROTECTED]:
Hey All-
Got a fun question - I hit the manual but not much luck on my question. I
want to combine 2 fields and then search them
SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ',
last_name) LIKE '%$user%'
Does this make sense? The CONCAT function was the closest I found to try
and
do what I want to do. I alread tried this:
SELECT concat(first_name, ' ', last_name) as fullname FROM user...
This did not work. If anyone has any ideas on how to search for users when
the first_name and last_name fields are broken up I'm all ears!
Thanks,
Matt Babineau
Criticalcode
858.733.0160
[EMAIL PROTECTED]
http://www.criticalcode.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to SELECT something (CONCAT) and search the field
I do this all the time and it works flawlessly. Just like your example and even
more extreme. I use this technique to provide search mechamisms for my
applications. ex,
SELECT ID
FROM table
WHERE concat(field1, field2, field3,...{all the fields in the table}) Like
'%searchstring%';
This works great for me. Is 'user' your actually table name and is it possible
that the table name is being confused with the 'user' table in the MySQL
database?
Good Luck
Matt Babineau [EMAIL PROTECTED] 6/23/05 1:50:12 PM
Hey All-
Got a fun question - I hit the manual but not much luck on my question. I
want to combine 2 fields and then search them
SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ',
last_name) LIKE '%$user%'
Does this make sense? The CONCAT function was the closest I found to try and
do what I want to do. I alread tried this:
SELECT concat(first_name, ' ', last_name) as fullname FROM user...
This did not work. If anyone has any ideas on how to search for users when
the first_name and last_name fields are broken up I'm all ears!
Thanks,
Matt Babineau
Criticalcode
858.733.0160
[EMAIL PROTECTED]
http://www.criticalcode.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to SELECT something (CONCAT) and search the field
While the query WHERE expr LIKE '%$user%' works without fail, it can
not use an index, and thus on large tables will be exceedingly slow.
mysql explain select last, first from users where concat(last,first)
like '%user%'\G
*** 1. row ***
id: 1
select_type: SIMPLE
table: users
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra: Using where
1 row in set (0.01 sec)
Therefor, where it's possible, I would suggest adding a fulltext index
on (first, last), or what ever columns you need. This will handle the
concatenation of these two columns inside the index :)
mysql alter table users add fulltext index (first, last);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
Then use the query WHERE MATCH expr AGAINST ('%$user%').. like this...
mysql explain select first, last from users where match (first,last)
against ('%user%')\G
*** 1. row ***
id: 1
select_type: SIMPLE
table: users
type: fulltext
possible_keys: first_2
key: first_2
key_len: 0
ref:
rows: 1
Extra: Using where
1 row in set (0.01 sec)
Hope that helps,
~Deva
Ed Reed wrote:
I do this all the time and it works flawlessly. Just like your example and even more extreme. I use this technique to provide search mechamisms for my applications. ex,
SELECT ID
FROM table
WHERE concat(field1, field2, field3,...{all the fields in the table}) Like '%searchstring%';
This works great for me. Is 'user' your actually table name and is it possible that the table name is being confused with the 'user' table in the MySQL database?
Good Luck
Matt Babineau [EMAIL PROTECTED] 6/23/05 1:50:12 PM
Hey All-
Got a fun question - I hit the manual but not much luck on my question. I
want to combine 2 fields and then search them
SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ',
last_name) LIKE '%$user%'
Does this make sense? The CONCAT function was the closest I found to try and
do what I want to do. I alread tried this:
SELECT concat(first_name, ' ', last_name) as fullname FROM user...
This did not work. If anyone has any ideas on how to search for users when
the first_name and last_name fields are broken up I'm all ears!
Thanks,
Matt Babineau
Criticalcode
858.733.0160
[EMAIL PROTECTED]
http://www.criticalcode.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RENAME TABLE with CONCAT string fails
I'm trying to rename some tables for archival, but the table renaming is failing when I use CONCAT() to form the table string name: mysql RENAME TABLE flows TO flows_tmp, flows_new TO flows, flows_tmp TO CONCAT(flows_, DATE_SUB(CURDATE(), interval 1 day)); ERROR 1064: You have an error in your SQL syntax near 'CONCAT(flows_, DATE_SUB(CURDATE(), interval 1 day))' at line 1 If I run the CONCAT() command by itself, everything works as expected: ++ | CONCAT(flows_, DATE_SUB(CURDATE(), interval 1 day)) | ++ | flows_2005-05-27 | ++ 1 row in set (0.00 sec) Is there some magic pixie dust I need to throw around the CONCAT() to have it interpolate into the RENAME TABLE command correctly? This is on mysql-server 3.23.58 on a RHEL 3 clone. Thanks, -- Jason Dixon DixonGroup Consulting http://www.dixongroup.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RENAME TABLE with CONCAT string fails
At 8:12 -0400 5/28/05, Jason Dixon wrote: I'm trying to rename some tables for archival, but the table renaming is failing when I use CONCAT() to form the table string name: CONCAT() produces a string, not an identifier. mysql RENAME TABLE flows TO flows_tmp, flows_new TO flows, flows_tmp TO CONCAT(flows_, DATE_SUB(CURDATE(), interval 1 day)); ERROR 1064: You have an error in your SQL syntax near 'CONCAT(flows_, DATE_SUB(CURDATE(), interval 1 day))' at line 1 If I run the CONCAT() command by itself, everything works as expected: ++ | CONCAT(flows_, DATE_SUB(CURDATE(), interval 1 day)) | ++ | flows_2005-05-27 | ++ 1 row in set (0.00 sec) Is there some magic pixie dust I need to throw around the CONCAT() to have it interpolate into the RENAME TABLE command correctly? This is on mysql-server 3.23.58 on a RHEL 3 clone. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RENAME TABLE with CONCAT string fails
On May 28, 2005, at 8:51 AM, Paul DuBois wrote: At 8:12 -0400 5/28/05, Jason Dixon wrote: I'm trying to rename some tables for archival, but the table renaming is failing when I use CONCAT() to form the table string name: CONCAT() produces a string, not an identifier. Fine. Is there any way to do this in MySQL or do I need to fall back on my Perl? It's not a big deal, I'm just curious now. Thanks, -- Jason Dixon DixonGroup Consulting http://www.dixongroup.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RENAME TABLE with CONCAT string fails
At 11:00 -0400 5/28/05, Jason Dixon wrote: On May 28, 2005, at 8:51 AM, Paul DuBois wrote: At 8:12 -0400 5/28/05, Jason Dixon wrote: I'm trying to rename some tables for archival, but the table renaming is failing when I use CONCAT() to form the table string name: CONCAT() produces a string, not an identifier. Fine. Is there any way to do this in MySQL or do I need to fall back on my Perl? It's not a big deal, I'm just curious now. Perl. Construct the table identifier and place the result into your SQL statement, then execute the statement. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RENAME TABLE with CONCAT string fails
Hi,
As Paul said, since concat gives a string, you can use this fact in preparing
statement (v4.1). This works fine for me :
But use replace to change '-' to '_' in the table_name.
set @tt:=concat('rename table flows_2005_05_27 to ',CONCAT(flows_,
replace(DATE_SUB(CURDATE(), interval 2 day),'-','_')),';');
select @tt;
mysql show tables like 'flow%';
+-+
| Tables_in_world (flow%) |
+-+
| flows_2005_05_27|
+-+
1 row in set (0.00 sec)
mysql
mysql set @tt:=concat('rename table flows_2005_05_27 to ',CONCAT(flows_,
replace(DATE_SUB(CURDATE(), interval 2 day),'-','_')),';');
mysql prepare stmt from @tt;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql execute stmt ;
mysql deallocate prepare stmt;
mysql show tables like 'flow%';
+-+
| Tables_in_world (flow%) |
+-+
| flows_2005_05_26|
+-+
1 row in set (0.00 sec)
Mathias
Selon Paul DuBois [EMAIL PROTECTED]:
At 11:00 -0400 5/28/05, Jason Dixon wrote:
On May 28, 2005, at 8:51 AM, Paul DuBois wrote:
At 8:12 -0400 5/28/05, Jason Dixon wrote:
I'm trying to rename some tables for archival, but the table
renaming is failing when I use CONCAT() to form the table string
name:
CONCAT() produces a string, not an identifier.
Fine. Is there any way to do this in MySQL or do I need to fall
back on my Perl? It's not a big deal, I'm just curious now.
Perl. Construct the table identifier and place the result into your
SQL statement, then execute the statement.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RENAME TABLE with CONCAT string fails
At 17:50 +0200 5/28/05, [EMAIL PROTECTED] wrote:
Hi,
As Paul said, since concat gives a string, you can use this fact in preparing
statement (v4.1). This works fine for me :
Ah, yes. This'll work. I forgot about prepared statements. :-)
But use replace to change '-' to '_' in the table_name.
set @tt:=concat('rename table flows_2005_05_27 to ',CONCAT(flows_,
replace(DATE_SUB(CURDATE(), interval 2 day),'-','_')),';');
select @tt;
mysql show tables like 'flow%';
+-+
| Tables_in_world (flow%) |
+-+
| flows_2005_05_27|
+-+
1 row in set (0.00 sec)
mysql
mysql set @tt:=concat('rename table flows_2005_05_27 to ',CONCAT(flows_,
replace(DATE_SUB(CURDATE(), interval 2 day),'-','_')),';');
mysql prepare stmt from @tt;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql execute stmt ;
mysql deallocate prepare stmt;
mysql show tables like 'flow%';
+-+
| Tables_in_world (flow%) |
+-+
| flows_2005_05_26|
+-+
1 row in set (0.00 sec)
Mathias
Selon Paul DuBois [EMAIL PROTECTED]:
At 11:00 -0400 5/28/05, Jason Dixon wrote:
On May 28, 2005, at 8:51 AM, Paul DuBois wrote:
At 8:12 -0400 5/28/05, Jason Dixon wrote:
I'm trying to rename some tables for archival, but the table
renaming is failing when I use CONCAT() to form the table string
name:
CONCAT() produces a string, not an identifier.
Fine. Is there any way to do this in MySQL or do I need to fall
back on my Perl? It's not a big deal, I'm just curious now.
Perl. Construct the table identifier and place the result into your
SQL statement, then execute the statement.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CONCAT
hi, how to concat 2 columns to display in report? select column1concat column2 form table1; Concatenation Operator pl? thanks - Discover Yahoo! Stay in touch with email, IM, photo sharing more. Check it out!
RE: CONCAT
[snip]
how to concat 2 columns to display in report?
select column1concat column2 form table1;
Concatenation Operator pl?
[/snip]
select concat('foo', 'bar')
results foobar
select concat('foo', ' ', 'bar')
results foo bar
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
concat multirow subselect
Hi, it is possible to subj? I have two tables. create table aaa (id int auto_increment not null, title varchar(255), primary key (id)); create table bbb (id int auto_increment not null, fk_aaa int not null, detail varchar(255), primary key (id)); insert into aaa values (1, 'aaa'), (2, 'bbb'); insert into bbb values (1, 1, '123'), (2, 1, '456'), (3, 2, '789'); I need display table aaa with last column concat values from bbb. Like this: | 1 | 'aaa' | '123 456' | | 2 | 'bbb' | '789' | It is possible with version 4.1.x? Or I must upgrade to 5.0.x and use stored procedures? Stano. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: concat multirow subselect
Stano Paska wrote: Hi, it is possible to subj? I have two tables. create table aaa (id int auto_increment not null, title varchar(255), primary key (id)); create table bbb (id int auto_increment not null, fk_aaa int not null, detail varchar(255), primary key (id)); insert into aaa values (1, 'aaa'), (2, 'bbb'); insert into bbb values (1, 1, '123'), (2, 1, '456'), (3, 2, '789'); I need display table aaa with last column concat values from bbb. Like this: | 1 | 'aaa' | '123 456' | | 2 | 'bbb' | '789' | It is possible with version 4.1.x? Or I must upgrade to 5.0.x and use stored procedures? Stano. In general, you can simply have your app output a newline, id, and title only when the id changes. Starting with 4.1, you can get the same result using GROUP_CONCAT(). See the manual for the details http://dev.mysql.com/doc/mysql/en/group-by-functions.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: concat multirow subselect
Michael Stassen wrote: Stano Paska wrote: Hi, it is possible to subj? I have two tables. create table aaa (id int auto_increment not null, title varchar(255), primary key (id)); create table bbb (id int auto_increment not null, fk_aaa int not null, detail varchar(255), primary key (id)); insert into aaa values (1, 'aaa'), (2, 'bbb'); insert into bbb values (1, 1, '123'), (2, 1, '456'), (3, 2, '789'); I need display table aaa with last column concat values from bbb. Like this: | 1 | 'aaa' | '123 456' | | 2 | 'bbb' | '789' | It is possible with version 4.1.x? Or I must upgrade to 5.0.x and use stored procedures? Stano. In general, you can simply have your app output a newline, id, and title only when the id changes. Starting with 4.1, you can get the same result using GROUP_CONCAT(). See the manual for the details http://dev.mysql.com/doc/mysql/en/group-by-functions.html. Michael Yes, this is what I need. I didn't read this part of manual. :-( Thank you. S. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
float type / concat
Hi list,
I'm using php/mysql, I was updating a table through phpmyadmin then I saw
that to update all the columns which type is declared to float the
developper of phpmyadmin have added a concat.
Something like:
UPDATE `tableInduction` SET `inductionType` = 'screening' AND CONCAT(
`volume` ) = '1' AND `growthVessel` = '24-96 well plates' AND CONCAT(
`pH_start` ) = '2'.
the column volume and pH_start are of type float, and it's the same for all
the columns I declare as float.
I suppose that this has some purpose, but I don't understand.
concat is an operator to merge 2 strings, for example concat
('string1','string2') should return string1string2.
What's the point of doing that for column's name?Is it a kind of cast
because '1' is considered as string because of the quotes?
In the manual I saw:
select concat(14.3) gives as a result '14.3'.
Is that the same kind of cast?
Why isn't that done for integers?
Additionally, I want to store 1 or 2 decimals floating point number, what is
the best?float?double?
I saw that using float may give you some unexpected problems, as all
calculations in MySQL are done with double-precision.
Thanks for your advice,
Melanie
_
It's fast, it's easy and it's free. Get MSN Messenger today!
http://www.msn.co.uk/messenger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update and concat
Someone else suggested that I remove all spaces before and after = and
between CONCAT and (...). This worked. Too bad the manual is not more
specific.
Thanks for your concern,
Tom
On Nov 30, 2004, at 7:14 PM, Michael Stassen wrote:
At this point, what you say you are doing should work, but doesn't.
We cannot guess what's wrong. Please enter your command,
UPDATE listings SET map = CONCAT(ML, '.png') WHERE ML '';
get your error message, and then copy/paste the whole thing into your
next message. That way, someone should spot the problem.
Michael
Thomas McDonough wrote:
That did not do it. I'm still getting the same error message (?)
Tom
On Nov 30, 2004, at 2:09 PM, gerald_clark wrote:
Thomas McDonough wrote:
I'm trying to set all the values of column 'map' to the value of
column ML and '.png'. My intuition and an extensive reading of the
manual and mail archives tell me to do it like this:
mysql update listings set map= concat (ML, '.png') where ML'';
mysql update listings set map= concat(ML, '.png') where ML'';
Lose the space between 'concat' and '('
but all I get is this:
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near '(ML, '.png') where ML''' at line 1
What am I doing wrong?
Tom
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update and concat
Spaces on either side of = are not a problem, but you must not put a space
between the name of a function and the opening parenthesis. From the manual
http://dev.mysql.com/doc/mysql/en/Functions.html, Note: By default, there
must be no whitespace between a function name and the parenthesis following
it. This helps the MySQL parser distinguish between function calls and
references to tables or columns that happen to have the same name as a
function. Spaces around function arguments are permitted, though.
Michael
Thomas McDonough wrote:
Someone else suggested that I remove all spaces before and after = and
between CONCAT and (...). This worked. Too bad the manual is not more
specific.
Thanks for your concern,
Tom
On Nov 30, 2004, at 7:14 PM, Michael Stassen wrote:
At this point, what you say you are doing should work, but doesn't.
We cannot guess what's wrong. Please enter your command,
UPDATE listings SET map = CONCAT(ML, '.png') WHERE ML '';
get your error message, and then copy/paste the whole thing into your
next message. That way, someone should spot the problem.
Michael
Thomas McDonough wrote:
That did not do it. I'm still getting the same error message (?)
Tom
On Nov 30, 2004, at 2:09 PM, gerald_clark wrote:
Thomas McDonough wrote:
I'm trying to set all the values of column 'map' to the value of
column ML and '.png'. My intuition and an extensive reading of the
manual and mail archives tell me to do it like this:
mysql update listings set map= concat (ML, '.png') where ML'';
mysql update listings set map= concat(ML, '.png') where ML'';
Lose the space between 'concat' and '('
but all I get is this:
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near '(ML, '.png') where ML''' at line 1
What am I doing wrong?
Tom
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
update and concat
I'm trying to set all the values of column 'map' to the value of column ML and '.png'. My intuition and an extensive reading of the manual and mail archives tell me to do it like this: mysql update listings set map= concat (ML, '.png') where ML''; but all I get is this: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(ML, '.png') where ML''' at line 1 What am I doing wrong? Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update and concat
Thomas McDonough wrote: I'm trying to set all the values of column 'map' to the value of column ML and '.png'. My intuition and an extensive reading of the manual and mail archives tell me to do it like this: mysql update listings set map= concat (ML, '.png') where ML''; but all I get is this: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(ML, '.png') where ML''' at line 1 What am I doing wrong? Don't use a space between 'concat' and the parantheses, it is a function call: update listings set map= concat(ML, '.png') where ML''; -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update and concat
Thomas McDonough wrote:
I'm trying to set all the values of column 'map' to the value of
column ML and '.png'. My intuition and an extensive reading of the
manual and mail archives tell me to do it like this:
mysql update listings set map= concat (ML, '.png') where ML'';
mysql update listings set map= concat(ML, '.png') where ML'';
Lose the space between 'concat' and '('
but all I get is this:
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near '(ML, '.png') where ML''' at line 1
What am I doing wrong?
Tom
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update and concat
That did not do it. I'm still getting the same error message (?)
Tom
On Nov 30, 2004, at 2:09 PM, gerald_clark wrote:
Thomas McDonough wrote:
I'm trying to set all the values of column 'map' to the value of
column ML and '.png'. My intuition and an extensive reading of the
manual and mail archives tell me to do it like this:
mysql update listings set map= concat (ML, '.png') where ML'';
mysql update listings set map= concat(ML, '.png') where ML'';
Lose the space between 'concat' and '('
but all I get is this:
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near '(ML, '.png') where ML''' at line 1
What am I doing wrong?
Tom
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update and concat
At this point, what you say you are doing should work, but doesn't. We
cannot guess what's wrong. Please enter your command,
UPDATE listings SET map = CONCAT(ML, '.png') WHERE ML '';
get your error message, and then copy/paste the whole thing into your next
message. That way, someone should spot the problem.
Michael
Thomas McDonough wrote:
That did not do it. I'm still getting the same error message (?)
Tom
On Nov 30, 2004, at 2:09 PM, gerald_clark wrote:
Thomas McDonough wrote:
I'm trying to set all the values of column 'map' to the value of
column ML and '.png'. My intuition and an extensive reading of the
manual and mail archives tell me to do it like this:
mysql update listings set map= concat (ML, '.png') where ML'';
mysql update listings set map= concat(ML, '.png') where ML'';
Lose the space between 'concat' and '('
but all I get is this:
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near '(ML, '.png') where ML''' at line 1
What am I doing wrong?
Tom
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.0 and concat
Hi: I have a problem in that all statements that include concat execute very slowly. For instance, if I have three fields in string format that represent a year, month and day, and want to issue a select like: select * from cxcmanpag where contact (year,month,day)=stringYear+stringMonth+stringDay (simplified), then it will take a long time, againts a table with only around 100,00 records. If I rewrite the statement to read: select * from cxcmanpag where year=stringYear and month=stringMonth and day=stringDay, it will execute considerable faster, but will not produce the same results. I have looked in the manual, and also read High Performance MySQL from Zawodny and Balling, and MySQL from Paul Dubois, but none of them seem to address this issue. Can somebody point me to a URL or book that I should be reading to improve, this, or how to avoid using concat altogether? Thank you. -- Alfredo J. Cole Grupo ACyC www.acyc.com - www.clshonduras.com - SolCom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0 and concat
Have you considered NOT comparing dates as strings but rather as date values? That will avoid the use of CONCAT() completely. SELECT * FROM sampletable WHERE datefield = '1999-01-12' and datefield '1999-02-01' This example query will get all of the records from sampletable that were entered after January 11th and before February 1st. It will also be **very** fast if the column datefield is indexed. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Alfredo Cole [EMAIL PROTECTED] wrote on 10/11/2004 09:43:14 AM: Hi: I have a problem in that all statements that include concat execute very slowly. For instance, if I have three fields in string format that represent a year, month and day, and want to issue a select like: select * from cxcmanpag where contact (year,month,day)=stringYear+stringMonth+stringDay (simplified), then it will take a long time, againts a table with only around 100,00 records. If I rewrite the statement to read: select * from cxcmanpag where year=stringYear and month=stringMonth and day=stringDay, it will execute considerable faster, but will not produce the same results. I have looked in the manual, and also read High Performance MySQL from Zawodny and Balling, and MySQL from Paul Dubois, but none of them seem to address this issue. Can somebody point me to a URL or book that I should be reading to improve, this, or how to avoid using concat altogether? Thank you. -- Alfredo J. Cole Grupo ACyC www.acyc.com - www.clshonduras.com - SolCom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: Re: MySQL 4.0 and concat
Sorry. This should have gone back to the list. -- Mensaje reenviado -- Subject: Re: MySQL 4.0 and concat Date: Lun 11 Oct 2004 11:37 From: Alfredo Cole [EMAIL PROTECTED] To: [EMAIL PROTECTED] El Lun 11 Oct 2004 08:35, escribió: Have you considered NOT comparing dates as strings but rather as date values? That will avoid the use of CONCAT() completely. I will try this. But there will always be times when using concat might be required. It would be nice to know if there is a solution to the concat problem. Thank you, and regards. -- Alfredo J. Cole Grupo ACyC www.acyc.com - www.clshonduras.com - SolCom --- -- Alfredo J. Cole Grupo ACyC www.acyc.com - www.clshonduras.com - SolCom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0 and concat
A bit of a warning, if the fields are DATETIME rather than DATE, add the appropriate hours:minutes:seconds to the test WHERE datetimefield '1999-02-01 23:59:59'. When comparing DATETIME fields with date strings '00:00:00' is assumed and that can cause problems if one forgets that. Have you considered NOT comparing dates as strings but rather as date values? That will avoid the use of CONCAT() completely. SELECT * FROM sampletable WHERE datefield = '1999-01-12' and datefield '1999-02-01' This example query will get all of the records from sampletable that were entered after January 11th and before February 1st. It will also be **very** fast if the column datefield is indexed. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Alfredo Cole [EMAIL PROTECTED] wrote on 10/11/2004 09:43:14 AM: Hi: I have a problem in that all statements that include concat execute very slowly. For instance, if I have three fields in string format that represent a year, month and day, and want to issue a select like: select * from cxcmanpag where contact (year,month,day)=stringYear+stringMonth+stringDay (simplified), then it will take a long time, againts a table with only around 100,00 records. If I rewrite the statement to read: select * from cxcmanpag where year=stringYear and month=stringMonth and day=stringDay, it will execute considerable faster, but will not produce the same results. I have looked in the manual, and also read High Performance MySQL from Zawodny and Balling, and MySQL from Paul Dubois, but none of them seem to address this issue. Can somebody point me to a URL or book that I should be reading to improve, this, or how to avoid using concat altogether? Thank you. -- Alfredo J. Cole Grupo ACyC www.acyc.com - www.clshonduras.com - SolCom -- William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Concat
Hi, Concat() returns Null if any field in the field list is Null. How can I use it to return a String regardless, or is there another function to do it? regards ___ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concat
select concat(ifnull(Field1,'NULL'), Filed2) from My Table; - Original Message - From: A Z [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 31, 2004 2:25 PM Subject: Concat Hi, Concat() returns Null if any field in the field list is Null. How can I use it to return a String regardless, or is there another function to do it? regards ___ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concat
Concat() returns Null if any field in the field list is Null. How can I use it to return a String regardless, or is there another function to do it? COALESCE(value,...) Returns the first non-NULL value in the list. mysql SELECT COALESCE(NULL,1); - 1 mysql SELECT COALESCE(NULL,NULL,NULL); - NULL COALESCE() was added in MySQL 3.23.3. So, if you use CONCAT(COALESCE(Myfield, ''), COALESCE(OtherField, '') ) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concat fields
Egor Egorov wrote: Paul McNeil [EMAIL PROTECTED] wrote: If I am not running 4, is there another way to achieve the same result as GROUP_CONCAT(myField) ? I don't think so. :( Except if you are ready to make a User-defined function to make a simple group_concat... http://dev.mysql.com/doc/mysql/en/UDF_aggr._calling.html -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concat fields
Philippe Poelvoorde [EMAIL PROTECTED] wrote: If I am not running 4, is there another way to achieve the same result as GROUP_CONCAT(myField) ? I don't think so. :( Except if you are ready to make a User-defined function to make a simple group_concat... http://dev.mysql.com/doc/mysql/en/UDF_aggr._calling.html Which is probably an overkill for most cases. :) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concat fields
Philippe, If he is pre-4 (I assume that as he does not seem to have the GROUP_CONCAT() function) and UDFs aren't available until 5+, how exactly would he do this as a UDF? :-D Paul, I believe you are going to have to combine those fields during some form of post-query processing (macro, script, program, etc). Sorry! Shawn Green Database Administrator Unimin Corporation - Spruce Pine Philippe Poelvoorde [EMAIL PROTECTED] wrote on 08/12/2004 05:20:49 AM: Egor Egorov wrote: Paul McNeil [EMAIL PROTECTED] wrote: If I am not running 4, is there another way to achieve the same result as GROUP_CONCAT(myField) ? I don't think so. :( Except if you are ready to make a User-defined function to make a simple group_concat... http://dev.mysql.com/doc/mysql/en/UDF_aggr._calling.html -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Concat fields
Good morning to all. How can I combine the elements from many text fields into one text field. I have a table. ID(auto inc) eventID data ID is unique eventID is repeated data represents what happened at this event. I would like to return all of the data for a given event ID, sorted by ID. Thank you. God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. Microneil Research Sortmonster Anti Spam GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concat fields
Paul McNeil [EMAIL PROTECTED] wrote: How can I combine the elements from many text fields into one text field. See http://dev.mysql.com/doc/mysql/en/String_functions.html, in particular, you need CONCAT() I have a table. ID(auto inc) eventID data ID is unique eventID is repeated data represents what happened at this event. I would like to return all of the data for a given event ID, sorted by ID. It's like that: SELECT data FROM table WHERE eventID = ? ORDER BY ID; In case you need to concatenate the data in SELECT, here's a clue: SELECT concat(data1,' ',data2,' ',data3) FROM table WHERE eventID = ? ORDER BY ID; Blessings, -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE Concat fields
Thank you for your response, Egor. My question is whether or not it is possible to concatenate the fields without knowing the event ID. pseudo SQL Select concat(data from all grouped fields) from foo group by (eventID); God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. Microneil Research Sortmonster Anti Spam GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RE Concat fields
If you use mysql 4.1, you can use GROUP_CONCAT(). See the manual for details http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html. Michael Paul McNeil wrote: Thank you for your response, Egor. My question is whether or not it is possible to concatenate the fields without knowing the event ID. pseudo SQL Select concat(data from all grouped fields) from foo group by (eventID); God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. Microneil Research Sortmonster Anti Spam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Concat fields
If I am not running 4, is there another way to achieve the same result as GROUP_CONCAT(myField) ? God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. Microneil research Sortmonster Anti Spam GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concat fields
Paul McNeil [EMAIL PROTECTED] wrote: If I am not running 4, is there another way to achieve the same result as GROUP_CONCAT(myField) ? I don't think so. :( -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large inserts, chunking and Concat
Part of the problem was the way the server settings were allocating memory. The concat would work until a certain size and then the suddenly failed to insert (inserted NULL). After the memory configuration change, in terms of concatenating (with CONCAT) in pieces, it seems to work at least in my test to about 240MBs. There may still be a problem as the data grows beyond this. John Harald Fuchs wrote: In article [EMAIL PROTECTED], John Ling [EMAIL PROTECTED] writes: Hello, realizing that there is a max_allowed_packet setting that limits the size of the insert statement, is there a way around it by chunking the query? In particular, if the query is to insert a large text or blob, can I simply concatenate smaller pieces of the data in succession using the Concat command? My concern is whether this will still in someway cause me other MySQL resource problems? I want to be able to insert a large text or blob of over 200-400MBs. I tried INSERTing in chunks with concat() a few months ago and found out that it didn't work; thus effectively your blob size is limited by max_allowed_packet. But since you can increase max_allowed_packet up to 1G since version 4.0, this should not be a problem any more. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Large inserts, chunking and Concat
Hello, realizing that there is a max_allowed_packet setting that limits the size of the insert statement, is there a way around it by chunking the query? In particular, if the query is to insert a large text or blob, can I simply concatenate smaller pieces of the data in succession using the Concat command? My concern is whether this will still in someway cause me other MySQL resource problems? I want to be able to insert a large text or blob of over 200-400MBs. Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem setting default value for column with concat()
Hi all,
When I run the following code the default value isn't being calculated using the concat
and other functions. Instead it is setting the column definition as a string:
CONCAT(TMP
How can I rework this to get a result more like
TMP-T-2
?
USE cro;
CREATE TABLE lpamform (
crofileno VARCHAR(11) NOT NULL DEFAULT 'CONCAT(TMP-T-,ROUND(RAND(NOW())*10))',
docstatus ENUM('C','A') NOT NULL DEFAULT 'C',
PRIMARY KEY (crofileno)
) TYPE=MyISAM;
Thanks,
Tim
Re: Problem setting default value for column with concat()
Tim Russell wrote:
Hi all,
When I run the following code the default value isn't being calculated using the concat
and other functions. Instead it is setting the column definition as a string:
CONCAT(TMP
How can I rework this to get a result more like
TMP-T-2
?
You can't.
Default values can only be constants.
( except for autoincrement and timestamp )
USE cro;
CREATE TABLE lpamform (
crofileno VARCHAR(11) NOT NULL DEFAULT 'CONCAT(TMP-T-,ROUND(RAND(NOW())*10))',
docstatus ENUM('C','A') NOT NULL DEFAULT 'C',
PRIMARY KEY (crofileno)
) TYPE=MyISAM;
Thanks,
Tim
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concat. operator ||
Hi Paul, Using the following fixed things. However, I am surprised at why it is not working in 4.0.18 (Win32)? Previously I had 4.0.17 (Win32) and things work perfectly. I guess you are right, it's a bug. [mysqld] sql-mode=REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY transaction-isolation=SERIALIZABLE Thanks. Hassan Paul DuBois wrote: At 12:50 -0600 2/23/04, Paul DuBois wrote: At 15:59 +0200 2/23/04, Victoria Reznichenko wrote: Hassan Shaikh [EMAIL PROTECTED] wrote: I am running MySQL in ANSI standard mode. However the following statement is giving unexpected result. Am I missing something? mysql select 'This is a' || ' test string'; +---+ | 'This is a' || ' test string' | +---+ | 0 | +---+ 1 row in set (0.00 sec) Here's a part of my my.ini, indicating that I am in ANSI mode. # The MySQL server [mysqld] port=3306 #socket=MySQL skip-locking set-variable= sql-mode=ansi This should work, but when I try it, it doesn't work. (The sql_mode variable is set to 4, which is the numeric equivalent of the ANSI_QUOTES option. I believe what's happening is that there is an attempt to match prefixes of option names that is done incorrectly.) I'll file a bug report. In the meantime, you can use this instead: [mysqld] ansi Oops, sql-mod=ansi not working isn't a bug after all, at least not if you're using MySQL 4.x. The ansi shortcut was added in 4.1.1: http://www.mysql.com/doc/en/Server_SQL_mode.html That should produce the desired effect. set-variable= key_buffer=32M set-variable= max_allowed_packet=1M set-variable= table_cache=32 set-variable= net_buffer_length=8K set-variable= sort_buffer=1M set-variable= record_buffer=512K set-variable= myisam_sort_buffer_size=4M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=8 log-bin server-id= 1 What version of MySQL do you use? If you use version 4.1 , you should put in the my.cnf [mysqld] ansi ... or [musqld] sql-mode=REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY transaction-isolation=SERIALIZABLE .. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Concat. operator ||
Hi All, I am running MySQL in ANSI standard mode. However the following statement is giving unexpected result. Am I missing something? mysql select 'This is a' || ' test string'; +---+ | 'This is a' || ' test string' | +---+ | 0 | +---+ 1 row in set (0.00 sec) Here's a part of my my.ini, indicating that I am in ANSI mode. # The MySQL server [mysqld] port=3306 #socket=MySQL skip-locking set-variable= sql-mode=ansi set-variable= key_buffer=32M set-variable= max_allowed_packet=1M set-variable= table_cache=32 set-variable= net_buffer_length=8K set-variable= sort_buffer=1M set-variable= record_buffer=512K set-variable= myisam_sort_buffer_size=4M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=8 log-bin server-id= 1 Thanks. Hassan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concat. operator ||
I get the same thing on our system (MySQL 4.0.15 running on Linux Mandrake
9.1).
According to the manual, section 1.8.4:
MySQL Server understands the || and operators to mean logical OR and
AND, as in the C programming language. In MySQL Server, || and OR are
synonyms, as are and AND. Because of this nice syntax, MySQL Server
doesn't support the standard SQL-99 || operator for string concatenation;
use CONCAT() instead. Because CONCAT() takes any number of arguments, it's
easy to convert use of the || operator to MySQL Server.
If you rewrite your query as follows, you should get the result you wanted:
select concat('This is a ', 'test string');
Rhino
- Original Message -
From: Hassan Shaikh [EMAIL PROTECTED]
To: MySQL (General List) [EMAIL PROTECTED]
Sent: Monday, February 23, 2004 7:58 AM
Subject: Concat. operator ||
Hi All,
I am running MySQL in ANSI standard mode. However the following
statement is giving unexpected result. Am I missing something?
mysql select 'This is a' || ' test string';
+---+
| 'This is a' || ' test string' |
+---+
| 0 |
+---+
1 row in set (0.00 sec)
Here's a part of my my.ini, indicating that I am in ANSI mode.
# The MySQL server
[mysqld]
port=3306
#socket=MySQL
skip-locking
set-variable= sql-mode=ansi
set-variable= key_buffer=32M
set-variable= max_allowed_packet=1M
set-variable= table_cache=32
set-variable= net_buffer_length=8K
set-variable= sort_buffer=1M
set-variable= record_buffer=512K
set-variable= myisam_sort_buffer_size=4M
set-variable= thread_cache=8
# Try number of CPU's*2 for thread_concurrency
set-variable= thread_concurrency=8
log-bin
server-id= 1
Thanks.
Hassan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concat. operator ||
Hassan Shaikh [EMAIL PROTECTED] wrote: I am running MySQL in ANSI standard mode. However the following statement is giving unexpected result. Am I missing something? mysql select 'This is a' || ' test string'; +---+ | 'This is a' || ' test string' | +---+ | 0 | +---+ 1 row in set (0.00 sec) Here's a part of my my.ini, indicating that I am in ANSI mode. # The MySQL server [mysqld] port=3306 #socket=MySQL skip-locking set-variable= sql-mode=ansi set-variable= key_buffer=32M set-variable= max_allowed_packet=1M set-variable= table_cache=32 set-variable= net_buffer_length=8K set-variable= sort_buffer=1M set-variable= record_buffer=512K set-variable= myisam_sort_buffer_size=4M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=8 log-bin server-id= 1 What version of MySQL do you use? If you use version 4.1 , you should put in the my.cnf [mysqld] ansi ... or [musqld] sql-mode=REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY transaction-isolation=SERIALIZABLE .. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concat. operator ||
At 15:59 +0200 2/23/04, Victoria Reznichenko wrote: Hassan Shaikh [EMAIL PROTECTED] wrote: I am running MySQL in ANSI standard mode. However the following statement is giving unexpected result. Am I missing something? mysql select 'This is a' || ' test string'; +---+ | 'This is a' || ' test string' | +---+ | 0 | +---+ 1 row in set (0.00 sec) Here's a part of my my.ini, indicating that I am in ANSI mode. # The MySQL server [mysqld] port=3306 #socket=MySQL skip-locking set-variable= sql-mode=ansi This should work, but when I try it, it doesn't work. (The sql_mode variable is set to 4, which is the numeric equivalent of the ANSI_QUOTES option. I believe what's happening is that there is an attempt to match prefixes of option names that is done incorrectly.) I'll file a bug report. In the meantime, you can use this instead: [mysqld] ansi That should produce the desired effect. set-variable= key_buffer=32M set-variable= max_allowed_packet=1M set-variable= table_cache=32 set-variable= net_buffer_length=8K set-variable= sort_buffer=1M set-variable= record_buffer=512K set-variable= myisam_sort_buffer_size=4M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=8 log-bin server-id= 1 What version of MySQL do you use? If you use version 4.1 , you should put in the my.cnf [mysqld] ansi ... or [musqld] sql-mode=REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY transaction-isolation=SERIALIZABLE .. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concat. operator ||
At 12:50 -0600 2/23/04, Paul DuBois wrote: At 15:59 +0200 2/23/04, Victoria Reznichenko wrote: Hassan Shaikh [EMAIL PROTECTED] wrote: I am running MySQL in ANSI standard mode. However the following statement is giving unexpected result. Am I missing something? mysql select 'This is a' || ' test string'; +---+ | 'This is a' || ' test string' | +---+ | 0 | +---+ 1 row in set (0.00 sec) Here's a part of my my.ini, indicating that I am in ANSI mode. # The MySQL server [mysqld] port=3306 #socket=MySQL skip-locking set-variable= sql-mode=ansi This should work, but when I try it, it doesn't work. (The sql_mode variable is set to 4, which is the numeric equivalent of the ANSI_QUOTES option. I believe what's happening is that there is an attempt to match prefixes of option names that is done incorrectly.) I'll file a bug report. In the meantime, you can use this instead: [mysqld] ansi Oops, sql-mod=ansi not working isn't a bug after all, at least not if you're using MySQL 4.x. The ansi shortcut was added in 4.1.1: http://www.mysql.com/doc/en/Server_SQL_mode.html That should produce the desired effect. set-variable= key_buffer=32M set-variable= max_allowed_packet=1M set-variable= table_cache=32 set-variable= net_buffer_length=8K set-variable= sort_buffer=1M set-variable= record_buffer=512K set-variable= myisam_sort_buffer_size=4M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=8 log-bin server-id= 1 What version of MySQL do you use? If you use version 4.1 , you should put in the my.cnf [mysqld] ansi ... or [musqld] sql-mode=REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY transaction-isolation=SERIALIZABLE .. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
