Re: Alphabetizing a Comma Delimited String
From: Dr. StrangeLove (steve.nospam._at_rac4sql.net)
Date: 02/28/04
- Next message: Dan: "Re: If I am adding a record to a table using a SP, how can I make the SP return"
- Previous message: Andrew J. Kelly: "Re: Triggering After an update"
- In reply to: Aaron Bertrand [MVP]: "Re: Alphabetizing a Comma Delimited String"
- Next in thread: John Bell: "Re: Alphabetizing a Comma Delimited String"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 28 Feb 2004 10:36:30 -0500
"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
news:ujuNpQW$DHA.3352@TK2MSFTNGP09.phx.gbl...
> Simulate an array, stuff it into a table, and then use order by. Use the
> client to put it back into a comma-delimited string.
>
> (See http://www.aspfaq.com/2248 for ideas and links about using
split()-like
> functionality to break up a string and store it in table form.)
>
> Better yet, store each item in a table rather than having this grotesque
> non-relational string in the first place. You will probably get great
> efficiencies in storage, since you could use a code or TINYINT
> representation.
One *wonders* why this functionality is not in the server engine.
Now here's something *wonder-full*.It's from MySql v4.1:
----------------------------------------------------------------------------
----------------------
GROUP_CONCAT(expr)
Full syntax:
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC]
[,col ...]]
[SEPARATOR str_val])
This function was added in MySQL version 4.1. It returns a string result
with the concatenated values from a group:
mysql> SELECT student_name,
-> GROUP_CONCAT(test_score)
-> FROM student
-> GROUP BY student_name;
or:
mysql> SELECT student_name,
-> GROUP_CONCAT(DISTINCT test_score
-> ORDER BY test_score DESC SEPARATOR " ")
-> FROM student
-> GROUP BY student_name;
In MySQL you can get the concatenated values of expression combinations. You
can eliminate duplicate values by using DISTINCT. If you want to sort values
in the result you should use ORDER BY clause. To sort in reverse order, add
the DESC (descending) keyword to the name of the column you are sorting by
in the ORDER BY clause. The default is ascending order; this may be
specified explicitly using the ASC keyword. SEPARATOR is the string value
which should be inserted between values of result. The default is a comma
(`","'). You can remove the separator altogether by specifying SEPARATOR "".
You can set a maximum allowed length with the variable group_concat_max_len
in your configuration. The syntax to do this at runtime is:
SET [SESSION | GLOBAL] group_concat_max_len = unsigned_integer;
If a maximum length has been set, the result is truncated to this maximum
length. The GROUP_CONCAT() function is an enhanced implementation of the
basic LIST() function supported by Sybase SQL Anywhere. GROUP_CONCAT() is
backward compatible with the extremely limited functionality of LIST(), if
only one column and no other options are specified. LIST() does have a
default sorting order.
----------------------------------------------------------------------------
---------------------------------
So a bunch of open source nerds can do this but MS can't ?? :)
Interestingly, the *same* functionality with the same ease of use
has been in RAC using @concatenate or list() for quite a while!
(http://www.rac4sql.net/onlinehelp.asp?topic=236)
There's an interesting message in all this but I'll leave that to the
reader:)
RAC v2.2 and QALite @
www.rac4sql.net
- Next message: Dan: "Re: If I am adding a record to a table using a SP, how can I make the SP return"
- Previous message: Andrew J. Kelly: "Re: Triggering After an update"
- In reply to: Aaron Bertrand [MVP]: "Re: Alphabetizing a Comma Delimited String"
- Next in thread: John Bell: "Re: Alphabetizing a Comma Delimited String"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
