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

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

PHPz
PHPzforward
2023-05-28 20:13:282001browse

    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!

    Statement:
    This article is reproduced at:yisu.com. If there is any infringement, please contact admin@php.cn delete