I have a field with a long string that I'd like to grab several substrings from. Grabbing one substring works fine, using a couple of SUBSTRING_INDEX commands to knock the front and the back off. And I believe I should be able to several queries in a function or procedure to do what I want. However... I only have read access to the database (it's a Dell KACE K1000). Is it possible to use a function or procedure to do what I want without using stored procedures or UDFs?
Specifically, here is my current "test" query with an example string:
SUBSTRING_INDEX(SUBSTRING_INDEX(s, "* USB", 1), "<br/>", -1)
FROM (SELECT '8/13/2012 9:04:09 AM - Logged in user: bcarter<br/>------------------------------------------------------<br/>Santa Fe Lanier LD145 * IP_192.168.4.253 * LANIER LD145 PCL 6 * <br/>Microsoft XPS Document Writer * XPSPort: * Microsoft XPS Document Writer * <br/>Microsoft Office Document Image Writer * Microsoft Document Imaging Writer Port: * Microsoft Office Document Image Writer Driver * <br/>HP Officejet 6500 E710n-z * USB002 * HP Officejet 6500 E710n-z * <br/>HP Deskjet 6940 series * USB001 * HP Deskjet 6940 series * <br/>Fax - HP Officejet 6500 E710n-z * USB003 * Fax - HP Officejet 6500 E710n-z * <br/>Fax * SHRFAX: * Microsoft Shared Fax Driver * <br/>Adobe PDF * My Documents\*.pdf * Adobe PDF Converter * My Documents<br/><br/>' AS s) temp
This outputs the first USB printer name, as desired:
HP Officejet 6500 E710n-z
I believe I could grab all of the USB printers by incrementing the count used in the inner SUBSTRING_INDEX, for example:
SUBSTRING_INDEX(SUBSTRING_INDEX(s, "* USB, 2) ...
SUBSTRING_INDEX(SUBSTRING_INDEX(s, "* USB, 3) ...
SUBSTRING_INDEX(SUBSTRING_INDEX(s, "* USB, 4) ...
But I'm not sure how to:
1) Return the results to a single field via CONCAT, with commas ( ,) in between each result
2) Drop the results if they are blank/null or if the string contains "fax" (Fax, FAX, etc)
3) Expand this to also search for "lpt" and "dot" (so there might be 3 different functions or routines, unless variables could be used?)
I believe I'd have to use a function with a DO... WHILE loop, but again I'm not sure if this would be feasible while only have read access to the database.
Just for reference, here is the query I'm currently using to pull the first match (using the table/column names, not an example string):
SELECT M.NAME as 'Machine',
SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, '*', 1), "<br/>", -1) as 'Local Printer'
FROM MACHINE_CUSTOM_INVENTORY MCI
JOIN MACHINE M on (M.ID = MCI.ID)
WHERE MCI.ID=M.ID and MCI.SOFTWARE_ID = 6913
AND MCI.STR_FIELD_VALUE rlike 'usb|dot|lpt'
ORDER BY Machine
Any advise or suggestions would be appreciated, full points to anyone who can possibly get this working with my constraints.
Thanks very much!!!