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
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',
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,
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,
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
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',
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
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, ''), ' ',
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
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
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
-
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
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
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
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
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
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
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
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
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))) |
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
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
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
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:
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:
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...
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
[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
[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
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
[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:
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,
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
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
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
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...
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...
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,
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
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
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
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
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
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]
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
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' |
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';
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' || '
At 17:20 -0600 2/23/04, 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
This is the article for you:
http://php.dreamwerx.net/forums/viewtopic.php?t=6
Shows how to store large files in database... I've currently got gigs and
gigs of files in mysql using this method..
On Tue, 2 Dec 2003, Jim Kutter wrote:
Hi folks.
I'm storing files in a BLOB table for a number
Ooks Server [EMAIL PROTECTED] wrote:
I've run into a problem with the behavior of concat(). If I have two fields,
char(10), and I do this:
concat(field1,fields)
With MSSQL I get both fields including trailing spaces. With MYSql, I get
the two fields with the trailing spaces trimmed.
]
Subject: Re: concat() differences between mssql and mysql
Ooks Server [EMAIL PROTECTED] wrote:
I've run into a problem with the behavior of concat(). If I
have two fields,
char(10), and I do this:
concat(field1,fields)
With MSSQL I get both fields including trailing spaces
Hmmm. Well you might be able to work some magic if you know the length of
the field it's supposed to be, then you could subtract the length of the
Field and then pad with spaces.
If this is to be displayed in a web page, and I assume you're trying to line
things up pretty, just put them in table
At 13:11 -0700 7/8/03, Daevid Vincent wrote:
Hmmm. Well you might be able to work some magic if you know the length of
the field it's supposed to be, then you could subtract the length of the
Field and then pad with spaces.
If this is to be displayed in a web page, and I assume you're trying to
Ashwin,
Wednesday, July 24, 2002, 3:15:25 PM, you wrote:
AK I have a table with a field full of urls.. what I need to do is to append
AK to those urls with a new url, so for example, if the field had
AK http://www.yahoo.com i want to make it
AK
Jeff Holzfaster writes:
Hi!
This query works: select date_format(date, "%W, %e %M %Y") as date from
table
This query doesn't: select concat(date_format(date, "%W, %e %M %Y"),"
",another) as time_of_day
I'm wondering if it is possible to use concat in this way and how if it is
On Mon, Apr 16, 2001 at 07:54:56AM -0500, Jeff Holzfaster wrote:
Hi!
This query works: select date_format(date, "%W, %e %M %Y") as date from
table
This query doesn't: select concat(date_format(date, "%W, %e %M %Y"),"
",another) as time_of_day
I'm wondering if it is possible to use
57 matches
Mail list logo