SQL行不,不行你把下面的sql放到函数里面封装一下吧。。SELECT REPLACE(WMSYS.WM_CONCAT(SUBSTR(A, 2, 1) || SUBSTR(A, 1, 1)), ',', '') FROM (SELECT SUBSTR('23452145', (ROWNUM - 1) * 2 + 1, 2) A FROM DUAL CONNECT BY ROWNUM <= LENGTH('23452145') / 2 + 1)
CREATE OR REPLACE FUNCTION FUC_TURN_STR(SOURCE_STR IN VARCHAR2) RETURN VARCHAR2 IS TARGET_STR VARCHAR2(4000); BEGIN SELECT REPLACE(WMSYS.WM_CONCAT(SUBSTR(A, 2, 1) || SUBSTR(A, 1, 1)), ',', '') INTO TARGET_STR FROM (SELECT SUBSTR(SOURCE_STR, (ROWNUM - 1) * 2 + 1, 2) A FROM DUAL CONNECT BY ROWNUM <= LENGTH(SOURCE_STR) / 2 + 1); RETURN(TARGET_STR); END FUC_TURN_STR;SQL> SELECT FUC_TURN_STR('1245DFDF') T FROM DUAL;T ---------------------------------- 2154FDFD
这个不对啊 1245DFDF 转成 这样 DFDF4512
啊,理解错了,那如果出现单数怎么办啊?CREATE OR REPLACE FUNCTION FUC_TURN_STR(SOURCE_STR IN VARCHAR2) RETURN VARCHAR2 IS TARGET_STR VARCHAR2(4000); BEGIN SELECT REPLACE(WMSYS.WM_CONCAT(A), ',', '') INTO TARGET_STR FROM (SELECT SUBSTR(SOURCE_STR, LENGTH(SOURCE_STR) - (ROWNUM - 1) * 2 + 1, 2) A FROM DUAL CONNECT BY ROWNUM <= LENGTH(SOURCE_STR) / 2 + 1); RETURN(TARGET_STR); END FUC_TURN_STR; 你要确定哈,否者结果可能会有差别
CREATE OR REPLACE FUNCTION FUC_TURN_STR(SOURCE_STR IN VARCHAR2) RETURN VARCHAR2 IS TARGET_STR VARCHAR2(4000); BEGIN SELECT REPLACE(WMSYS.WM_CONCAT(A), ',', '') INTO TARGET_STR FROM (SELECT SUBSTR(SOURCE_STR, LENGTH(SOURCE_STR) - (ROWNUM - 1) * 2 - 1, 2) A FROM DUAL CONNECT BY ROWNUM <= LENGTH(SOURCE_STR) / 2); RETURN(TARGET_STR); END FUC_TURN_STR; 最终答案哈,呵呵,用这个吧,好理解一点。。
FROM (SELECT SUBSTR('23452145', (ROWNUM - 1) * 2 + 1, 2) A
FROM DUAL
CONNECT BY ROWNUM <= LENGTH('23452145') / 2 + 1)
RETURN VARCHAR2 IS
TARGET_STR VARCHAR2(4000);
BEGIN
SELECT REPLACE(WMSYS.WM_CONCAT(SUBSTR(A, 2, 1) || SUBSTR(A, 1, 1)),
',',
'')
INTO TARGET_STR
FROM (SELECT SUBSTR(SOURCE_STR, (ROWNUM - 1) * 2 + 1, 2) A
FROM DUAL
CONNECT BY ROWNUM <= LENGTH(SOURCE_STR) / 2 + 1);
RETURN(TARGET_STR);
END FUC_TURN_STR;SQL> SELECT FUC_TURN_STR('1245DFDF') T FROM DUAL;T
----------------------------------
2154FDFD
RETURN VARCHAR2 IS
TARGET_STR VARCHAR2(4000);
BEGIN
SELECT REPLACE(WMSYS.WM_CONCAT(A), ',', '')
INTO TARGET_STR
FROM (SELECT SUBSTR(SOURCE_STR,
LENGTH(SOURCE_STR) - (ROWNUM - 1) * 2 + 1,
2) A
FROM DUAL
CONNECT BY ROWNUM <= LENGTH(SOURCE_STR) / 2 + 1);
RETURN(TARGET_STR);
END FUC_TURN_STR;
你要确定哈,否者结果可能会有差别
RETURN VARCHAR2 IS
TARGET_STR VARCHAR2(4000);
BEGIN
SELECT REPLACE(WMSYS.WM_CONCAT(A), ',', '')
INTO TARGET_STR
FROM (SELECT SUBSTR(SOURCE_STR,
LENGTH(SOURCE_STR) - (ROWNUM - 1) * 2 - 1,
2) A
FROM DUAL
CONNECT BY ROWNUM <= LENGTH(SOURCE_STR) / 2);
RETURN(TARGET_STR);
END FUC_TURN_STR;
最终答案哈,呵呵,用这个吧,好理解一点。。
我尽力了亲,这个sql不算复杂了啊。。