Most all SQL code is transactional - thus acts on Large sets of - and NOT Single rec's! I believe it's the Cursor related command. Thus U can work a Single record at a time & control Ur data manipulations down to the record And Field level - which may B what U need...

-K-

Sent from my iPhone

On Nov 17, 2017, at 6:06 PM, Matt Wiedeman Matt.Wiedeman@nahealth.com wrote:

Hi everyone,

I am working on a project where I need to insert SAP data into a vendor SQL Server database. To do so, my script will need to read a pipe delimited file down, do lookups in multiple other tables, and insert new rows into three tables. Most of the data I need uses normal tables with a key so it is pretty easy. However, one of the configuration tables needed uses a field that has an image field type. The field contains hex code. The hex string in each is over 8000 characters. I can't find a good way to parse this field out, though. Somehow, when the application users make changes or add a new record, it stores it in hex format which seems crazy but I confirmed this with the vendor. I can only guess that other languages do this more easily than TSQL. I have tried many variations of convert or cast to do this but am coming up empty.

This websitehttps://www.csoft.co.uk/developer/support/hex-converter does an ok job of converting the string but it has a bunch of garbage in it. So, using this listhttp://www.theasciicode.com.ar/ascii-table-characters.pdf, I created a little FoxPro program to read two characters at a time and store this to a variable. I could duplicate this using TSQL if it worked. Anyway, doing this is a little better than the converter sites but still not really useable. There are codes like: NULL, BEL, STX, ACK, SOH, STX, EOT, BS, HT, etc. that I'm not sure what to do with. I tried putting a space or dash in for these but that does not help. I would like to parse this hex string into a new table.

Any suggestions?

--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html

---

[excessive quoting removed by server]

OTTOMH - If you are just inserting fields from other existing tables why get involved in converting stuff - why not just pick the fields up as is - e.g.: INSERT INTO TableNew  (PriKey, flda, fldb)        SELECT ForeignKey, fldx, fldb        FROM SomeView;

or possibly:

SELECT ForeignKey, fldx, fldb INTO TableNew FROM SomeTblView tv WHERE tv.ForeignKey = m.lookupkey

On 18-Nov-2017 4:36 AM, Matt Wiedeman wrote:

Hi everyone,

I am working on a project where I need to insert SAP data into a vendor SQL Server database. To do so, my script will need to read a pipe delimited file down, do lookups in multiple other tables, and insert new rows into three tables. Most of the data I need uses normal tables with a key so it is pretty easy. However, one of the configuration tables needed uses a field that has an image field type. The field contains hex code. The hex string in each is over 8000 characters. I can't find a good way to parse this field out, though. Somehow, when the application users make changes or add a new record, it stores it in hex format which seems crazy but I confirmed this with the vendor. I can only guess that other languages do this more easily than TSQL. I have tried many variations of convert or cast to do this but am coming up empty.

This websitehttps://www.csoft.co.uk/developer/support/hex-converter does an ok job of converting the string but it has a bunch of garbage in it. So, using this listhttp://www.theasciicode.com.ar/ascii-table-characters.pdf, I created a little FoxPro program to read two characters at a time and store this to a variable. I could duplicate this using TSQL if it worked. Anyway, doing this is a little better than the converter sites but still not really useable. There are codes like: NULL, BEL, STX, ACK, SOH, STX, EOT, BS, HT, etc. that I'm not sure what to do with. I tried putting a space or dash in for these but that does not help. I would like to parse this hex string into a new table.

Any suggestions?

--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html

---

[excessive quoting removed by server]