Array.prototype.splice()

SQL References

SQL Keywords ADD ADD CONSTRAINT ALTER ALTER COLUMN ALTER TABLE ALL AND ANY AS ASC BACKUP DATABASE BETWEEN CASE CHECK COLUMN CONSTRAINT CREATE CREATE DATABASE CREATE INDEX CREATE OR REPLACE VIEW CREATE TABLE CREATE PROCEDURE CREATE UNIQUE INDEX CREATE VIEW DATABASE DEFAULT DELETE DESC DISTINCT DROP DROP COLUMN DROP CONSTRAINT DROP DATABASE DROP DEFAULT DROP INDEX DROP TABLE DROP VIEW EXEC EXISTS FOREIGN KEY FROM FULL OUTER JOIN GROUP BY HAVING IN INDEX INNER JOIN INSERT INTO INSERT INTO SELECT IS NULL IS NOT NULL JOIN LEFT JOIN LIKE LIMIT NOT NOT NULL OR ORDER BY OUTER JOIN PRIMARY KEY PROCEDURE RIGHT JOIN ROWNUM SELECT SELECT DISTINCT SELECT INTO SELECT TOP SET TABLE TOP TRUNCATE TABLE UNION UNION ALL UNIQUE UPDATE VALUES VIEW WHERE

MySQL Functions String Functions ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE

Date Functions ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DATE_SUB DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_DAYS HOUR LAST_DAY LOCALTIME LOCALTIMESTAMP MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SECOND SEC_TO_TIME STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK

Advanced Functions BIN BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION

SQL Server Functions String Functions ASCII CHAR CHARINDEX CONCAT Concat with + CONCAT_WS DATALENGTH DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE STR STUFF SUBSTRING TRANSLATE TRIM UNICODE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATN2 AVG CEILING COUNT COS COT DEGREES EXP FLOOR LOG LOG10 MAX MIN PI POWER RADIANS RAND ROUND SIGN SIN SQRT SQUARE SUM TAN

Date Functions CURRENT_TIMESTAMP DATEADD DATEDIFF DATEFROMPARTS DATENAME DATEPART DAY GETDATE GETUTCDATE ISDATE MONTH SYSDATETIME YEAR

Advanced Functions CAST COALESCE CONVERT CURRENT_USER IIF ISNULL ISNUMERIC NULLIF SESSION_USER SESSIONPROPERTY SYSTEM_USER USER_NAME

MS Access Functions String Functions Asc Chr Concat with & CurDir Format InStr InstrRev LCase Left Len LTrim Mid Replace Right RTrim Space Split Str StrComp StrConv StrReverse Trim UCase

Numeric Functions Abs Atn Avg Cos Count Exp Fix Format Int Max Min Randomize Rnd Round Sgn Sqr Sum Val

Date Functions Date DateAdd DateDiff DatePart DateSerial DateValue Day Format Hour Minute Month MonthName Now Second Time TimeSerial TimeValue Weekday WeekdayName Year

Other Functions CurrentUser Environ IsDate IsNull IsNumeric

SQL OperatorsSQL Data TypesSQL Quick Ref


SQL Plus Operator Overview

Usually, we use a SQL Plus (+) operator to perform SQL Server Concatenate operation with multiple fields together. We can specify space character as well in between these columns.

Examples of SQL Plus(+) Operator

Look at the following query. In this query, we use SQL Plus(+) operator and space between the single quote as a separator between these fields.

1 2 3 4 5 6

SELECTTOP10FirstName,

MiddleName,

LastName,

FirstName+’ ‘+MiddleName+’ ‘+LastNameASFullName

FROMAdventureWorks2017.Person.Person

WHEREMiddlenameISNOTNULL;

In the output of SQL Server Concatenate using SQL Plus (+) operator, we have concatenate data from these fields (firstname, MiddleName and LastName) as a new column FullName.

We have a drawback in SQL Server Concatenate data with SQL Plus(+) operator. Look at the following example.

1 2 3 4 5

SELECTTOP10FirstName,

MiddleName,

