-1

I'm looking around to encode/cast/convert a string into numbers within a query. Like ASCII() but it only returns the left-most character to its relative code. Is there any function or method available on this topic? -which is actually decode-able

JUST For example:

METHOD("test-string") # Outputs: 25478596325417
revo
  • 47,783
  • 14
  • 74
  • 117
  • Please give some examples of what you are trying to do and what you are trying to accomplish. Are you looking for a loss-less (reversible) encoding? Are you looking for numbers of a specific length? Is there a particular set of values that you want to convert? what do you want to do with the numbers after conversion? – Gordon Linoff Jul 06 '14 at 20:38
  • See related question http://stackoverflow.com/questions/5960620/convert-text-into-number-in-mysql-query. – Mihai8 Jul 06 '14 at 20:39
  • http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html#function_cast – Rachel Gallen Jul 06 '14 at 20:39
  • @GordonLinoff I want to convert a set of sequential characters - *strings actually* - to numbers which I can decode them later. Most likely their ASCII codes. Example added – revo Jul 06 '14 at 20:42
  • @user1929959 I saw it before. It doesn't fit my case. – revo Jul 06 '14 at 20:46
  • @RachelGallen That doesn't result in what I wanted. – revo Jul 06 '14 at 20:46
  • How does "test-string" compute to 25478596325417? – Simon Jul 06 '14 at 20:56
  • @Simon That's an imaginary example, man! – revo Jul 06 '14 at 21:03
  • What are you going to do with this number? Are you going to store it as a number? That will put a limit on how big it can be. – Hogan Jul 06 '14 at 21:42
  • @Hogan I'm going to store it like that but strictly not Integer or other numeric types. – revo Jul 08 '14 at 20:08

4 Answers4

2

This will work for strings up to 8 characters long.

To encode:

SELECT CONV(HEX(string), 16, 10);

To decode:

SELECT UNHEX(CONV(number, 10, 16));

MySQL supports integers up to 64 bit long, and this method uses 8 bits per character. Therefore using this method you can store up to 64 / 8 = 8 characters in an integer.

pdw
  • 8,359
  • 2
  • 29
  • 41
1

If hexadecimal is good enough for your application, then then function hex() does what you want. For instance, you can try:

select hex('abc'), hex('abcd')

This will work on arbitrary strings. If this doesn't quite work, then perhaps there is a way to convert the hex representation to something appropriate.

By the way, unhex() will return the original string.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • But HEX may include A to F numbers. *I need Integers only!* – revo Jul 06 '14 at 21:07
  • @revo What's the greatest length your string may have? – VMai Jul 06 '14 at 21:22
  • hmmmm maybe 20 and yet it's a guess. – revo Jul 06 '14 at 21:26
  • @revo Have a look at the range of the [INTEGER](http://dev.mysql.com/doc/refman/5.6/en/integer-types.html) and [DECIMAL](http://dev.mysql.com/doc/refman/5.6/en/fixed-point-types.html) data types. Won't you rather convert to a string? – VMai Jul 06 '14 at 21:30
  • Sorry, I think I made you misunderstood things. I meant numeric values by "Integers". – revo Jul 06 '14 at 21:32
0

Try this one.

SELECT CAST(HEX(your_string) AS DECIMAL);

I admit, I didn't test it, but it should work.

EDIT:

Some other databases (e.g. Oracle, DB2, PostgreSQL) have the function TRANSLATE() for it. Unfortunately MySQL does not support it. And as far as I know no replacement for this function in MySQL exists currently. So using nested REPLACE() is probably the only option currently.

DarkSide
  • 3,670
  • 1
  • 26
  • 34
  • @pdw `CAST(HEX("mmm") AS DECIMAL)` outputs `6`. How you can decode `6` to `mmm` then? – revo Jul 06 '14 at 21:16
  • it has it's own limitations :( See my edit above. Nested REPLACE is the only way to support strings of any length. – DarkSide Jul 07 '14 at 21:33
0

You could use 

 COMPRESS('ABC)

To get a binary string that is not the string. It can compress an arbitrary size. But it is not clear what you are going to do with the number -- or how you need to store it.

Hogan
  • 69,564
  • 10
  • 76
  • 117
  • `COMPRESS` output isn't only numeric values. I need a conversion to numbers and have this ability to decode the main string from those numbers. See others answer. Thanks – revo Jul 06 '14 at 21:50