Oracle and MySQL String concatenation

Oracle and MySQL String concatenation

I. MySQL

In Java, we usually use the plus sign (+) to concatenate strings. in MySQL, we can also use the plus sign (+). For example:

Add test data first

Create table test (
Id INT,
Name VARCHAR (10 ),
Score FLOAT
);
Insert into test VALUES (1, 'zhang ', 98 );
Insert into test VALUES (2, 'lil', 95 );

Demo1

Select name + 'hello' FROM test;

Execution result:

MySQL will try to convert the field values at both ends of the plus sign to the numeric type. If the conversion fails, the field value is regarded as 0.

Demo2

SELECT score, score + 5 FROM test;

Execution result:

From the above, we can see that "+" is actually an operator. To concatenate strings in MySQL, you must use the CONCAT function,

The CONCAT function supports one or more parameters. The parameter type can be string or non-string. For non-string parameters, MySQL will try

Convert it to the string type. The CONCAT function concatenates all parameters into a string in the order of parameters as the return value.

Demo3

Select concat (NAME, '-hello', 'good') FROM test;

Execution result:

MySQL also provides another String concatenation function CONCAT_WS,

CONCAT_WS can add the specified separator between the strings to be spliced. The first parameter is the delimiter to be set,

The remaining parameters are the string values to be spliced.

Demo4

SELECT CONCAT_WS ('-', NAME, '', score) FROM test;

Execution result:

Ii. Oracle

Concatenate strings using "|" in Oracle

Demo5

Select name | 'hello' from test;

Execution result:

In addition to "|", Oracle also supports String concatenation using the CONCAT () function.

Demo6

Execution result:

If the connected value in CONCAT is not a string, Oracle will try to convert it to a string. Unlike MySQL's CONCAT () function, Oracle's CONCAT () function only supports two parameters, splicing of two or more strings is not supported.

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Install Oracle 11.2.0.4 x64 in Oracle Linux 6.5