Invisible data in MySQL

[DavJans]

I've been trying to figure this one out for a while now but thought maybe Id be able to find the answer here.

When I query data from another program we use I get a lot of invisible character's in front of the actual data.

with livecode I can copy one character worth of this invisible data and then use a replace " <invisible> " with ""

Curiously I can not paste the copied character outside of livecode, can't even search google for it.

but I cant use things like select * from table where column = '1'

I have to use Like %1% but this also gives me 11 and 12 and 111 and so on.

Has anyone else had any experience with something like this?

BTW Select HEX(column) gives me this --> 01010101010101010101010101010101010101010132313530 for example, that's only 1 record.

[AxWald]

Hi,

CharToNum(MyInvisibleChar) or
ByteToNum(MyInvisibleChar) maybe?

Is the invisible data always the same? Maybe some filler to get fixed-lenth fields? Then you could use:

Code: Select all

char offset(NumToChar(MyInvisibleCharAsNum))+1 to len(MyData) of MyData

to get the start of the real data received, and fill again with a repeat:

Code: Select all

repeat for (MyDesiredFieldLength - len(MyData))
     put NumToChar(MyInvisibleCharAsNum) before MyData
end repeat

for outgoing data?

Have fun!

Edit: This is untested pseudo code and will eat your machine if used without consideration!

[DavJans]

Thank you.

I was able to get the answer from the company that created the database.

"Those are padding characters that are inserted into the main mark, piece mark, drawing #, sequence, and lot # fields in order to achieve the desired sorting behavior depending on the input type settings. For a query like that we need to join to the ProductionControlJobs table to get the settings and then use the _FS_MARK_STR function (a user-defined function we install in the database) to get the plain-text value."

[AxWald]

Hi,

"Those are padding characters that are inserted [...] in order to achieve the desired sorting behavior depending on the input type settings. For a query like that we need to join to the ProductionControlJobs table to get the settings and then use the _FS_MARK_STR function (a user-defined function we install in the database) to get the plain-text value."

Spooky! I smell primeval evil, unspeakable atrocities from times long forgotten - the foul odor of dBase! Where's the garlic, the crucifix and the silver bullets?

Well, sometimes we have to swallow such toads. Life ain't easy.
It's up to you now to decide if to use their way, or to analyze the data to find out if you can handle it yourself.
I'd rather use the second way if possible, working with the raw data & avoiding any hidden traps that may lurk in the code of such coders - provided you only need to READ the data.

If you need to WRITE, it's always good custom not to do it without consent from the other party, and to do it in way they approve. (This can save you a lot of trouble!)

Have fun,
and should there be more questions, feel free to ask!