stripping characters using SPL
Hi, I've a field abc in a table that hold values like 'HOME XXX' 'WATER XXX' Is there a way by using Stored procedure or SQL statement to stripped the XXX out of the values so that I can store them in a temp table like 'HOME' 'WATER' Your help is very much appreciated. Thanks Joshua
In article <885mgl$cnb$1@mango.singnet.com.sg>,
"jh" <joshho@singnet.com.sg> wrote:
> Hi,
>
> I've a field abc in a table that hold values like
>
> 'HOME XXX'
> 'WATER XXX'
>
> Is there a way by using Stored procedure or SQL statement to
stripped
> the XXX out of the values so that I can store them in a temp table
like
>
> 'HOME'
> 'WATER'
>
> Your help is very much appreciated.
>
> Thanks
>
> Joshua
>
>
Create this stored procedure:
-- ############################################################
create procedure sp_instr(v_string char(255),
v_find char(255))
returning smallint;-- #############################################################
-- recibiendo como parametros:
-- v_string: string el que se buscara
-- v_find : string que se buscara dentro del anterior
-- retorna la posicion en que se encuentra
define v_pos smallint; -- posicion
define v_len_find smallint; -- largo del string a ser buscado
define v_existe smallint; -- existe el substring?
--set debug file to "/tmp/mads.out";
let v_len_find = length(v_find);
if v_len_find = 0
then
let v_len_find = 1;
end if;
--trace "v_len_find " || v_len_find;
let v_existe = 0;
for v_pos = 1 to length(v_string)
--trace "substr " || substr(v_string, v_pos, v_len_find);
--trace "v_find " || v_find;
if substr(v_string, v_pos, v_len_find) = v_find
then
let v_existe = 1;
exit for;
end if;
end for;
if v_existe = 1
then
return v_pos;
end if;
return 0;
end procedure;
And then you can do:
select abc, substr(abc, 1, sp_instr(abc, " "))
from aaa
Sent via Deja.com http://www.deja.com/
Before you buy.
mdaponte@prtc.net пишет в сообщении <2522184520%88c9fk$rjp$1@nnrp1.deja.com> ... >In article <885mgl$cnb$1@mango.singnet.com.sg>, > "jh" <joshho@singnet.com.sg> wrote: >> Hi, >> >> I've a field abc in a table that hold values like >> >> 'HOME XXX' >> 'WATER XXX' >> >> Is there a way by using Stored procedure or SQL statement to >stripped >> the XXX out of the values so that I can store them in a temp table >like >> >> 'HOME' >> 'WATER' >> >> Your help is very much appreciated. >> >> Thanks >> >> Joshua >> In DBMS Linter : select substr(col_name,1,instr(col_name,' ')) from table; where function : substr- get part col_name from 1 to N instr - find first position of symbol