473,320 Members | 2,202 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

DB2... stored procedures?

1
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:

Expand|Select|Wrap|Line Numbers
  1. -----------------------------
  2. | FULL_NAME      | AGE .....   
  3. -----------------------------
  4. |  Bobby   Sans  | 34
  5. -----------------------------
  6. |  Ted   Spencer | 23
  7. -----------------------------
  8. |  John  Turner  | 50
  9. -----------------------------
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 :-)
Aug 4 '10 #1
1 2069
Hi,

To just retrieve the formatted values you could use:

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

To update the values in situ you could use:

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

N.B. both these solutions only work where there is a amximum of 2 names in the column
Aug 31 '10 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: M Wells | last post by:
Hi All, I'm wondering if anyone can tell me if it's possible to search for stored procedures by their contents? I tend to leave very descriptive notes in stored procedures and they're...
17
by: serge | last post by:
How can i delete all user stored procedures and all table triggers very fast in a single database? Thank you
11
by: jrefactors | last post by:
I want to know the differences between SQL Server 2000 stored procedures and oracle stored procedures? Do they have different syntax? The concept should be the same that the stored procedures...
2
by: scott | last post by:
Hi, Just wondering what sort of problems and advantages people have found using stored procedures. I have an app developed in VB6 & VB.NET and our developers are starting to re-write some of the...
2
by: Kent Lewandowski | last post by:
hi all, Recently I wrote some stored procedures using java jdbc code (admittedly my first stab) and then tried to implement the same within java packages (for code reuse). I encountered...
5
by: Rhino | last post by:
I am trying to determine the behaviour of stored procedures in DB2 V8.2.x in Windows/Unix/Linux and how I can control that behaviour. Some documentation in the manuals is confusing the issue...
5
by: Tim Marshall | last post by:
I was following the thread "Re: Access Treeview - Is it Safe Yet?" with interest and on reading the post describing Lauren Quantrell's SmartTree, I've run into something I don't understand: Stored...
0
by: Amber | last post by:
Stored procedures are faster and more efficient than in-line SQL statements. In this article we will look at two SQL Server stored procedures; one using an input parameter and one not, and see how...
45
by: John | last post by:
Hi When developing vb.bet winform apps bound to sql server datasource, is it preferable to use SELECTs or stored procedure to read and write data from/to SQL Server? Why? Thanks Regards
28
by: mooreit | last post by:
The purpose for my questions is accessing these technologies from applications. I develop both applications and databases. Working with Microsoft C#.NET and Microsoft SQL Server 2000 Production and...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.