SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Data Virtualization Tutorials and Guides
Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.




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.

Data Virtuality database address table

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

SQL string split on Data Virtuality

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.

split string using SQL TextTable function on Data Virtuality

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

Data Virtuality SQL TextTable function sample query

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

SQL split string on Data Virtuality using TextTable function

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.





DV Tutorials

Data Virtualization










Copyright © 2004 - 2020 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems