Saturday, April 6, 2019

String Concatenation in SQL Using NetSuite ODBC

Concatenation is a technique which is one of the most used in SQL.
NetSuite ODBC connection uses DataDirect driver which allows two ways of SQL syntax:
1. "+" concatenation operator
2. concat function

The "+" operator is easier to use because the query is shorter. However, DataDirect returns only certain amount of characters when using "+". Therefore, it is useful for concatenating two or three values.

select firstname + ' ' + lastname from employees;

Output (one row):

Svako Ragan

The "concat" function is more restricted than the "+" operator as it can have only two arguments. However, the amount of characters returned in one field is much bigger than when using "+".

select concat('',lastname) from employees;

Output (one row):

TIP: Advantages of both ways of concatenating can be combined by using them together. Output string is long, concat is used with only 2 parameters and the query is more readable.
Example #1:

select concat('','category_0:"' + category_0 + '", companyname:"' + companyname + '", full_name:"' + full_name + '", email:"' + email + '"') as data from customers

Output (one row):

category_0:"PROSPECT", companyname:"Smith Books", full_name:"Smith Books", email:""

No comments:

Post a Comment