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

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.

None

None

• LIST function [Aggregate]

The following query returns a list of black colored products.

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

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' );

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 <> '';

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 OR REPLACE 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;

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