0

Long story short: I need to find the rules for type conversion from string to integer in MariaDB.

My case: I have a VARCHAR column in a table that can have numeric values (1,2,3), numeric values padded with zeroes (01, 02, 03) and some non numeric values. I need to compare the numeric values as integers, but can't find sufficient documentation about the workings of CAST or CONVERT functions. It is not at all obvious to me that for example non numeric values should be always converted to 0 as it now seems to work.

Arkay
  • 1
  • 1
  • Show the code you are using. It depends on how the conversion is being done. – Gordon Linoff Apr 07 '21 at 12:51
  • 1
    I'm explicitly using CAST like `CAST(my_column AS INTEGER)` – Arkay Apr 07 '21 at 12:54
  • For things like `'5'` or `'005'`, casting to integer as suggested above should not be a problem. – Tim Biegeleisen Apr 07 '21 at 12:57
  • So after casting to integer, strings like `5`, `005` and `0000000005` are all just `5`? Maybe I'm weird, but I would really like to see the official documentation about this behavior. – Arkay Apr 07 '21 at 13:02
  • @Arkay If your real underlying question is "what is the full behaving of string to integer casting," then your question is too broad. Maybe update and show us all the character data which might be getting cast, and someone can comment on it. – Tim Biegeleisen Apr 07 '21 at 13:04
  • Well that actually is the real underlying question. Maybe this is the wrong place to ask, but I have been looking for the official documentation about converting strings to integers, but I can't find it. I thought someone here must know how to find this documentation. How can I even make a reliable program if this documentation doesn't exist. – Arkay Apr 07 '21 at 13:11
  • That being said, my data is week numbers, so between 1 and 53 i guess. I just need to find how to compare these. The problem is that sometimes week numbers between 1 and 9 are padded with a preceding zero, like 01, 09. Then again, the column also contains totally non numeric values, which aren't even completely documented. Some of these are fine to be converted to 0 for my purposes, some not. Yeah, I'm dealing with a real legacy system... – Arkay Apr 07 '21 at 13:22
  • >So after casting to integer, strings like 5, 005 and 0000000005 are all just 5? What other possibilities are there? This is not an issue with CAST https://mariadb.com/kb/en/cast/ but basic mathematics. 005 to an integer is 5. Right? Are you expecting 005? – Jeff Mergler Apr 07 '21 at 18:04
  • The behavior of 05, 005 and 000000005 being cast as 5 is what I would expect to happen, yes. My problem is that this is still just my assumption and my experience in programming tells me, that often times things don't work at all the way I would assume. There can be all kinds of weird exceptions and behavior that isn't actually intended and could change in the future. Are all non numeric strings cast always to 0 or could there be a NULL result or an exception? There is no way of making a reliable program without clearly documented behavior about things like these. – Arkay Apr 08 '21 at 06:45
  • Some strings that I would consider non numeric could be some obscure format that MariaDB recognizes... Things like these have happened to me. – Arkay Apr 08 '21 at 06:48

0 Answers0