Java How to Split Text to Update Database and Display It Again

Database developers often need to catechumen a comma-separated value or other delimited items into a tabular format. Delimiters include pipe "|", hash "#", dollar "$" and other characters. SQL Server compatibility level 130, and subsequent versions, support a string_split() part to convert delimiter-separated values to rows (table format). For compatibility levels under 130, developers accept previously done this with a user-defined function, which incorporates a While loop or Cursor to extract the data.

Why is comma-separated input required for a process or T-SQL query?

Execute a program in an iteration using a loop is a common method in the back-terminate awarding as well as the database. When iteration count is smaller than expected, the execution time tin be less still the number of iterations is bigger which leads to longer processing time. For example, if the application is fetching data from a database with executing a piece of program in a loop by parsing dissimilar input parameter application will need to wrap the database response of each iteration. To handle this blazon of situation, we tin can execute the same programme with a unmarried execution with parsing the comma or delimiter separated values as an input parameter in the database and make it to tabular format in the T-SQL program to proceed with further logic.

Today, many platforms have been changed to micro-service architecture. Not only application but database design could besides be structured in micro-service-based architecture and nearly of the internal advice between the databases is done with integer references only. That is the reason to get some catchy way to achieve meliorate execution from the database side.

Multiple inputs to the same parameter can be accomplished with comma-separated values in the input parameter of the stored procedure or input to the tabular function, and used with the table in a T-SQL argument. At that place may be more situations for this strategy when using T-SQL. While programming with SQL Server, a developer must break a string into elements using a separator. Using the split part against the string defines the separator equally an input parameter. The entire cord will be split and returned as a table. In certain circumstances, the input parameter tin can be a combination of two or more character-separated values for each set of input. For instance:

N'203616, 198667, 193718, 188769,…' N'1021|203616$1021|198667$1022|193718$1022|188769$…' North'1021|203616,198667$1022|193718,188769$…'

There are advantages to single-thread execution with comma-separated values over multi-thread execution in SQL Server:

• Single execution for function of the program, resulting in the number of input
• Single read or write functioning over the database tables instead of several iterations using a loop
• One-time server asset utilize
• No more hurdles on the dorsum-finish application to ready a response from multiple effect sets
• I-shot commit means that the database/SQL Server won't block the transaction and deadening things down. Thus, no blocking possibilities
• Easy to oversee transaction consistency

Split up office using loop

In nigh cases, a developer writes the table-valued function with a loop to extract the information from comma-separated values to a list or table format. A loop extracts the tuple ane by i with the recursion. Functioning problems tin can ascend when working with the large input parameter since information technology will take more time to extract it. Hither, we have a simple function with the name of split_string, which is using a loop inside the tabular array-valued office. This function allows two arguments or input parameters (ane – input cord and 2 – delimiter):

1 2 3 iv v six vii viii 9 10 11 12 13 14 xv sixteen 17

CREATE Role split_string ( @ in_string VARCHAR ( MAX ) , @ delimeter VARCHAR ( i ) ) RETURNS @ list Table ( tuple VARCHAR ( 100 ) ) AS BEGIN WHILE LEN ( @ in_string ) > 0 BEGIN INSERT INTO @ list ( tuple ) SELECT left ( @ in_string , charindex ( @ delimiter , @ in_string + ',' ) - ane ) every bit tuple Ready @ in_string = stuff ( @ in_string , one , charindex ( @ delimiter , @ in_string + @ delimiter ) , '' ) end Render Cease

A upshot-set up table returned by this part contains every substring of the input parameter string which is separated by the delimiter. The substrings in the tabular array are in the social club in which they happen in the input parameter. If the separator or delimiter doesn't coordinate with whatsoever piece of the input string, then the output table will take but 1 component. The tabular function will return the upshot set in a row-column format from the comma-separated string.

SELECT * FROM split_string ( '1001,1002,1003,1004' , ',' )

Recent versions of SQL Server provide a built-in part string_split() to exercise the same task with the input parameters of the input string and delimiter. Merely at that place is another efficient fashion to perform this task using XML.

Divide function using XML

The Split function using XML logic is a useful method to divide values from a delimited cord. Using XML logic with the T-SQL based XQUERY approach results in the T-SQL not existence characterized as a recursion using loop or cursor. Essentially, the input parameter string is converted to XML by replacing the XML node in the T-SQL articulation. The resulting XML is used in XQUERY to extract the node value into the tabular array format. It also results in better performance.

For example, nosotros have a comma-separated value with the variable @user_ids in the below T-SQL statement. In a variable, a comma is replaced by the XML tags to make information technology an XML information type document and so extracted using XQUERY with the XPATH as '/root/U':

DECLARE @ user_ids NVARCHAR ( MAX ) = N '203616, 198667, 193718, 188769, 183820, 178871, 173922, 168973, 164024, 159075, 154126, 149177, 144228, 139279, 134330, 129381, 124432, 119483, 114534, 109585, 104636, 99687, 94738, 89789, 84840, 79891, 74942, 69993, 65044, 60095, 55146' DECLARE @ sql_xml XML = Cast ( '<root><U>' + Replace ( @ user_ids , ',' , '</U><U>' ) + '</U></root>' AS XML ) SELECT f . x . value ( '.' , 'BIGINT' ) Equally user_id INTO #users FROM @ sql_xml . nodes ( '/root/U' ) f ( x ) SELECT * FROM #users

