sa_split_list system procedure

Takes a string of values, separated by a delimiter, and returns a set of rows—one row for each value.

Syntax

sa_split_list( 
str
 [, delim ]
 [, maxlen ]
)

Arguments

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.

Result set

Column name	Data type	Description
line_num	INTEGER	Sequential number for the row.
row_value	LONG VARCHAR	Value from the string, truncated to maxlen if required.

Remarks

The sa_split_list procedure accepts a string with a delimited list of values, and returns a result set with one value per row. This is the opposite of the action performed by the LIST function [Aggregate]. An empty string is returned for row_value if the string:

begins with delim
contains two successive instances of delim in the middle of the string
ends with delim

White space within the input string is significant. If the delimiter is a space character, extra spaces in the input string result in extra rows in the result set. If the delimiter is not a space character, spaces in the input string are not trimmed from the values in the result set.

Permissions

None

Side effects

None

Examples

The following query returns a list of black colored products.

SELECT list( Name )
  FROM Products 
  WHERE Color = 'Black';

list (Products.Name)
Tee Shirt,Baseball Cap,Visor,Shorts

In the following example, the sa_split_list procedure is used to return the original result set from the aggregated list.

SELECT * 
  FROM sa_split_list( 'Tee Shirt,Baseball Cap,Visor,Shorts' );

line_num	row_value
1	Tee Shirt
2	Baseball Cap
3	Visor
4	Shorts

The following example returns a row for each word. To avoid returning rows where row_value is an empty string, the WHERE clause must be specified.

SELECT *
  FROM sa_split_list( 'one||three|four||six|', '|' ) 
  WHERE row_value <> '';

line_num	row_value
1	one
3	three
4	four
6	six

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 white or black.

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

SELECT * from ProductsWithColor( 'white,black' );

Discuss this page in DocCommentXchange.
Send feedback about this page using email.