EX : Identify all the values from below strings which starts with "MTC":
* MOCS13;MTCIN13;MTCIN14;100IN14;111IN14;123IN14
* MTCIN13;MTCIN14;100IN14;111IN14;123IN14
* MOCS13;MTCIN13;MTCIN14
* MOCS13;M100IN14;111IN14;123IN14
WITH t AS
(SELECT 'MOCS13;MTCIN13;MTCIN14;100IN14;111IN14;123IN14' AS list FROM dual
UNION ALL
SELECT 'BOB13;MTCIN23;123IN14' FROM dual
UNION ALL
SELECT 'MTCIN11;123IN14;MTC123X' FROM dual
) ,
t2 AS
(SELECT list,
row_number() over (partition BY list order by 1) AS pos
FROM t,
(SELECT rownum FROM dual CONNECT BY rownum <= 100
)
) -- assumes never more than 100 entries in list
SELECT list,
ltrim(sys_connect_by_path(mtcs,';'),';') AS mtcs
FROM
(SELECT list,
ltrim(REGEXP_SUBSTR(';'
||list,';[[:alnum:]]*',1,pos),';') AS MTCs,
row_number() over (partition BY list order by 1) rn
FROM t2
WHERE ltrim(REGEXP_SUBSTR(';'
||list,';[[:alnum:]]*',1,pos),';') LIKE 'MTC%'
)
WHERE connect_by_isleaf = 1
CONNECT BY list = prior list
AND RN = prior RN+1
START WITH rn = 1
LIST MTCS
---------------------------------------------- ------------------------------
BOB13;MTCIN23;123IN14 MTCIN23
MOCS13;MTCIN13;MTCIN14;100IN14;111IN14;123IN14 MTCIN13;MTCIN14
MTCIN11;123IN14;MTC123X MTCIN11;MTC123XFOr the same example above , if we need each substring in diffrent lines. Then
WITH t AS
(SELECT 'MOCS13;MTCIN13;MTCIN14;100IN14;111IN14;123IN14' AS list FROM dual
)
SELECT ltrim(REGEXP_SUBSTR(';'
||list,';[[:alnum:]]*',1,rn),';') AS MTCs
FROM t,
(SELECT rownum rn
FROM dual
CONNECT BY rownum <=
(SELECT LENGTH(regexp_replace(list,'[^;]'))+1 FROM t
)
)
WHERE ltrim(REGEXP_SUBSTR(';'
||list,';[[:alnum:]]*',1,rn),';') LIKE 'MTC%'
MTCS
-----------------------------------------------
MTCIN13
MTCIN14/**************************************************************************
************************************************************************/
EX 2: Identify all distinct <Partnumbers> from below XML data
<vmsg action="PartsListResp"><mwid>HFERRIS</mwid><Model>70564</Model><PartList><Entry><PartNumber>70424-001</PartNumber><PartDesc>CABLE ASSEMBLY,POWER-ON INDICATOR</PartDesc><PartType>E</PartType><PartPrice>9.36</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>FAB-07529</PartNumber><PartDesc>SHIM, SENSOR TILT</PartDesc><PartType>E</PartType><PartPrice>96</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70437-001</PartNumber><PartDesc>CABLE ASSY,ELEC ENCL ROBOT</PartDesc><PartType>E</PartType><PartPrice>52.6</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70435-001</PartNumber><PartDesc>CABLE ASSY,ELEC ENCL CPS 1-4,IS</PartDesc><PartType>E</PartType><PartPrice>89.6</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70976-001</PartNumber><PartDesc>CABLE ASSY, Y-STAGE GND, IEP</PartDesc><PartType>E</PartType><PartPrice>5.28</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>51274-001</PartNumber><PartDesc>ENCODER INTERFACE, PC BOARD</PartDesc><PartType>E</PartType><PartPrice>432</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>50227-008</PartNumber><PartDesc>CABLE CLAMP, FIXED DIA. HEAVY DUTY APP. #10SCREW</PartDesc><PartType>E</PartType><PartPrice>1</PartPrice><PartReturn>N</PartReturn><Reparable>No Return</Reparable></Entry><Entry><PartNumber>71006-001S</PartNumber><PartDesc>IMAGER MOTION CONTROL BOARD STACK SERVICE VERS</PartDesc><PartType>E</PartType><PartPrice>3968.7</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>71001-001</PartNumber><PartDesc>CASSETTE INTERFACE PLATE ASSEMBLY, IEP</PartDesc><PartType>E</PartType><PartPrice>2033.86</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>51890-001</PartNumber><PartDesc>FAN MOUNT, VIBRATION</PartDesc><PartType>E</PartType><PartPrice>1</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>03068-001</PartNumber><PartDesc>RETAINER,KEY SLOT</PartDesc><PartType>E</PartType><PartPrice>25.4</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>51092-004</PartNumber><PartDesc>SCREW,SHBH,BLK, #4-40 .250 L</PartDesc><PartType>E</PartType><PartPrice>1</PartPrice><PartReturn>N</PartReturn><Reparable>No Return</Reparable></Entry><Entry><PartNumber>74158-001-RFB</PartNumber><PartDesc>TS PART ASSY, ROBOT ASSY</PartDesc><PartType>E</PartType><PartPrice>6701.68</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>03305-001</PartNumber><PartDesc>PLATE,INTERFACE</PartDesc><PartType>E</PartType><PartPrice>646</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70443-001</PartNumber><PartDesc>CABLE ASSY,CASSETTE PRES SNSR #amp; LED IND,IS</PartDesc><PartType>E</PartType><PartPrice>554.04</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>51761-002</PartNumber><PartDesc>SWITCHBOX, MANUAL, 2 TO 1</PartDesc><PartType>E</PartType><PartPrice>60</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>51161-001</PartNumber><PartDesc>LENS,CONDENSER</PartDesc><PartType>E</PartType><PartPrice>775.8</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>71113-001</PartNumber><PartDesc>IEP CONDENSER ASSY</PartDesc><PartType>E</PartType><PartPrice>3750</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>51344-002</PartNumber><PartDesc>ACCELEROMETER, ICP, MINI LOW NOISE</PartDesc><PartType>E</PartType><PartPrice>657</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>51627-001</PartNumber><PartDesc>TISSUE, OPTICAL LENS CLEANER</PartDesc><PartType>E</PartType><PartPrice>1</PartPrice><PartReturn>N</PartReturn><Reparable>No Return</Reparable></Entry><Entry><PartNumber>03301-001</PartNumber><PartDesc>GUARD,CABLE GUIDE,SHORT</PartDesc><PartType>E</PartType><PartPrice>28</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>04127-001</PartNumber><PartDesc>GUARD, CABLE GUIDE, LONG</PartDesc><PartType>E</PartType><PartPrice>30.8</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>51067-010</PartNumber><PartDesc>SCREW,SHFH,SS,M3 X 10</PartDesc><PartType>E</PartType><PartPrice>1</PartPrice><PartReturn>N</PartReturn><Reparable>No Return</Reparable></Entry><Entry><PartNumber>RM-70524-001</PartNumber><PartDesc>REFURBISHED PCA,LED MODULE HOLDER</PartDesc><PartType>E</PartType><PartPrice>628</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>03303-001</PartNumber><PartDesc>SHIELD,ROBOT</PartDesc><PartType>E</PartType><PartPrice>114.2</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>03304-001</PartNumber><PartDesc>PANEL, LOWER VANITY</PartDesc><PartType>E</PartType><PartPrice>54.6</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>51390-004</PartNumber><PartDesc>SCREW,SHBH,SS,BLK PASS,#6-32</PartDesc><PartType>E</PartType><PartPrice>1</PartPrice><PartReturn>N</PartReturn><Reparable>No Return</Reparable></Entry><Entry><PartNumber>70426-001</PartNumber><PartDesc>CABLE ASSY,SIS FAN/FILTER PRES SW,IS</PartDesc><PartType>E</PartType><PartPrice>149.08</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>ASY-03568</PartNumber><PartDesc>BOARD,LIGHT CONTROL IM FOR REPLACEMENT LED</PartDesc><PartType>E</PartType><PartPrice>232</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>ASY-03569</PartNumber><PartDesc>PCA,LED MODULE HOLDER (REPLACEMENT LED)</PartDesc><PartType>E</PartType><PartPrice>80</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70549-001</PartNumber><PartDesc>CABLE ASSY, DELTA TAU PS EXTENSION</PartDesc><PartType>E</PartType><PartPrice>15.28</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70530-001</PartNumber><PartDesc>CABLE ASSY, DELTA TAU INTERFACE RIBBON</PartDesc><PartType>E</PartType><PartPrice>22.28</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>PWR-00142</PartNumber><PartDesc>UPS BATTERY PACK, 24V, 1500VA, RS/XS SERIES</PartDesc><PartType>E</PartType><PartPrice>381</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>03020-001</PartNumber><PartDesc>EFFECTOR, END, ROBOT</PartDesc><PartType>E</PartType><PartPrice>368</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>PWR-00140</PartNumber><PartDesc>UPS, 1500VA, 120VAC 50/60HZ, RS SERIES</PartDesc><PartType>E</PartType><PartPrice>588</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>PWR-00141</PartNumber><PartDesc>UPS, 1500VA, 230VAC 50/60HZ, RS SERIES</PartDesc><PartType>E</PartType><PartPrice>666</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>ASY-04635</PartNumber><PartDesc>KIT, SENSOR TILT RETROFIT</PartDesc><PartType>E</PartType><PartPrice>96</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70932-001</PartNumber><PartDesc>CABLE ASSY, SHEMP DC POWER, IEP,</PartDesc><PartType>E</PartType><PartPrice>26.28</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>51697-006</PartNumber><PartDesc>CABLE, CAT5E ETHERNET, 10FT</PartDesc><PartType>E</PartType><PartPrice>45</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70440-001</PartNumber><PartDesc>CABLE ASSY,OCR CAMERA,IS</PartDesc><PartType>E</PartType><PartPrice>55.36</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70937-001</PartNumber><PartDesc>CABLE ASSY,ELEC ENCL OCR CAMERA, IEP</PartDesc><PartType>E</PartType><PartPrice>59.2</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>51812-001</PartNumber><PartDesc>SERIAL ADAPTOR, DB9(F) RJ-11 (F)</PartDesc><PartType>E</PartType><PartPrice>54.92</PartPrice><PartReturn>N</PartReturn><Reparable>No Return</Reparable></Entry><Entry><PartNumber>50612-001</PartNumber><PartDesc>MODULE,POWER INPUT,SH, DPST,10A</PartDesc><PartType>E</PartType><PartPrice>55.68</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>51827-001</PartNumber><PartDesc>CABEL, PANEL MOUNT, FLATWIRE</PartDesc><PartType>E</PartType><PartPrice>47</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>04878-001</PartNumber><PartDesc>HUB DOOR PIVOT, THRU BOLT</PartDesc><PartType>E</PartType><PartPrice>43.6</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70550-001</PartNumber><PartDesc>ACCELEROMETER INTERFACE MODULE, IS ASSY.LEVEL 1</PartDesc><PartType>E</PartType><PartPrice>278.6</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70546-001</PartNumber><PartDesc>CABLE ASSY, TRIGGER LINES, IS</PartDesc><PartType>E</PartType><PartPrice>150.6</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>74158-001</PartNumber><PartDesc>TS PART ASSEMBLY, ROBOT ASSEMBLY</PartDesc><PartType>E</PartType><PartPrice>7090.78</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70659-001</PartNumber><PartDesc>PROGRAM ASSEMBLY,BOOT ROM,IS SHEMP</PartDesc><PartType>E</PartType><PartPrice>19</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>05381-001</PartNumber><PartDesc>DOOR CENTER ACESS</PartDesc><PartType>E</PartType><PartPrice>480</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>74109-001</PartNumber><PartDesc>TS ASSEMBLY,MOTOR,RADIAL AXIS ASSEMBLY</PartDesc><PartType>E</PartType><PartPrice>96.95</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70515-001</PartNumber><PartDesc>CABLE ASSY,SINGLE PHASE AMP,COMMUNICATION</PartDesc><PartType>E</PartType><PartPrice>57.72</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70942-001</PartNumber><PartDesc>CABLE ASSY,ELEC ENCL SHEMP J5 SENSOR INP.IEP</PartDesc><PartType>E</PartType><PartPrice>107.6</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>51140-006</PartNumber><PartDesc>SCREW, PHPH, SELF-TAP, #6-19, .375 #quot; LG</PartDesc><PartType>E</PartType><PartPrice>1</PartPrice><PartReturn>N</PartReturn><Reparable>No Return</Reparable></Entry><Entry><PartNumber>CMP-01011</PartNumber><PartDesc>SCANNER, OCR, JDK-1472, TP-3000</PartDesc><PartType>E</PartType><PartPrice>1336</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>CMP-00979</PartNumber><PartDesc>SCANNER, OCR, JDK-1471, IMAGER</PartDesc><PartType>E</PartType><PartPrice>1348</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>04175-001</PartNumber><PartDesc>ENCLOSURE, ELECTRICAL TOP</PartDesc><PartType>E</PartType><PartPrice>57.8</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>03538-001S</PartNumber><PartDesc>SCANNER OCR 4710 SERVICE VERSION</PartDesc><PartType>E</PartType><PartPrice>1558.08</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70525-001</PartNumber><PartDesc>CABLE ASSY, ELEC ENCL SHMP RS-232</PartDesc><PartType>E</PartType><PartPrice>46.32</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>50868-030</PartNumber><PartDesc>WASHER, FLAT, METRIC, SS M3</PartDesc><PartType>E</PartType><PartPrice>1</PartPrice><PartReturn>N</PartReturn><Reparable>No Return</Reparable></Entry></PartList><MsgTotal>3</MsgTotal><MsgNum>3</MsgNum></vmsg>
In my example XML data is coming from table. we can hardcode the string or we can change the highlighted select query as per your requirement.
WITH T AS
(SELECT msg_contents AS LIST
FROM HOLX_VETTRO_OB_QUEUE
WHERE 1 =1
AND MSG_TYPE ='PartsListResponse'
AND msg_sequence_id ='2146981'
)
select LTRIM(REGEXP_SUBSTR('<PartNumber>'
||list,'<PartNumber>[a-zA-Z0-9-]*',1,RN),'<PartNumber>') AS Part_Number
FROM T,
(SELECT rownum RN
FROM DUAL
CONNECT BY rownum <=
(SELECT LENGTH(REGEXP_REPLACE(list,'[^<PartNumber>]'))+1 FROM T
)
)
WHERE LTRIM(REGEXP_SUBSTR('<PartNumber>'
||list,'<PartNumber>[a-zA-Z0-9-]*',1,RN),'<PartNumber>') LIKE '%';
Altered select quesry and I ran this but Iam getting an error (single row subquery returns more than one row)
WITH T AS
(SELECT msg_contents AS LIST
FROM HOLX_VETTRO_OB_QUEUE
WHERE 1 =1
AND MSG_TYPE ='PartsListResponse'
AND mwid ='PKELLIHER'
AND TRUNC(creation_date)=TRUNC(sysdate)
AND msg_contents LIKE '%Model%70031%'
)
select LTRIM(REGEXP_SUBSTR('<PartNumber>'
||list,'<PartNumber>[a-zA-Z0-9-]*',1,RN),'<PartNumber>') AS PART_NUMBER
FROM T,
(SELECT rownum RN
FROM DUAL
CONNECT BY rownum <=
(SELECT LENGTH(REGEXP_REPLACE(list,'[^<PartNumber>]'))+1 FROM T
)
)
WHERE LTRIM(REGEXP_SUBSTR('<PartNumber>'
||list,'<PartNumber>[a-zA-Z0-9-]*',1,RN),'<PartNumber>') LIKE '%';
Just replaced highlated one with 100--. This query will work for at most 100 records , Increase the number as per your requirement.
WITH T AS
(SELECT msg_contents AS LIST
FROM HOLX_VETTRO_OB_QUEUE
WHERE 1 =1
AND MSG_TYPE ='PartsListResponse'
AND mwid ='PKELLIHER'
AND TRUNC(creation_date)=TRUNC(sysdate)
AND msg_contents LIKE '%Model%70031%'
)
SELECT LTRIM(REGEXP_SUBSTR('<PartNumber>'
||list,'<PartNumber>[a-zA-Z0-9-]*',1,RN),'<PartNumber>') AS PART_NUMBER
FROM T,
(SELECT rownum RN
FROM DUAL
CONNECT BY rownum <= 100 --(select length(REGEXP_REPLACE(list,'[^<PartNumber>]'))+1 from T)
)
WHERE LTRIM(REGEXP_SUBSTR('<PartNumber>'
||list,'<PartNumber>[a-zA-Z0-9-]*',1,RN),'<PartNumber>') LIKE '%';
/*********************************************************************/ /**********************************************************************/
/**********************************************************************/
/**********************************************************************/
The importnat thing to understand from the above scripts is
what sort of characters available in Part_number field
'<PartNumber>[a-zA-Z0-9-]*'
This implies part number will be having all numbers and small letters and capital letters and symbol '-'
No comments:
Post a Comment