From: Robin Bowes
Date: April 30 1999 12:18pm
Subject: Function enhancement request
List-Archive: http://lists.mysql.com/mysql/2689
Message-Id: <37299F83.5F824829@eoc.org.uk>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Hi,

I'd really like to see a variant on the concat() function which could be
used to "intelligently" join together fields with a given seperator.

For example, consider a table with the following fields:

+----------------+---------------------+------+-----+---------+-------+
| Field          | Type                | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+-------+
| Addr_Line_1    | varchar(40)         |      |     |         |       |
| Addr_Line_2    | varchar(40)         |      |     |         |       |
| Addr_Line_3    | varchar(40)         |      |     |         |       |
| Addr_Town      | varchar(30)         |      |     |         |       |
| Addr_County    | varchar(30)         |      |     |         |       |
| Addr_Postcode  | varchar(8)          |      |     |         |       |
+----------------+---------------------+------+-----+---------+-------+

I'd like to be able to do something like:

select
	concatX(
		',',
		Addr_Line1,
		Addr_Line2,
		Addr_Line3,
		Addr_Town,
		Addr_County,
		Addr_Postcode
	 ) as Address
from
	Table;

And have output that looked like this...

+------------------------------------------------------------------+
| Address                                                          |
+------------------------------------------------------------------+
|                                                                  |
|                                                                  |
|                                                                  |
| 46 Stamford Road,Mossley,ASHTON-UNDER-LYNE,Lancashire,OL5 0BE    |
| 58 Copster Hill Road,OLDHAM,Lancashire,OL8 1QD                   |
| 4 Stewart Avenue,UPMINSTER,Essex,RM14 2AF                        |
| MP for Clydebank & Milngavie,(Constituency)                      |
| 30 Whin Street,CLYDEBANK,Dunbartonshire,G81 3JE                  |
| 499 Kilbowie Road,CLYDEBANK,Dunbartonshire,G81 2AX               |
| 30-32 Dougrie Drive,GLASGOW,Lanarkshire,G45 9AG                  |
+------------------------------------------------------------------+

...rather than this...

+------------------------------------------------------------------+
| Address                                                          |
+------------------------------------------------------------------+
| ,,,,,                                                            |
| ,,,,,                                                            |
| ,,,,,                                                            |
| 46 Stamford Road,Mossley,,ASHTON-UNDER-LYNE,Lancashire,OL5 0BE   |
| 58 Copster Hill Road,,,OLDHAM,Lancashire,OL8 1QD                 |
| 4 Stewart Avenue,,,UPMINSTER,Essex,RM14 2AF                      |
|  MP for Clydebank & Milngavie,(Constituency),,,,                 |
| 30 Whin Street,,,CLYDEBANK,Dunbartonshire,G81 3JE                |
| 499 Kilbowie Road,,,CLYDEBANK,Dunbartonshire,G81 2AX             |
| 30-32 Dougrie Drive,,,GLASGOW,Lanarkshire,G45 9AG                |
+------------------------------------------------------------------+

Notice how the extraneous separator characters ( commas ) are removed in
the first example.

As far as I can tell, this shouldn't be too hard to code - it simply
requires a check that neither of the arguments being concatenated are
empty before inserting the seperator character.

I had a look at the source code to see if I could hack something
together based on the concat function but I'm afraid I'm don't really do
C.

Would this be a possible enhancement in the future?

R.
-- 
Robin Bowes - System Development Manager - Room 405A
E.O.C., Overseas House, Quay St., Manchester, M3 3HN, UK.
Tel: +44 161 838 8321  Fax: +44 161 835 1657