LastName,

FirstName+’ ‘+MiddleName+’ ‘+LastNameASFullName

FROMAdventureWorks2017.Person.Person

In this example, we can see that if we have any NULL value present in any fields, we get output of concatenate string as NULL with SQL Plus(+) operator.

We can use SQL ISNULL function with + operator to replace NULL values with a space or any specific value. Execute the following query and we can still use SQL Plus(+) operator to concatenate string for us.

1 2 3 4 5

SELECTTOP10FirstName,

MiddleName,

LastName,

ISNULL(FirstName,»)+’ ‘+ISNULL(MiddleName,»)+’ ‘+ISNULL(LastName,»)ASFullName

FROMAdventureWorks2017.Person.Person;

Let’s look at another example of SQL Server Concatenate values with string as well as numeric values. In the following query, we want to concatenate first, middle, full name along with the NationalID number.

1 2 3 4 5 6 7 8

SELECTp.Title,

p.FirstName,

p.MiddleName,

p.LastName,

p.FirstName+’ ‘+p.MiddleName+’ ‘+p.LastName+’NationalIDNumber is :’+e.NationalIDNumberASEmpDetail

FROMHumanResources.Employeee

INNERJOINPerson.PersonpONp.BusinessEntityID=e.BusinessEntityID

WHEREp.MiddlenameISNOTNULL;

Suppose we want the single quote as well in the SQL Server Concatenate. In SQL Server, once we combine strings using SQL Plus(+) operator, it concatenates values inside single quotes. In the following query, we can see we specified two single quotes to display a single quote in the output.

1 SELECT’Let»s’+’ explore SQL Server with articles on SQLShack’;

If there is any mismatch or incorrect use of the single quote, you get following error message.

Msg 102, Level 15, State 1, Line 4 Incorrect syntax near ‘ + ‘. Msg 319, Level 15, State 1, Line 4 Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Msg 105, Level 15, State 1, Line 4 Unclosed quotation mark after the character string ‘; ‘.

If we want to print more single quotes, we need to define in the string in the following format with two single quotes.

Expected Output: ‘Let’s explore SQL Server with articles on SQLShack’

We can modify SQL query as follow.

1 SELECT»’Let»s’+’ explore SQL Server with articles on SQLShack»’;

Note: You should not confuse a single quote with the double quotes. SQL Server treats double quotes as a character.

In the following query, we used a double quote in a combination of a single quote.

1 SELECT'»»Let»s’+’ explore SQL Server with articles on SQLShack»’;

We can do SQL Server Concatenate operation using the SQL Plus(+) operator; however, it becomes complex if you need to use multiple single quotes. It is difficult to debug code as well since you need to look at all the single quotes combinations as well.

# Alternative Concat Syntax

Alternatively, you can also write the method, in this regard:

As you can see, this way of writing it doesn’t manipulate or change the existing array.

Which one should I pick?


Let’s list out both versions, so you can see it in comparison.

So now the question is, which one should I pick . I prefer Version A because I think the intention is a lot more clear. Just by looking at it, I know I’m creating a new array and I’m not manipulating the existing array. Whereas if I look at Version B, it appears like I’m adding the array to the array, and it doesn’t seem obvious to me that the array isn’t being changed. But, maybe that’s just me. I’d be curious to know what you think?

Since I don’t really have a substantial reason besides aesthetics, I think you and your team should stick with whatever you choose

SQL References

SQL Keywords ADD ADD CONSTRAINT ALTER ALTER COLUMN ALTER TABLE ALL AND ANY AS ASC BACKUP DATABASE BETWEEN CASE CHECK COLUMN CONSTRAINT CREATE CREATE DATABASE CREATE INDEX CREATE OR REPLACE VIEW CREATE TABLE CREATE PROCEDURE CREATE UNIQUE INDEX CREATE VIEW DATABASE DEFAULT DELETE DESC DISTINCT DROP DROP COLUMN DROP CONSTRAINT DROP DATABASE DROP DEFAULT DROP INDEX DROP TABLE DROP VIEW EXEC EXISTS FOREIGN KEY FROM FULL OUTER JOIN GROUP BY HAVING IN INDEX INNER JOIN INSERT INTO INSERT INTO SELECT IS NULL IS NOT NULL JOIN LEFT JOIN LIKE LIMIT NOT NOT NULL OR ORDER BY OUTER JOIN PRIMARY KEY PROCEDURE RIGHT JOIN ROWNUM SELECT SELECT DISTINCT SELECT INTO SELECT TOP SET TABLE TOP TRUNCATE TABLE UNION UNION ALL UNIQUE UPDATE VALUES VIEW WHERE

