Split String using SQL TextTable Function on Data Virtuality
In this Data Virtuality tutorial for SQL developers I want to show how programmers can split string into pieces using a delimiter. String splitting in SQL is possible in different data platform by SQL functions shipped with those databases. If it is not possible to split string on its source, data virtualization platform developers can also split character type field values easily using SQL as shown in this tutorial.
Let's assume I have a table named Address and the table includes a column named regioncity which includes a concatenated form of region and city information in this single database table column.
select * from kodyaz.Address;;
As seen below the database table column includes both region and city information. Let's split string value using delimiter character "/" to get the region and city in separate fields.
An old approach for SQL developers is using SQL string functions like Locate(), Left() and Right() functions as shown in following SQL Select statement.
select
addressid,
detail,
regioncity,
CASE WHEN (LOCATE('/', regioncity) > 0)
THEN
LEFT(regioncity, LOCATE('/', regioncity)-1)
ELSE
regioncity
END as region,
CASE WHEN (LOCATE('/', regioncity) > 0)
THEN
RIGHT(regioncity, LENGTH(regioncity)-LOCATE('/', regioncity))
ELSE
NULL
END as city
from kodyaz.Address;;
As database developers can see this SQL approach still works quite good
Data Virtuality provides an additional table-valued function for SQL developers.
That is TEXTTABLE
Using TextTable() table-valued SQL function, SQL developers building their business views or data model on top of data resources, can split column values of string data type using a delimiter character.
Here is an example
select *
FROM TEXTTABLE ('one,two,three,four'
COLUMNS
column1 varchar(10),
column2 varchar(10),
column3 varchar(10)
delimiter ','
) as T;;
As seen in the execution result, the input text value is splitted by using the delimiter character as the separator and maps into the columns defined in the COLUMNS list of the TEXTTABLE function.
If there are less number of columns defined in the TEXTTABLE COLUMNS list, remaining data is not visible like "four" in our case.
On the other hand, if the case is just the opposite. I mean if there are more columns stated in the TextTable() function, but it is not possible to split the string into that much parts, then the remaining columns return NULL values like column5 in following sample
select *
FROM TEXTTABLE ('one,two,three,four'
COLUMNS
column1 varchar(10),
column2 varchar(10),
column3 varchar(10),
column4 varchar(10),
column5 varchar(10)
delimiter ','
) as T;;
SQL developers can now see that column5 value is NULL as TextTable() function output for given input string after split
If we return back to our original problem which is splitting database column string values separated by a delimiter character in Address table, we can build a new SQL query as follows using the TextTable function and removing SQL string functions Locate, Left, Right and CASE control statements.
select
addressid, detail, regioncity, region, city
from kodyaz.Address as a
cross join TEXTTABLE (a.regioncity
COLUMNS
region varchar(10),
city varchar(10)
delimiter '/'
) as T;;
SQL programmers building their applications on top of Data Virtuality data virtualization layer can split string stored in table columns as seen below in the execution output of above SQL Select statement
I hope this Data Virtuality SQL tutorial provides useful information with SQL samples showing how TextTable table-valued function can be used to split string stored in database columns easily for database developers.