|
Takes a string of values, separated by a delimiter, and returns a set of rows—one row for each value.
sa_split_list(
str
[, delim
[, maxlen ] ]
)
str Use this LONG VARCHAR parameter to specify the string containing the values to be split, separated by delim.
delim Use this optional CHAR(10) parameter to specify the delimiter used in str to separate values. The delimiter can be a string of any characters, up to 10 bytes. If delim is not specified, a comma is used by default.
maxlen Use this optional INTEGER parameter to specify the maximum length of the returned values. For example, if maxlen is set to 3, the values in the result set are truncated to a length of 3 characters. If you specify 0 (the default), values can be any length.
| Column name | Data type | Description |
|---|---|---|
| line_num | INTEGER | Sequential number for the row. |
| row_value | CHAR | Value from the string, truncated to maxlen if required. |
The sa_split_list procedure can be used within other procedures to restrict a query to the result set of sa_split_list.
None
None
The following call formats the string 'yellow##blue##red' so that individual items appear on separate lines in the result set.
CALL sa_split_list( 'yellow##blue##red', '##', 3 );
| line_num | row_value |
|---|---|
| 1 | yel |
| 2 | blu |
| 3 | red |
In the following example, a procedure called ProductsWithColor is created. When called, the ProductsWithColor procedure uses sa_split_list to parse the color values specified by the user, looks in the Color column of the Products table, and returns the name, description, size, and color for each product that matches one of the user-specified colors.
The result of the procedure call below is the name, description, size, and color of all products that are either blue or white.
CREATE PROCEDURE ProductsWithColor( IN color_list LONG VARCHAR ) BEGIN SELECT Name,Description,Size,Color FROM Products WHERE Color IN ( SELECT row_value FROM sa_split_list( color_list ) ) END GO CALL ProductsWithColor( 'white, blue' )