罗莎田园干红:Instr与SubstrB语法的学习

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:--->12 0 12
FACT_NO:B0HW PRO_DATE:20101201 SEC_NO:300007 TIME_NO:02 DATA_KIND:3 FACT_ODR_NO:BA10110027 SIZERUN:L08 HAND_QTY:--->18 0 18
FACT_NO:B0HW PRO_DATE:20101201 SEC_NO:300007 TIME_NO:03 DATA_KIND:3 FACT_ODR_NO:BA10110027 SIZERUN:L08- HAND_QTY:--->60 0 60
FACT_NO:B0HW PRO_DATE:20101201 SEC_NO:300007 TIME_NO:03 DATA_KIND:3 FACT_ODR_NO:BA10110027 SIZERUN:L10- HAND_QTY:--->10 0 10
FACT_NO:B0HW PRO_DATE:20101202 SEC_NO:300007 TIME_NO:06 DATA_KIND:3 FACT_ODR_NO:BA10110018R2 SIZERUN:L14 HAND_QTY:--->8 0 8
FACT_NO:B0HW PRO_DATE:20101203 SEC_NO:500009 TIME_NO:07 DATA_KIND:5 FACT_ODR_NO:BA10110014 SIZERUN:L07 HAND_QTY:--->10 0 10
FACT_NO:B0HW PRO_DATE:20101203 SEC_NO:500009 TIME_NO:07 DATA_KIND:5 FACT_ODR_NO:BA10110014 SIZERUN:L08 HAND_QTY:--->20 0 20
FACT_NO:B0HW PRO_DATE:20101203 SEC_NO:500009 TIME_NO:07 DATA_KIND:5 FACT_ODR_NO:BA10110014 SIZERUN:L09 HAND_QTY:--->42 0 42
FACT_NO:B0HW PRO_DATE:20101203 SEC_NO:500009 TIME_NO:07 DATA_KIND:5 FACT_ODR_NO:BA10110014 SIZERUN:L10 HAND_QTY:--->60 0 60
FACT_NO:B0HW PRO_DATE:20101203 SEC_NO:500009 TIME_NO:07 DATA_KIND:5 FACT_ODR_NO:BA10110014 SIZERUN:L11 HAND_QTY:--->60 0 60
FACT_NO:B0HW PRO_DATE:20101203 SEC_NO:500009 TIME_NO:07 DATA_KIND:5 FACT_ODR_NO:BA10110014 SIZERUN:L12 HAND_QTY:--->30 0 30
FACT_NO:B0HW PRO_DATE:20101203 SEC_NO:500009 TIME_NO:07 DATA_KIND:5 FACT_ODR_NO:BA10110014 SIZERUN:L13 HAND_QTY:--->10 0 10
FACT_NO:B0HW PRO_DATE:20101203 SEC_NO:500009 TIME_NO:07 DATA_KIND:6 FACT_ODR_NO:BA10110014 SIZERUN:L07 HAND_QTY:--->10 0 10

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

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       OQ NQ
B0HW 20101201 300007 02 3 BA10110027 HAND_QTY:--->12 0 12
B0HW 20101201 300007 02 3 BA10110027 HAND_QTY:--->18 0 18
B0HW 20101201 300007 03 3 BA10110027 HAND_QTY:--->60 0 60
B0HW 20101201 300007 03 3 BA10110027 HAND_QTY:--->10 0 10
B0HW 20101202 300007 06 3 BA10110018R2 HAND_QTY:--->8 0 8
B0HW 20101203 500009 07 5 BA10110014 HAND_QTY:--->10 0 10
B0HW 20101203 500009 07 5 BA10110014 HAND_QTY:--->20 0 20
B0HW 20101203 500009 07 5 BA10110014 HAND_QTY:--->42 0 42
B0HW 20101203 500009 07 5 BA10110014 HAND_QTY:--->60 0 60
B0HW 20101203 500009 07 5 BA10110014 HAND_QTY:--->60 0 60
B0HW 20101203 500009 07 5 BA10110014 HAND_QTY:--->30 0 30
B0HW 20101203 500009 07 5 BA10110014 HAND_QTY:--->10 0 10
B0HW 20101203 500009 07 6 BA10110014 HAND_QTY:--->10 0 10