RE: How to delete last char in string?
John wrote:
>Here is an SQL puzzle for everyone:
> We need to trim the last character from a variable length parameter
>that is passed into an SPL stored procedure. We have tried setting
>a variable len as:
> let len = length(v_parm_name) - 1;
> let v_short_parm_name = v_parm_name[1, len];
>On the second line we get a syntax error on len. I have run into the
>same problem with a regular SQL statement such as:
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
If you have to use an SPL procedure to remove the character from the end
of the string, the only way I have found you can do it is:
create procedure a(str char(10)
returning varchar(10);
define nstr varchar(10);
define x integer;
let x = length(str);
if( x = 2 ) then
let nstr = str1(str);
elif( x = 3 ) then
let nstr = str2(str);
elif( x = 4 ) then
let nstr = str3(str);
elif( x = 5 ) then
let nstr = str4(str);
else
let nstr = "";
end if
return nstr;
end procedure;
create procedure str1(ch1 char(1))
returning char(1); return ch1;
end procedure;
create procedure str2(ch2 char(2))
returning char(2); return ch2;
end procedure;
Horrible, but it works. Only SPL solution I could think of. Not much use for
large strings.
>select col1[1, LENGTH(col2)] from table1;
> Any ideas on how to solve this problem? If you can think of any way to
>do, please send your ideas. We could write this in SQL, SPL, 4GL, etc. or
>if we had to, call a C program.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
If you can use 4GL to strip the last char before calling the SPL routine its
much easier. 9This is probably not what you are indicating you are able
to do anyhow).
DEFINE nstr char(50);
PREPARE astmt FROM "execute yourproc(?)"
LET idx = length(yourstr)-1
LET nstr = yourstr[1,idx]
EXECUTE astmt USING nstr
Mark Denham
BBC
London, UK
Mark.Denham@bbc.co.uk