Instr与SubstrB语法的学习

来源：互联网发布：c v8调用js 函数编辑：程序博客网时间：2024/05/16 08:22

2011-01-17

好久没有写下自己的心得了,现将Instr与SubStrB语法学习的心得放于博客上供大家学习与参考.在此主要放一些语法与结果,会在留言版中进行补充说明,希望大家不吝赐教.多提宝贵意见,以弥补不足之处.

SELECT A.DATA_OLD,Decode(Instr(A.UPD_DATA,'-'),1,'0',SubstrB(A.UPD_DATA,1,Instr(A.UPD_DATA,'-') - 1)) OQ,

SubstrB(A.UPD_DATA,Instr(A.UPD_DATA,'-') +4) NQ

FROM

(

SELECT DATA_OLD,Trim(SUBSTRB(DATA_OLD,Instr(DATA_OLD,'HAND_QTY:') + 9)) UPD_DATA

FROM UPDATE_LOG

WHERE DATA_OLD LIKE 'FACT_NO:B0HW PRO_DATE:201012%'

AND (Instr(SUBSTRB(DATA_OLD,Instr(DATA_OLD,'HAND_QTY:') + 9),'1') > 0

or Instr(SUBSTRB(DATA_OLD,Instr(DATA_OLD,'HAND_QTY:') + 9),'2') > 0

or Instr(SUBSTRB(DATA_OLD,Instr(DATA_OLD,'HAND_QTY:') + 9),'3') > 0

or Instr(SUBSTRB(DATA_OLD,Instr(DATA_OLD,'HAND_QTY:') + 9),'4') > 0

or Instr(SUBSTRB(DATA_OLD,Instr(DATA_OLD,'HAND_QTY:') + 9),'5') > 0

or Instr(SUBSTRB(DATA_OLD,Instr(DATA_OLD,'HAND_QTY:') + 9),'6') > 0

or Instr(SUBSTRB(DATA_OLD,Instr(DATA_OLD,'HAND_QTY:') + 9),'7') > 0

or Instr(SUBSTRB(DATA_OLD,Instr(DATA_OLD,'HAND_QTY:') + 9),'8') > 0

or Instr(SUBSTRB(DATA_OLD,Instr(DATA_OLD,'HAND_QTY:') + 9),'9') > 0)

ORDER BY SER_NO)A

结果如下:

DATA_OLD OQ NQ
FACT_NO:B0HW PRO_DATE:20101201 SEC_NO:300007 TIME_NO:02 DATA_KIND:3 FACT_ODR_NO:BA10110027 SIZERUN:L07- HAND_QTY:--->12012
FACT_NO:B0HW PRO_DATE:20101201 SEC_NO:300007 TIME_NO:02 DATA_KIND:3 FACT_ODR_NO:BA10110027 SIZERUN:L08 HAND_QTY:--->18018
FACT_NO:B0HW PRO_DATE:20101201 SEC_NO:300007 TIME_NO:03 DATA_KIND:3 FACT_ODR_NO:BA10110027 SIZERUN:L08- HAND_QTY:--->60060
FACT_NO:B0HW PRO_DATE:20101201 SEC_NO:300007 TIME_NO:03 DATA_KIND:3 FACT_ODR_NO:BA10110027 SIZERUN:L10- HAND_QTY:--->10010
FACT_NO:B0HW PRO_DATE:20101202 SEC_NO:300007 TIME_NO:06 DATA_KIND:3 FACT_ODR_NO:BA10110018R2 SIZERUN:L14 HAND_QTY:--->808
FACT_NO:B0HW PRO_DATE:20101203 SEC_NO:500009 TIME_NO:07 DATA_KIND:5 FACT_ODR_NO:BA10110014 SIZERUN:L07 HAND_QTY:--->10010
FACT_NO:B0HW PRO_DATE:20101203 SEC_NO:500009 TIME_NO:07 DATA_KIND:5 FACT_ODR_NO:BA10110014 SIZERUN:L08 HAND_QTY:--->20020
FACT_NO:B0HW PRO_DATE:20101203 SEC_NO:500009 TIME_NO:07 DATA_KIND:5 FACT_ODR_NO:BA10110014 SIZERUN:L09 HAND_QTY:--->42042
FACT_NO:B0HW PRO_DATE:20101203 SEC_NO:500009 TIME_NO:07 DATA_KIND:5 FACT_ODR_NO:BA10110014 SIZERUN:L10 HAND_QTY:--->60060
FACT_NO:B0HW PRO_DATE:20101203 SEC_NO:500009 TIME_NO:07 DATA_KIND:5 FACT_ODR_NO:BA10110014 SIZERUN:L11 HAND_QTY:--->60060
FACT_NO:B0HW PRO_DATE:20101203 SEC_NO:500009 TIME_NO:07 DATA_KIND:5 FACT_ODR_NO:BA10110014 SIZERUN:L12 HAND_QTY:--->30030
FACT_NO:B0HW PRO_DATE:20101203 SEC_NO:500009 TIME_NO:07 DATA_KIND:5 FACT_ODR_NO:BA10110014 SIZERUN:L13 HAND_QTY:--->10010
FACT_NO:B0HW PRO_DATE:20101203 SEC_NO:500009 TIME_NO:07 DATA_KIND:6 FACT_ODR_NO:BA10110014 SIZERUN:L07 HAND_QTY:--->10010

