SQL - STRING_SPLIT() Function

The SQL STRING_SPLIT() function is used to convert string into column.

It accepts three parameters str, separator, and enable_ordinal, and splits a string into rows of substrings, based on a specified separator character. If the ordinal output column is not enabled, STRING_SPLIT() function returns a single-column table whose rows are the substrings.

The name of the column will be VALUE. It returns nvarchar if any of the input arguments are either nvarchar or nchar. Otherwise, it returns varchar. The length of the return type is the same as the length of the string argument.

Syntax

Following is the syntax of the SQL STRING_SPLIT() function −

STRING_SPLIT ( string , separator [ , enable_ordinal ] )

Parameters

  • str − It is an expression of any character type (like nvarchar, varchar, nchar).

  • separator − It is a single character expression of any character type that is used as as separator for concatenated substrings.

  • enable_ordinal − It is an int or bit expression that serves as a flag to enable or disable the ordinal output column.

Following are some important points about the enable_ordinal parameter:

  • It is an optional parameter.

  • If the enable_ordinal parameter value is 1, the ordinal column is enabled.

  • If the enable_ordinal value is NULL, the ordinal column is omitted.

  • If the enable_ordinal value is 0, ordinal_column is disabled.

Example

In the following example,we are using the SQL STRING_SPLIT() function to convert string content (‘Java’, ‘HTML’, ‘CSS’, ‘JavaScript’,’ Angular’) into the column.

SELECT VALUE FROM STRING_SPLIT('Java IS A PROGRAMMING LANGUAGE',' ');

Output

Following is the output of the above statement −

+--------------+
| VALUE        |  
+--------------+
| Java         |
| IS           |
| A            |
| PROGRAMMING  |
| LANGUAGE     | 
+--------------+

Example

If we pass the enable_ordinal argument value as 1 to the function, it enables the output column.

In the following example, we are using the STRING_SPLIT() function to convert the contents of the string (‘Welcome to TutorialsPoint Family’) into a column.

SELECT * FROM STRING_SPLIT('Welcome to TutorialsPoint Family', ' ', 1);

Output

On executing the above program, it will produce the following output −

+-----------------+---------+
| VALUE           | ordinal |
+-----------------+---------+
| Welcome         |   1     |
| to              |   2     |
| TutorialsPoint  |   3     |
| Family          |   4     | 
+-----------------+---------+

Example

If you pass the enable_ordinal argument value as 0 to the function, it disables the output column.

In the following example, we are passing the enable_ordinal argument value as 0 to the STRING_SPLIT() function. Then, using this function, we are trying to convert the contents of the string (‘Hello World’) into a column.

SELECT * FROM STRING_SPLIT('Hello World',' ', 0);

Output

The above program generates the following output −

+----------+
| VALUE    |  
+----------+
| Hello    |
| World    |
+----------+

Example

If we pass the enable_ordinal argument value as NULL to function, this function omitted the output column

In the following example,we are passing the enable_ordinal argument value as NULL to the STRING_SPLIT() function. Then, using this function we are trying to convert the string content ('Java IS A PROGRAMMING LANGUAGE',' ', NULL) into the column.

SELECT VALUE FROM STRING_SPLIT('Java IS A PROGRAMMING LANGUAGE',' ', NULL);

Output

Following is the output of the above SQL query −

+--------------+
| VALUE        |  
+--------------+
| Java         |
| IS           |
| A            |
| PROGRAMMING  |
| LANGUAGE     | 
+--------------+

Example

You can also pass a table column as an argument to the STRING_SPLIT() function to find the rows by list of values. Assume we have created a table with the name Customers using the CREATE statement as follows −

CREATE TABLE CUSTOMERS(    
ID INT NOT NULL,    
FIRST_NAME VARCHAR (20),
LAST_NAME VARCHAR(20),
AGE INT NOT NULL,    
ADDRESS CHAR (25) ,    
SALARY DECIMAL (18, 2));

Now let's insert four records into the customers table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES (2, 'Ramesh','KUMAR', 32, 'Ahmedabad', 2000.00 ); 
INSERT INTO CUSTOMERS VALUES (3, 'Khilan','Verma', 25, 'Delhi', 1500.00 ); 
INSERT INTO CUSTOMERS VALUES (3, 'kaushik','Gupta', 23, 'Kota', 2000.00 ); 
INSERT INTO CUSTOMERS VALUES (5, 'Chaitali','Pal', 25, 'Mumbai', 6500.00 );

The Following SQL query retrieve the rows by list of values of the Customers table −

SELECT * FROM CUSTOMERS JOIN STRING_SPLIT('3',' ', 1) on value = ID;

Output

Following is the output of the above query −

+----+------------+-----------+-----+-----------+---------+-------+----------+
| ID | FIRST_NAME | LAST_NAME | AGE | ADDRESS   | SALARY  | value | ordinal  |
+----+------------+-----------+-----+-----------+---------+-------+----------+
|  3 | Khilan     | Verma     |  25 | Delhi     | 1500.00 |  3    |   1      |
|  3 | kaushik    | Gupta     |  23 | Kota      | 2000.00 |  3    |   1      |
+----+------------+-----------+-----+-----------+---------+-------+----------+
sql-string-functions.htm
Advertisements