数据库 concat 与 ||
mysql中用concat,oracle中concat和||都有,都是做字符串拼接的
oracle简单实例:
1.建表
CREATE TABLE tab1 (col1 VARCHAR2(6), col2 CHAR(6),
col3 VARCHAR2(6), col4 CHAR(6) );
INSERT INTO tab1 (col1, col2, col3, col4)
VALUES ('abc', 'def ', 'ghi ', 'jkl');
INSERT INTO tab1 (col1, col2, col3, col4)
VALUES ('1a', '456 ', '321 ', '234');
INSERT INTO tab1 (col1, col2, col3, col4)
VALUES ('45345', '656 ', '65 ', 'a1');
2.简单运用
select*from tab1;
SELECT * from tab1 t where t.col1 like '%a%'; //模糊查询
SELECT * from tab1 t where t.col1 like '%' || 'a' || '%';//模糊查询
SELECT * from tab1 t where t.col1 like concat(concat('%','a'),'%');//模糊查询
SELECT col1||col2||col3||col4 "Concatenation" FROM tab1;
SELECT 'col1=' || t.col1 || ',col2=' || t.col2 "字段拼接" FROM tab1 t; //也就是当我们需要自定义查询结果的时候,使用||拼接。
3.项目运用
生成由各种字段拼接的字段然后更新表
UPDATE sr_main
SET mjjzbh = 'ZJDB' || substr(xzqh, 0, 6) || to_char(sysdate, 'yyyymm') ||
(SELECT (CASE
WHEN max(xlh) IS NULL THEN
10001
ELSE
(max(xlh) + 1)
END) AS new_xlh
FROM (SELECT SUBSTR(mjjzbh, -5) AS xlh
FROM sr_main
WHERE mjjzbh LIKE
'ZJDB' || '%' || TO_CHAR(SYSDATE, 'yyyymm') || '%'
GROUP BY mjjzbh))
WHERE pk_sr_main = '$pkid$'
模糊查询:
SELECT * FROM bd_database t WHERE t.sys_xzqh like concat(?,'%') AND t.defstr1 = '9013'