MAKE_SET() Function SQL

The MAKE_SET() is not an oft used function. It basically takes a series of strings and looks for the position of the string in the list, if it matches the pattern established by the first parameter, it is placed in a new string as a comma delimited list. Syntax:

MAKE_SET( bit_pattern, string1, string2, etc. )

The bit_pattern is a number. That number will call the strings located in the specific parameter positions as if those strings corresponded to a binary number. The following limited list (through number 8 bit_pattern) will give an idea of the pattern established. The binary number is in parenthesis.

1 (1) = string1
2 (10) = string2
3 (11) = string1, string2
4 (100) = string3
5 (101) = string1, string3
6 (110) = string2, string3
7 (111) = string1, string2, string3
8 (1000) = string4

Thus, it is possible to select any combination of strings by the designation of a numeral (converted by the function into a binary number). However, it is not possible to rearrange their order.

Multiple patterns can be called upon by using a pipe. Like this:

SELECT MAKE_SET( 8|1,'a','b','c','d')

This would ensure that the last and first strings were always selected. In spite of the fact that we have put the 1 on the back side of the pipe, the strings will still be included in the order in which they are listed.

< LEN function | MATCH AGAINST Function >

Interesting Pages