| Author |
Message
|
| mqwbiwf |
 Posted: Fri Jan 25, 2008 1:13 pm Post subject: Alphanumeric string - ESQL function |
 |
|
Centurion
Joined: 21 Jul 2006
Posts: 126
|
hi, i was trying to figure out a way to extract the numeric value(eg: 12345) and alphabets (eg: abc) from a variable length alphanumeric string(eg: abc12345). do we have a esql funciton which can be used to do so? i tried to search but couldn't find one.
if not, can someone tell me any other best possible way to do so. appreciate ur help guys... |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Fri Jan 25, 2008 1:58 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Is the "mix" in a known format? I mean, is it always three letters followed by two numbers, or etc?
Otherwise you can look at OVERLAY or REPLACE on copies of a string, to replace all the 0123456789's with nothing, or all the letters instead.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| mqwbiwf |
| Posted: Fri Jan 25, 2008 2:02 pm Post subject: |
|
|
Centurion
Joined: 21 Jul 2006
Posts: 126
|
| no, it's a variable length string. can be any number of chars followed by numbers. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri Jan 25, 2008 2:09 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Is it always "some unknown number of letters, followed by some unknown number of numbers"?
Or is it "some unknown number of letters mixed randomly with some unknown number of numbers"?
If there's any kind of "structure" at all, you should at a minimum be able to use POSITION and SUBSTRING.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| mqwbiwf |
| Posted: Fri Jan 25, 2008 2:12 pm Post subject: |
|
|
Centurion
Joined: 21 Jul 2006
Posts: 126
|
it's always :
"some unknown number of letters, followed by some unknown number of numbers"?
for eg: can be ab123 or a12 or abc12345 etc.
which makes it difficult to use the POSITION/SUBSTRING functions. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri Jan 25, 2008 2:27 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
It doesn't make it difficult for the POSITION/SUBSTRING approach.
You might have to write 10 positions, but that's not "difficult". Ugly, perhaps.
Or, again, TRANSLATE/TRIM. (I know I said OVERLAY/REPLACE last time.... I meant Translate.)
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
|
|
