NAME ------------------------------------ d-d 珍珠金-真皮咖啡 玛莎拉蒂蓝-运动蓝 钛晶灰-时尚灰
SQL> SELECT substr(a.name,1,INSTR(a.name,'-',1)-1)前,SUBSTR(a.name,INSTR(a.name,'-',1)+1) 后 FROM tb1 a;
前 后 ------------------------------------------------------------------------ ------------------------------------------------------------------------ d d 珍珠金 真皮咖啡 玛莎拉蒂蓝 运动蓝 钛晶灰 时尚灰
SQL> with t as( 2 select '玛莎拉蒂蓝-运动蓝' name from dual union all 3 select '珍珠金-真皮咖啡' from dual union all 4 select '钛晶灰-时尚灰' from dual 5 ) 6 select substr(name,1,instr(name,'-',1)-1) name_1,substr(name,instr(name,'-',1)+1) name_2 7 from t 8 / /* NAME_1 NAME_2 ---------------------------------- ---------------------------------- 玛莎拉蒂蓝 运动蓝 珍珠金 真皮咖啡 钛晶灰 时尚灰 */
WITH t AS (SELECT '玛莎拉蒂蓝-运动蓝' NAME FROM DUAL UNION ALL SELECT '珍珠金-真皮咖啡' FROM DUAL UNION ALL SELECT '钛晶灰-时尚灰' FROM DUAL) SELECT REGEXP_SUBSTR (NAME, '[^\-]+', 1, 1) name_1, REGEXP_SUBSTR (NAME, '[^\-]+', 1, 2) name_2 FROM t /
NAME
------------------------------------
d-d
珍珠金-真皮咖啡
玛莎拉蒂蓝-运动蓝
钛晶灰-时尚灰
SQL> SELECT substr(a.name,1,INSTR(a.name,'-',1)-1)前,SUBSTR(a.name,INSTR(a.name,'-',1)+1) 后 FROM tb1 a;
前 后
------------------------------------------------------------------------ ------------------------------------------------------------------------
d d
珍珠金 真皮咖啡
玛莎拉蒂蓝 运动蓝
钛晶灰 时尚灰
SQL> with t as(
2 select '玛莎拉蒂蓝-运动蓝' name from dual union all
3 select '珍珠金-真皮咖啡' from dual union all
4 select '钛晶灰-时尚灰' from dual
5 )
6 select substr(name,1,instr(name,'-',1)-1) name_1,substr(name,instr(name,'-',1)+1) name_2
7 from t
8 /
/*
NAME_1 NAME_2
---------------------------------- ----------------------------------
玛莎拉蒂蓝 运动蓝
珍珠金 真皮咖啡
钛晶灰 时尚灰
*/
(SELECT '玛莎拉蒂蓝-运动蓝' NAME
FROM DUAL
UNION ALL
SELECT '珍珠金-真皮咖啡'
FROM DUAL
UNION ALL
SELECT '钛晶灰-时尚灰'
FROM DUAL)
SELECT REGEXP_SUBSTR (NAME, '[^\-]+', 1, 1) name_1,
REGEXP_SUBSTR (NAME, '[^\-]+', 1, 2) name_2
FROM t
/