loading..
Ðóññêèé    English
19:58

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, let’s 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 DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.