Pitfalls encountered in oracle/mysql splicing values ​​and how to use double vertical bars || and concat

oracle mysql pitfall encountered when concatenating values ​​​​double vertical bars|| concat

||

"||" in oracle is Splicing values, but in mysql it means "or".

where name like '%' || 'Tony' || '%'

So it is recommended to use concat()

concat()

But concat() also has pitfalls.

In mysql, you can directly use concat to splice 3 values. In

concat( '%' , 'Tonny' , '%' )

oracle, this use is wrong. Because Oracle's concat can only splice 2 values, it needs to be like this:

concat( '%' , concat('Tonny' , '%') )

Field splicing in Oracle CONCAT and ||

In Oracle, there are two methods of string splicing, namely CONCAT() function and "||" splicing

1. CONCAT() function splicing

Disadvantages: only supports the splicing of two strings, if more than two, an error will be reported (report The error seems to be the missing right bracket)

//表中的两个字段拼接
select concat(t1.column_1,t1.column_2) from table t1;//任意一个字段与任意字符串拼接 (time是取的别名，记住：Oracle 取别名不要用as )
select concat('时间是: ',t1.column_2) time from table t1;
select concat(t1.column_1,' 单位：元') time from table t1;//超过两个字段，会报错（下面这样写会报错）
select concat(t1.column_1,t1.column_2,t1.column_3) from table t1;

When using the CONCAT() function to splice strings, if there are Chinese characters in the spliced ​​fields (strings), garbled characters may result. The solution is to add to_char() to the spliced ​​field (string):

//如果遇到乱码，加上to_char()
select concat(to_char(t1.column_1),to_char(t1.column_2)) time from table t1;

2. Use "||" to splice strings

Use "|| "Splicing, there is no restriction.

//表中两个字符串拼接，取别名为time
select t1.column_1 || t1.column_2 time from table t1;//表中三个字符串拼接，取别名为time
//这里可以使用括号将几个要拼接的字段括起来，可读性会好点，好像加不加括号都不影响
select (t1.column_1 || t1.column_2 || t1.column_3) time from table t1;

The advantage of using "||" to splice, you can use this when doing fuzzy query

//这样可以动态进行模糊查询，field是动态值
select t1.* from table t1 where t1.name like '%' || field || '%';//如果对模糊查询更加细粒度，当然，也可以使用concat()进行模糊查询
select t1.* from table t1 where t1.name like concat('%',field);
select t1.* from table t1 where t1.name like concat(field,'%');

Additional: mysql use Pitfalls encountered by concat or || when concatenating strings

Business needs, concatenate several fields into one field for query, and found that all of them are empty after checking. Later, when searching online, I found:
Use || Or concat concatenates strings. If one of them is null, it becomes null
Can be avoided by using concat_ws

The above is the detailed content of Pitfalls encountered in oracle/mysql splicing values ​​and how to use double vertical bars || and concat. For more information, please follow other related articles on the PHP Chinese website!