DB2... stored procedures?

[Donato]

Hi there,
I am pretty new to databases and DB2 in particular. I have this problem: there is an already populated db and I need to perform a certain operation on each reacord of a table. The operation consists in modifying the content of a VARCHAR() column, basically to remove some spaces in the records.

For example, suppose this is the table:

-----------------------------
| FULL_NAME      | AGE .....   
-----------------------------
|  Bobby   Sans  | 34
-----------------------------
|  Ted   Spencer | 23
-----------------------------
|  John  Turner  | 50
-----------------------------

Now, what I want is to remove the leading spaces from the the FULL_NAME columns and also to remove exceeding spaces between the name and surname.

Of course, I know how to do it in Java, but I want to know if I can do it in a more performant way using something like stored-procedures.

Please, point to any relevant page/tutorial/example.

Thank you :-)

[ctp0102]

Hi,

To just retrieve the formatted values you could use:

select
case
when posstr(trim(FUL L_NAME), ' ') = 0 then trim(FULL_NAME) -- less than 2 names in column
else substr(trim(FUL L_NAME),1, posstr(trim(FUL L_NAME), ' ')) || trim(substr(tri m(FULL_NAME), posstr(trim(FUL L_NAME), ' ')))
end
FROM test_table;

To update the values in situ you could use:

update test_table
set FULL_NAME =
case
when posstr(trim(FUL L_NAME), ' ') = 0 then trim(FULL_NAME) -- less than 2 names in column
else substr(trim(FUL L_NAME),1, posstr(trim(FUL L_NAME), ' ')) || trim(substr(tri m(FULL_NAME), posstr(trim(FUL L_NAME), ' ')))
end

N.B. both these solutions only work where there is a amximum of 2 names in the column