"||" in oracle is Splicing values, but in mysql it means "or".
where name like '%' || 'Tony' || '%'
So it is recommended to use 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' , '%') )
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;
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,'%');
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!