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
Thread |
---|
• Function enhancement request | Robin Bowes | 30 Apr |