MySQL Functions String Functions ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE

Date Functions ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DATE_SUB DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_DAYS HOUR LAST_DAY LOCALTIME LOCALTIMESTAMP MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SECOND SEC_TO_TIME STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK

Advanced Functions BIN BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION

SQL Server Functions String Functions ASCII CHAR CHARINDEX CONCAT Concat with + CONCAT_WS DATALENGTH DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE STR STUFF SUBSTRING TRANSLATE TRIM UNICODE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATN2 AVG CEILING COUNT COS COT DEGREES EXP FLOOR LOG LOG10 MAX MIN PI POWER RADIANS RAND ROUND SIGN SIN SQRT SQUARE SUM TAN

Date Functions CURRENT_TIMESTAMP DATEADD DATEDIFF DATEFROMPARTS DATENAME DATEPART DAY GETDATE GETUTCDATE ISDATE MONTH SYSDATETIME YEAR

Advanced Functions CAST COALESCE CONVERT CURRENT_USER IIF ISNULL ISNUMERIC NULLIF SESSION_USER SESSIONPROPERTY SYSTEM_USER USER_NAME

MS Access Functions String Functions Asc Chr Concat with & CurDir Format InStr InstrRev LCase Left Len LTrim Mid Replace Right RTrim Space Split Str StrComp StrConv StrReverse Trim UCase

Numeric Functions Abs Atn Avg Cos Count Exp Fix Format Int Max Min Randomize Rnd Round Sgn Sqr Sum Val

Date Functions Date DateAdd DateDiff DatePart DateSerial DateValue Day Format Hour Minute Month MonthName Now Second Time TimeSerial TimeValue Weekday WeekdayName Year

Other Functions CurrentUser Environ IsDate IsNull IsNumeric

SQL OperatorsSQL Data TypesSQL Quick Ref

Example

Let’s look at some Oracle CONCAT function examples and explore how to use the CONCAT function in Oracle/PLSQL.

For example:

CONCAT('Tech on', ' the Net')
Result: 'Tech on the Net'

CONCAT('a', 'b')
Result: 'ab'

Concatenate More Than 2 Values

In Oracle, the CONCAT function will only allow you to concatenate two values together. If you want to concatenate more values than two, you can nest multiple CONCAT function calls.

For example:

SELECT CONCAT(CONCAT('A', 'B'),'C')
FROM dual;
Result: 'ABC'

This example would concatenate the 3 values together and return ‘ABC’.


To concatenate 4 values, you can nest one more CONCAT function call.

For example:

SELECT CONCAT(CONCAT(CONCAT('A', 'B'),'C'),'D')
FROM dual;
Result: 'ABCD'

This example would concatenate the 4 values together and return ‘ABCD’.

Concatenate Single Quotes

Since the parameters within the CONCAT function are separated by single quotes, it isn’t straight forward how to add a single quote character within the result of the CONCAT function.

Let’s look at a fairly easy example that shows how to add a single quote to the resulting string using the CONCAT function.

For example:

SELECT CONCAT('Let''s', ' learn Oracle')
FROM dual;
Result: Let's learn Oracle

Looks like

