| Author |
Message
|
| scravr |
 Posted: Wed Apr 25, 2012 11:15 am Post subject: convert alphanumeric to int |
 |
|
Partisan
Joined: 03 Apr 2003
Posts: 391
Location: NY NY USA 10021
|
Does anyone have a quick magic way to convert ESQL alphanumeric CHAR to INT?
Something like:
DECLARE mychar CHAR ’abcABC123’;
DECLARE myint INT;
SET myint=SomeMagic(mychar);
Can BASE64ENCODE, BASE64DECODE, ASBITSTREAM, or other functions can be used? |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Wed Apr 25, 2012 11:27 am Post subject: Re: convert alphanumeric to int |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| scravr wrote: |
| Does anyone have a quick magic way to convert ESQL alphanumeric CHAR to INT? |
So is "onetwothreefour" a valid alphanumeric string that can be converted to an INT?
| scravr wrote: |
| Can BASE64ENCODE, BASE64DECODE, ASBITSTREAM, or other functions can be used? |
Do those functions have any bearing on your requirement?
have you thought about the actual problem you're looking to solve?
Is it the same as the problem of removing characters that do not represent valid numeric values from a string? Are there ESQL functions that help you alter strings? |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Apr 25, 2012 11:53 am Post subject: Re: convert alphanumeric to int |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| scravr wrote: |
Does anyone have a quick magic way to convert ESQL alphanumeric CHAR to INT?
Something like:
DECLARE mychar CHAR ’abcABC123’;
DECLARE myint INT;
SET myint=SomeMagic(mychar); |
So in this example you want 123? What about 'abcABC123xxx456'? Is that 123, 456 or 123456?
You can do it in 1 line of ESQL, if you're ingenious.
| scravr wrote: |
| Can BASE64ENCODE, BASE64DECODE, ASBITSTREAM, or other functions can be used? |
IHMO 3 functions that convert from 1 format to another with no transformational aspect are unlikely to be of assistance to you. I'm uncertain how you picked on these... 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| scravr |
| Posted: Wed Apr 25, 2012 12:42 pm Post subject: |
|
|
Partisan
Joined: 03 Apr 2003
Posts: 391
Location: NY NY USA 10021
|
I am trying to get a numeric value for an alphanumeric string.
Something like this java function:
Int HashValue = (long)0;
for ( int i = 0; i < CharString.length(); ++i )
{
char c = CharString.charAt(i);
HashValue = HashValue + (long)c;
} |
|
| Back to top |
|
|
| kimbert |
| Posted: Thu Apr 26, 2012 1:11 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| Try the CAST function. Please look up the details in the info center. |
|
| Back to top |
|
|
| McueMart |
| Posted: Thu Apr 26, 2012 1:38 am Post subject: |
|
|
Chevalier
Joined: 29 Nov 2011
Posts: 490
Location: UK...somewhere
|
Here's a quick attempt. Someone will be along shortly no doubt to do it in a single line!
| Code: |
DECLARE zeroPadBLOB CONSTANT BLOB X'00000000000000';
CREATE FUNCTION SomeMagic(IN mychar CHAR) RETURNS INT BEGIN
DECLARE count INTEGER 1;
DECLARE l INTEGER LENGTH(mychar);
DECLARE ret INTEGER 0;
WHILE count <= l DO
SET ret = ret + CAST(zeroPadBLOB || CAST(SUBSTRING(mychar FROM count FOR 1) AS BLOB CCSID 1208) AS INTEGER);
SET count = count+1;
END WHILE;
RETURN ret;
END;
|
p.s. It seems in ESQL an integer is represented with 8 bytes... that took me off guard; but makes sense seeing as there is no 'long' type. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Apr 26, 2012 6:22 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| McueMart wrote: |
| Someone will be along shortly no doubt to do it in a single line! |
Wow, you took the long way around to get there. There's an example floating around somewhere in the archives using translate. or maybe it was replace? |
|
| Back to top |
|
|
| Esa |
| Posted: Thu Apr 26, 2012 6:39 am Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
| mqjeff wrote: |
| McueMart wrote: |
| Someone will be along shortly no doubt to do it in a single line! |
Wow, you took the long way around to get there. There's an example floating around somewhere in the archives using translate. or maybe it was replace? |
No, the code is creating a numeric hashcode by summing up hexadecimal values of all characters in a string. I don't think you can do it with translate or replace.
The value will not be unique, because 'ABCabc123' will produce the same number as '321cbaCBA' and any other combination of the same characters. But perhaps uniqueness is not required here... |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Apr 26, 2012 6:49 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Esa wrote: |
| No, the code is creating a numeric hashcode by summing up hexadecimal values of all characters in a string. |
oh yeah, lookitthat. 
ret is an INTEGER, not a CHAR. |
|
| Back to top |
|
|
| McueMart |
| Posted: Thu Apr 26, 2012 6:52 am Post subject: |
|
|
Chevalier
Joined: 29 Nov 2011
Posts: 490
Location: UK...somewhere
|
| Yes i was struggling to see how it could be done with translate (and trying to search,unsuccessfully, for what mqjeff was referring to). Agree with Esa that this is a poor method of 'hashing' a character string. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Apr 26, 2012 6:59 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
There's an example somewhere that uses one of the string functions to remove everything that's a legitimate integer character, 0-9 from a string, and then uses the result as the input to remove everything left over from the original string.
You're then left with a string that only holds characters that represent 0-9 values, which you can cast to an integer. |
|
| Back to top |
|
|
| Esa |
| Posted: Thu Apr 26, 2012 7:11 am Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
| I think you can make the hashcode more unique for example by modifying the function so that it produces a temporary sum of every ten characters, then calculates modulus 11 of it, multiplies it with 1000 * (10 exp n) where n is incremented after each group, and adds that to the hashcode. You just need to take care of stopping before ESQL integer flows over. |
|
| Back to top |
|
|
| scravr |
| Posted: Thu Apr 26, 2012 8:55 am Post subject: |
|
|
Partisan
Joined: 03 Apr 2003
Posts: 391
Location: NY NY USA 10021
|
you all are getting very close.
I need a function returning a unique INT for a given STRING (with all in it; not only the numeric chars).
So
when calling func with "abc123", I need return int of (let's say) 500.
when calling func with "xyz", I need return int of (let's say) 800.
when calling func with "a1", I need return int of (let's say) 400.
when calling func with "xyz", I need the same retun int of 800. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Thu Apr 26, 2012 9:30 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
|
| Back to top |
|
|
| kimbert |
| Posted: Thu Apr 26, 2012 12:14 pm Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| Quote: |
| I need a function returning a unique INT for a given STRING (with all in it; not only the numeric chars). |
There's a name for that. It's called a 'perfect hash'. See http://en.wikipedia.org/wiki/Perfect_hash_function
I hope you know the full set of strings that you want to hash - otherwise it is going to be tricky to come up with a perfect hash function 
There must be loads of implementations of string hashing functions out there in the world of Java. If you require a *perfect* hash, then I suspect it's going to be tricky doing it in ESQL. A rough-and-ready non-perfect hash is easy, and you've already been given a couple of ideas for those. There are others, if you Google for them. |
|
| Back to top |
|
|
|
|