In the in a higher place part, we have used the VARCHAR (100) data type for the extracted value rather than BIGINT. There can be changes in the input parameter cord, also. For example, if the input string has one extra ',' at the start or end of the string, it returns one extra row with 0 for the BIGINT data type and " for VARCHAR.

For case,

DECLARE @ user_ids VARCHAR ( MAX ) = N '203616, 198667, 193718, 188769, 183820, 178871,' DECLARE @ sql_xml XML = Cast ( '<root><U>' + Supervene upon ( @ user_ids , ',' , '</U><U>' ) + '</U></root>' AS XML ) SELECT f . x . value ( '.' , 'BIGINT' ) Every bit user_id FROM @ sql_xml . nodes ( '/root/U' ) f ( 10 )

To handle this type of " or 0 data in the result set, nosotros can add the condition below in the T-SQL statement:

DECLARE @ user_ids VARCHAR ( MAX ) = N '203616, 198667, 193718, 188769, 183820, 178871,' DECLARE @ sql_xml XML = Bandage ( '<root><U>' + Replace ( @ user_ids , ',' , '</U><U>' ) + '</U></root>' Every bit XML ) SELECT f . ten . value ( '.' , 'BIGINT' ) Every bit user_id FROM @ sql_xml . nodes ( '/root/U' ) f ( x ) WHERE f . 10 . value ( '.' , 'BIGINT' ) <> 0

Here, f.ten.value('.', 'BIGINT') <> 0 excludes the 0 and f.x.value('.', 'VARCHAR(100)') <> " excludes the " in the query result gear up. This T-SQL condition can be added into the tabular array-valued functions with the number of input parameter to handle this extra delimiter.

Role:

1 2 iii 4 five half dozen 7 eight 9 10 xi 12 13 14 15 xvi 17

CREATE FUNCTION split_string_XML ( @ in_string VARCHAR ( MAX ) , @ delimeter VARCHAR ( 1 ) ) RETURNS @ list Table ( tuple VARCHAR ( 100 ) ) AS Begin DECLARE @ sql_xml XML = Cast ( '<root><U>' + Replace ( @ in_string , @ delimeter , '</U><U>' ) + '</U></root>' As XML ) INSERT INTO @ list ( tuple ) SELECT f . 10 . value ( '.' , 'VARCHAR(100)' ) AS tuple FROM @ sql_xml . nodes ( '/root/U' ) f ( x ) WHERE f . x . value ( '.' , 'BIGINT' ) <> 0 Render END

Execution:

SELECT * FROM split_string_XML ( '203616,198667,193718,188769,183820,178871,173922,' , ',' )

Combination of characters in delimiter-separated string

But what about when the input parameter is a combination of ii values with multiple separators? In this example, the input parameter must be extracted twice with the assist of a subquery, as shown beneath:

DECLARE @ in_string VARCHAR ( MAX ) = '1021|203616$1021|198667$1022|193718$1022|188769$' DECLARE @ sql_xml XML = Cast ( '<root><U>' + Replace ( @ in_string , '$' , '</U><U>' ) + '</U></root>' Every bit XML ) SELECT X . Y . value ( '(U)[1]' , 'VARCHAR(20)' ) AS role_id , X . Y . value ( '(U)[ii]' , 'VARCHAR(xx)' ) Equally user_id FROM ( SELECT Cast ( '<root><U>' + Supercede ( f . ten . value ( '.' , 'VARCHAR(MAX)' ) , '|' , '</U><U>' ) + '</U></root>' AS XML ) AS xml_ FROM @ sql_xml . nodes ( '/root/U' ) f ( ten ) WHERE f . x . value ( '.' , 'VARCHAR(MAX)' ) <> '' ) T OUTER APPLY T . xml_ . nodes ( 'root' ) as X ( Y )

This example is similar to the above case just nosotros accept utilized pipe "|" every bit a delimiter with a 2d delimiter, dollar "$". Here, the input parameter is the combination of two-columns, role_id, and user_id. Every bit we see below, user_id and role_id are in a carve up cavalcade in the table outcome set:

We can utilize any character in the higher up function, such every bit a single graphic symbol-separated string or combination of graphic symbol-separated strings. A developer must simply supplant the grapheme in the T-SQL statements higher up. Follow the steps described here to catechumen any delimited string to a list. Merely remember that the XML office of SQL Server accepts a string input and the input string volition be delimited with the specified separator using the tabular role.

• Writer
• Recent Posts

Jignesh has good experience in Database Solutions and Architecture, working with multiple customers on Database Pattern & Architecture, SQL Development, Administration, Query Optimization, Performance Tuning, HA and Disaster Recovery.

View all posts by Jignesh Raiyani

greenmatephy70.blogspot.com

Source: https://www.sqlshack.com/converting-commas-or-other-delimiters-to-a-table-or-list-in-sql-server-using-t-sql/

Share this post