constminNumber=constresult=q.select('table1.some_boring_id, table2.something_interesting, mystery_table.surprise', q`LEAST(table1.whatever, ${minNumber}) AS whatever`).from('table1').join('table2','table1.some_boring_id = table2.id').leftJoin('mystery_table','mystery_table.twister_reality = table2.probably_null_column').where('table1.pants','fancy').where('table1.britches','>',99).build()constexpectedQuery='SELECT table1.some_boring_id, table2.something_interesting, mystery_table.surprise, LEAST(table1.whatever, ?) AS whatever\n'+'FROM table1\n'+'JOIN table2 ON table1.some_boring_id = table2.id\n'+'LEFT JOIN mystery_table ON mystery_table.twister_reality = table2.probably_null_column\n'+'WHERE table1.pants = ? AND table1.britches > ?'result.sqlresult.values

SQL CONCAT FUNCTION

Starting from SQL Server 2012, we have a new function to concatenate strings in SQL Server.

Syntax of SQL CONCAT function

CONCAT ( string1, string2….stringN)

We require at least two values to concatenate together and specify in the SQL CONCAT function.

Examples

Let’s explore SQL CONCAT with an example. The following query, concatenate string and gives output as a single string. We specified multiple single quotes between each word to print space in between each word.

1 SELECTCONCAT(‘My’,’ ‘,’Name’,’ ‘,’is’,’ ‘,’Rajendra’,’ ‘,’Gupta’)ASintroduction

We can use system functions as well in concatenate sting using SQL CONCAT function.

1 SELECTCONCAT(‘SQL ISNULL function published on’,’ ‘,GETDATE()-2)ASSingleString

We use Getdate() function to get a specified date in a concatenated string as well.

In the previous section, we explored that if we want to concatenate string using + operator and any of string has a NULL value, and we get the output as NULL. We use SQL ISNULL function to replace NULL values in the string. We need to use SQL ISNULL with each column containing NULL values. If we have a large number of the column that may have NULL values, it becomes complex to write such code.

Let’s review this again with SQL CONCAT function. We only need to specify the SQL CONCAT function at once and specify all string columns. We get the output as a concatenated string.

1 2 3 4 5

SELECTTOP10FirstName,

MiddleName,

LastName,

CONCAT(FirstName,’ ‘,MiddleName,’ ‘,LastName)ASFullName

FROMAdventureWorks2017.Person.Person;

Note: If all the string passed in SQL CONCAT function have a NULL value, we get the output of this function also NULL.

SQL CONCAT and data type conversion

SQL CONCAT function implicitly converts arguments to string types before concatenation. We can use SQL CONVERT function as well without converting the appropriate data type.

If we concatenate string using the plus( +) operator, we need to use SQL CONVERT function to convert data types. Lets’ look this using the following example.

In the following query, we want to concatenate two strings. In this example, data type of first string is Text while another data type is a date.

1 2 3

Declare@TextVarchar(100)=’Microsoft SQL Server 2019 Community Technology Preview 2.5 launched on’

Declare@datedate=’20190422′

Select@Text+’ ‘+@dateasResult

Execute this query, and we get the following output.

Msg 402, Level 16, State 1, Line 3 The data types varchar and date are incompatible in the add operator.

We need to use SQL CONVERT function as per the following query, and it returns output without error message.

1 2 3


Declare@TextVarchar(100)=’Microsoft SQL Server 2019 Community Technology Preview 2.5 launched on’

Declare@datedate=’20190422′

Select@Text+’ ‘+Convert(varchar,(@date))asResult

We do not need to use SQL CONVERT function to convert data type in SQL CONCAT function. It automatically does the conversion based on the input data type.

1 2 3

Declare@TextVarchar(100)=’Microsoft SQL Server 2019 Community Technology Preview 2.5 launched on’

Declare@datedate=’20190422′

Selectconcat(@Text,@date)asResult

In the following table, we can see that data type conversion for input and output data types in SQL CONCAT function.

Input Data Type

Output Data Type

SQL CLR

NVARCHAR(MAX)

NVARCHAR(MAX)

NVARCHAR(MAX)

NVARCHAR(<=4000 characters)

NVARCHAR(<=4000 characters)

VARBINARY(MAX)

NVARCHAR(MAX)

All other data types

