| Author |
Message
|
| Ramphart |
 Posted: Fri Sep 10, 2004 1:59 am Post subject: How to get next alphabet char ? |
 |
|
Disciple
Joined: 21 Jul 2004
Posts: 150
Location: South Africa, JHB
|
I'm looking for a function to give me the next char in the alphabet
e.g. function('A') must return 'B'
e.g. function('X') must return 'Y'
e.g. function('Z') must return 'A'
Is there a builtin SQL function that can do this? If not, any suggestion on how i write one to easily achieve this?
This is what I do for now:
IF value = 'A' THEN SET value = 'B'; ELSE IF
value = 'B' THEN SET value = 'C'; ELSE IF
value = 'C' THEN SET value = 'D; ELSE IF
--and so on ...
value = 'Y' THEN SET value = 'Z'; ELSE IF
value = 'Z' THEN SET value = 'A';
END IF; END IF;
END IF; END IF;
END IF;
Ramo
_________________
Applications Architect |
|
| Back to top |
|
 |
| romudd |
| Posted: Fri Sep 10, 2004 5:16 am Post subject: |
|
|
Apprentice
Joined: 12 Aug 2003
Posts: 31
Location: Sao Paulo - Brazil
|
This may be done by incrementing the ASCII code for the letter. You can define a function for this. I made an example, improve it as you wish:
| Quote: |
create function your_sql_user.NEXTLETTER ( @inputchar char(1) )
returns char(1)
as
BEGIN
declare @asciicode int
-- Gets the ASCII code for the character
set @asciicode = ASCII(@inputchar)
-- According to the ASCII table, these values are not letters. Return 0
IF (@asciicode < 65) or (@asciicode > 90 and @asciicode < 97) or (@asciicode > 122)
return '0'
-- If it's 'Z' set to the value before 'A'
IF (@asciicode = 90)
set @asciicode = 64
-- If it's 'z' set to the value before 'a'
IF (@asciicode = 122)
set @asciicode = 96
-- Increment the ASCII code and convert it back to character
return CHAR(@asciicode + 1)
END |
It works on Microsoft SQLServer, but probably the script can be changed for using with other databases, or any programming language |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri Sep 10, 2004 7:13 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| romudd wrote: |
| This may be done by incrementing the ASCII code for the letter. |
ESQL doesn't have an ASCII code function.
The translate function might be useful. This is tested and seems to work...
| Code: |
| translate(inputValue,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ','bcdefghijklmnopqrstuvwxyzaBCDEFGHIJKLMNOPQRSTUVWXYZA'); |
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| Ramphart |
| Posted: Sun Sep 12, 2004 11:25 pm Post subject: |
|
|
Disciple
Joined: 21 Jul 2004
Posts: 150
Location: South Africa, JHB
|
Hi Jeff,
Is the translate function available in WMQI 2.1 CSD 07 on Windows 2000? I can't seem to find any documentation on it.
Regards.
_________________
Applications Architect |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon Sep 13, 2004 4:37 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| Ramphart wrote: |
| Is the translate function available in WMQI 2.1 CSD 07 on Windows 2000? I can't seem to find any documentation on it. |
No, I don't think so.
I'm fairly sure it's a v5 only function.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| Galichet |
| Posted: Mon Sep 13, 2004 5:05 am Post subject: |
|
|
Acolyte
Joined: 26 Jun 2001
Posts: 69
Location: Paris - France
|
Hi,
You can try the following proposition :
Create a Table like this
---------------------------
CREATE TABLE TABLECONVERT
("TABIN" VARCHAR(255) ,
"TABOUT" VARCHAR(255),
"PAD" CHAR(1) NOT NULL
);
INSERT INTO TABLECONVERT VALUES(
'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
'0123456789bcdefghijklmnopqrstuvwxyzaBCDEFGHIJKLMNOPQRSTUVWXYZA',
' ') ;
In a compute node
----------------------
(Assuming that SourceValue contains the field you want to translate)
DECLARE REQ_CONV CHARACTER;
SET REQ_CONV ='select translate(cast(? as varchar(255)), tabout, tabin, pad) as TEMP from TableConvert ';
SET OutputLocalEnvironment.Variables.TargetValue[] = PASSTHRU(REQ_CONV, SourceValue));
I think it should work ... Each character in your SourceValue will be translated according to the table. If a character is not found a space will be used. Beware if you have characters with accents like éèà î ... you have to include them too in the table
I hope this can help you
_________________
Eric Galichet
SMABTP
France |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon Sep 13, 2004 5:09 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Or you could use a combination of substring and Position on the formed strings 'abcde..', etc, to avoid using a database table and a fairly expensive passthru.
Something like (when I use the phrase "something like", it means "untested code") - substring('bcdef...' from position(inputvalue in 'abcd...') for 1)
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| Ramphart |
| Posted: Mon Sep 13, 2004 5:43 am Post subject: |
|
|
Disciple
Joined: 21 Jul 2004
Posts: 150
Location: South Africa, JHB
|
Hi Jeff,
Substring and postion is a great idea.
This is what i've done:
/*********************************************************************
** FUNCTION NextChar
**********************************************************************/
CREATE FUNCTION NextChar (value CHAR) RETURNS CHAR
BEGIN
DIM strRange CHAR;
SET strRange = 'ABCDEFGHIJKLMNOPQRSTUVWXYZA';
SET value = UCASE(value);
SET value = SUBSTRING(strRange FROM POSITION(value IN strRange) + 1 FOR 1);
RETURN value;
END;
Thx man
_________________
Applications Architect |
|
| Back to top |
|
|
|
|
