loading..
   English
19:05

STRING_SPLIT function

The STRING_SPLIT function performs the inverse operation of STRING_AGG. It takes a character string and breaks it up into substrings using the separator symbol passed to it as the second argument. These substrings form a one-column table. The single column of this table can be referred to as value.

  1. SELECT * FROM STRING_SPLIT('0 1 2 3 4 5 6 7 8 9',' ');

value
0
1
2
3
4
5
6
7
8
9

In fact, the STRING_SPLIT function acts as a non-standard single-column table value constructor. The standard alternative looks as follows:

Console
Execute
  1. SELECT * FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) X(value);

A more interesting case is passing the value of a column of a table to the function.

For instance, lets assume we have to break up the multi-word names in the Ships table into single words. The following query could be used for that:

  1. SELECT name, value FROM Ships CROSS APPLY STRING_SPLIT(name,' ')
  2. WHERE name LIKE '% %';

name value
New Jersey New
New Jersey Jersey
North Carolina North
North Carolina Carolina
Royal Oak Royal
Royal Oak Oak
Royal Sovereign Royal
Royal Sovereign Sovereign
South Dakota South
South Dakota Dakota



Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
yesterday
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100