VARCHAR(<=8000) *if any parameters data type is NVARCHAR, the output value will be NVARCHAR(MAX)

JavaScript

JS Array concat() constructor copyWithin() entries() every() fill() filter() find() findIndex() forEach() from() includes() indexOf() isArray() join() keys() length lastIndexOf() map() pop() prototype push() reduce() reduceRight() reverse() shift() slice() some() sort() splice() toString() unshift() valueOf()

JS Boolean constructor prototype toString() valueOf()

JS Classes constructor() extends static super

JS Date constructor getDate() getDay() getFullYear() getHours() getMilliseconds() getMinutes() getMonth() getSeconds() getTime() getTimezoneOffset() getUTCDate() getUTCDay() getUTCFullYear() getUTCHours() getUTCMilliseconds() getUTCMinutes() getUTCMonth() getUTCSeconds() now() parse() prototype setDate() setFullYear() setHours() setMilliseconds() setMinutes() setMonth() setSeconds() setTime() setUTCDate() setUTCFullYear() setUTCHours() setUTCMilliseconds() setUTCMinutes() setUTCMonth() setUTCSeconds() toDateString() toISOString() toJSON() toLocaleDateString() toLocaleTimeString() toLocaleString() toString() toTimeString() toUTCString() UTC() valueOf()

JS Error name message

JS Global decodeURI() decodeURIComponent() encodeURI() encodeURIComponent() escape() eval() Infinity isFinite() isNaN() NaN Number() parseFloat() parseInt() String() undefined unescape()

JS JSON parse() stringify()

JS Math abs() acos() acosh() asin() asinh() atan() atan2() atanh() cbrt() ceil() cos() cosh() E exp() floor() LN2 LN10 log() LOG2E LOG10E max() min() PI pow() random() round() sin() sqrt() SQRT1_2 SQRT2 tan() tanh() trunc()

JS Number constructor isFinite() isInteger() isNaN() isSafeInteger() MAX_VALUE MIN_VALUE NEGATIVE_INFINITY NaN POSITIVE_INFINITY prototype toExponential() toFixed() toLocaleString() toPrecision() toString() valueOf()

JS OperatorsJS RegExp constructor compile() exec() g global i ignoreCase lastIndex m multiline n+ n* n? n{X} n{X,Y} n{X,} n$ ^n ?=n ?!n source test() toString() (x|y) . \w \W \d \D \s \S \b \B \0 \n \f \r \t \v \xxx \xdd \uxxxx

JS Statements break class continue debugger do…while for for…in for…of function if…else return switch throw try…catch var while

JS String charAt() charCodeAt() concat() constructor endsWith() fromCharCode() includes() indexOf() lastIndexOf() length localeCompare() match() prototype repeat() replace() search() slice() split() startsWith() substr() substring() toLocaleLowerCase() toLocaleUpperCase() toLowerCase() toString() toUpperCase() trim() valueOf()

Frequently Asked Questions

Question: How can you use the CONCAT function to concatenate more than 2 strings together?

Answer: Since the CONCAT function will only let you concatenate 2 strings, you will need to nest multiple CONCAT functions to concatenate more than 2 strings together.

For example, to concatenate 3 strings, you could nest the CONCAT function as follows:

CONCAT( CONCAT( string1, string2 ), string3 )

Or you could nest the CONCAT function as follows, if you wanted to concatenate 4 strings:

CONCAT( CONCAT( CONCAT( string1, string2 ), string3 ), string4 )

Here is an example provided by Ruth that demonstrates how to nest multiple CONCAT functions to concatenate 6 strings:

CONCAT( CONCAT( CONCAT( CONCAT( CONCAT(
 'I like ', t.type_desc_column), ' cake with '), t.icing_desc_column),' and a '),
   t.fruit_desc_column)

The CONCAT function is one method to concatenate strings in Oracle. An alternative to using the CONCAT function would be to use the || operator to catenatenate multiple strings, as follows:

'I like ' || t.type_desc_column || ' cake with ' || t.icing_desc_column || ' and a '
  || t.fruit_desc_column

С этим читают