接下来,我需要获取我所要的资料,语法如下:

SELECT FACT,REC_DATE,SEC_NO,TN,D,ODR_NO,HAND_QTY,

DATA_OLD,Decode(Instr(UPD_DATA,'-'),1,'0',SubstrB(UPD_DATA,1,Instr(UPD_DATA,'-') - 1)) OQ,

SubstrB(UPD_DATA,Instr(UPD_DATA,'-') +4) NQ

FROM

(SELECT SUBSTR(DATA_OLD,9,4) FACT,SUBSTR(DATA_OLD,31,8) REC_DATE,SUBSTR(DATA_OLD,55,6) SEC_NO,

SUBSTR(DATA_OLD,78,2) TN,SUBSTR(DATA_OLD,99,2) D,TRIM(SUBSTR(DATA_OLD,121,18)) ODR_NO,

TRIM(SUBSTR(DATA_OLD,158,4)) SIZERUN,SUBSTR(DATA_OLD,170,20) HAND_QTY,

Trim(SUBSTRB(DATA_OLD,Instr(DATA_OLD,'HAND_QTY:') + 9)) UPD_DATA,DATA_OLD

FROM UPDATE_LOG

WHERE SUBSTR(DATA_OLD,9,4) LIKE 'B0HW%' AND SUBSTR(DATA_OLD,31,8) LIKE '201012%');

结果如下:

FACT REC_DATE SEC_NO TN D ODR_NO HAND_QTY OQNQ
B0HW20101201 300007 02 3 BA10110027 HAND_QTY:--->12012
B0HW20101201 300007 02 3 BA10110027HAND_QTY:--->18018
B0HW20101201 300007 03 3 BA10110027 HAND_QTY:--->60060
B0HW20101201 300007 03 3 BA10110027 HAND_QTY:--->10010
B0HW20101202 300007 06 3 BA10110018R2HAND_QTY:--->808
B0HW20101203 500009 07 5 BA10110014HAND_QTY:--->10010
B0HW20101203 500009 07 5 BA10110014HAND_QTY:--->20020
B0HW20101203 500009 07 5 BA10110014HAND_QTY:--->42042
B0HW20101203 500009 07 5 BA10110014HAND_QTY:--->60060
B0HW20101203 500009 07 5 BA10110014HAND_QTY:--->60060
B0HW20101203 500009 07 5 BA10110014HAND_QTY:--->30030
B0HW20101203 500009 07 5 BA10110014HAND_QTY:--->10010
B0HW20101203 500009 07 6 BA10110014HAND_QTY:--->10010