SQL - [區間機號] - 判斷目前機號(Ex: A00042) 在[區間機號] 內(Ex: A00040-00045)

 目的: SQL - [區間機號] - 判斷[目前機號(Ex: A00042)] 在[區間機號] 內(Ex: A00040-00045)

處理方法: 1>找出 [區間機號(AC1-AC2)]的 AC1 & AC2
                  2>找出AC1 & AC2 的數字 - 將非數字(\D)字元改成''

AC1:    CAST(regexp_replace(SUBSTRING(B.SERNR_LO,1,INSTR(B.SERNR_LO,'-')-1),'\D+', '') as int ) as AC_1

AC2:   CAST(regexp_replace(SUBSTRING(B.SERNR_LO,INSTR(B.SERNR_LO,'-')+1,5), '\D+', '') as int) as AC_2

                  3>判斷   AC1<=目前機號<=AC2

SQL 如下:
SELECT  A.AUFNR,A.MATNR,B.SERNR_LO,
                CAST(regexp_replace(SUBSTRING(B.SERNR_LO,1,INSTR(B.SERNR_LO,'-')-1),'\D+', '') as int ) as AC_1,
                CAST(regexp_replace(SUBSTRING(B.SERNR_LO,INSTR(B.SERNR_LO,'-')+1,5), '\D+', '') as int) as AC_2
FROM     SAPR3.AFPO@SAP A,SAPR3.TECS@SAP B
WHERE A.TECHS=B.TECHS
AND       a.MANDT='999'
and          b.MANDT='999'
AND       B.SERNR_LO LIKE '%-%'
AND      42>=CAST(regexp_replace(SUBSTRING(B.SERNR_LO,1,INSTR(B.SERNR_LO,'-')-1),'\D+', '') as int )
AND      42<=CAST(regexp_replace(SUBSTRING(B.SERNR_LO,INSTR(B.SERNR_LO,'-')+1,5), '\D+', '') as int);