Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

update record in sentence case

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
jeeva84

New User


Joined: 18 Apr 2008
Posts: 7
Location: India

PostPosted: Mon Jun 02, 2008 3:16 pm    Post subject: update record in sentence case
Reply with quote

Hi,

I want a query to update all the record in a column in the following manner.

Input:
Column Name : City
Records:
NEW YORK
FLORIDA
ST. THOMAS
MIAMI-DADE


Requried output:
Column Mane : City
Records :
New York
Florida
St. Thomas
Miami-Dade
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Jun 02, 2008 4:33 pm    Post subject:
Reply with quote

use this ...

Code:


UPDATE INPTABLE A SET CITY =
(SELECT LCASE(CITY) FROM INPTABLE B WHERE A.KEY = B.KEY);



this is a correlated full select being used for update .....
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Jun 02, 2008 4:55 pm    Post subject:
Reply with quote

ashimer,

i don't understand how LCASE(CITY) will result in Miami, South Dakota, Miami-Dade?
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Jun 02, 2008 5:00 pm    Post subject:
Reply with quote

You are right Dick ..i didnt notice that ...just took it to be updated using lower case ... thanks for the correction ...

here i guess you cannot do anything using a single query but will have to fetch each record and then manipulate the records and then update ...
Back to top
View user's profile Send private message
jeeva84

New User


Joined: 18 Apr 2008
Posts: 7
Location: India

PostPosted: Mon Jun 02, 2008 5:31 pm    Post subject:
Reply with quote

ok thnx Ashimer and Dick.
Is their any other way to achive this.
Back to top
View user's profile Send private message
nabarundas

New User


Joined: 21 Jun 2007
Posts: 28
Location: pune

PostPosted: Mon Jun 02, 2008 5:49 pm    Post subject: check this
Reply with quote

hi,

Try this:

SELECT CONCAT(SUBSTR(city,1,1),LCASE(SUBSTR(city,2)))
FROM <TableName>


Regards,
Nabarun
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Jun 02, 2008 6:01 pm    Post subject:
Reply with quote

nabarundas,
nice try but no cigar: Miami-Dade, ST. THOMAS , New York

why do you think everyone has avoided this one?

I would suggest, as did Ashimer, use a cursor, select the field, manipulate it with a programming language then update.

you could write the program faster than generate the necessary complex sql.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Jun 03, 2008 3:38 pm    Post subject:
Reply with quote

Jeeva ,

this query will work as per your requirement provided only '.' and '-' will appear in city ..or else you may have to change the query as reqd ...

Code:


UPDATE INPTABLE SET CITY =
     CASE LOCATE('.',CITY)
      WHEN   0 THEN
        CASE LOCATE(' ',CITY)
           WHEN 0 THEN
             CASE LOCATE ('-',CITY)
                WHEN 0 THEN
                    SUBSTR(CITY,1,1) || LCASE(SUBSTR(CITY,2))
                ELSE
SUBSTR(CITY,1,1)||LCASE(SUBSTR(CITY,2,LOCATE('-',CITY)-2))||'-'
||SUBSTR(CITY,LOCATE('-',CITY)+1,1)||LCASE(SUBSTR(CITY,LOCATE('-',
CITY)+2))
                END
           ELSE
SUBSTR(CITY,1,1)||LCASE(SUBSTR(CITY,2,LOCATE(' ',CITY)-2))||' '
||SUBSTR(CITY,LOCATE(' ',CITY)+1,1)||LCASE(SUBSTR(CITY,LOCATE(' ',
CITY)+2))
           END
      ELSE
SUBSTR(CITY,1,1)||LCASE(SUBSTR(CITY,2,LOCATE('.',CITY)-2))||'.'
||SUBSTR(CITY,LOCATE('.',CITY)+1,1)||LCASE(SUBSTR(CITY,LOCATE('.',
CITY)+2))
     END;



as dick said this query might be a lil expensive and i wont recommend using this ... but as i had a lil free time today thot of writing this testing ...
consult with ur seniors before using this ...
Back to top
View user's profile Send private message
jeeva84

New User


Joined: 18 Apr 2008
Posts: 7
Location: India

PostPosted: Tue Jun 03, 2008 5:12 pm    Post subject:
Reply with quote

Thanks Ashimer.
Back to top
View user's profile Send private message
jeeva84

New User


Joined: 18 Apr 2008
Posts: 7
Location: India

PostPosted: Tue Jun 03, 2008 7:34 pm    Post subject:
Reply with quote

Now i got one more scenario. there are possibilities for three words.
For Example : KINGS AND QUEEN
The Expected Output is Kings And Queen.
For this i have to locate the second occurance of the space, is their any command in DB2
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10231
Location: italy

PostPosted: Tue Jun 03, 2008 7:58 pm    Post subject: Reply to: update record in sentence case
Reply with quote

Not natively, as You might already have noticed...
just expand the logic provide by Ashimer

at the end the cheapest solution will certainly be to write a small program
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Tue Jun 03, 2008 8:25 pm    Post subject:
Reply with quote

jeeva84 wrote:
Now i got one more scenario. there are possibilities for three words.
For Example : KINGS AND QUEEN
The Expected Output is Kings And Queen.
For this i have to locate the second occurance of the space, is their any command in DB2


Look at this thread http://ibmmainframes.com/viewtopic.php?t=31255&highlight=[/url]
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Jun 03, 2008 8:28 pm    Post subject:
Reply with quote

Jeeva,

Change your 2nd ELSE part to

Code:


SUBSTR(CITY,1,1)||LCASE(SUBSTR(CITY,2,LOCATE(' ',CITY)-2))||' '
||SUBSTR(CITY,LOCATE(' ',CITY)+1,1)||LCASE(SUBSTR(CITY,LOCATE(' ',
CITY)+2,LOCATE(' ',CITY,LOCATE(' ',CITY)) - LOCATE (' ',CITY) + 3))
||SUBSTR(CITY,LOCATE(' ',CITY,LOCATE(' ',CITY)+1)+1,1)
||LCASE(SUBSTR(CITY,LOCATE(' ',CITY,LOCATE(' ',CITY)+1)+2))



Now what will you do if there are 3 spaces within the variable you will have to expand this query and it will go on ....

Similarly if your city has value S.T.THOMAS again you need to change for this part too ... which will make the query more and more expensive ... so better handle these conditions programmatically
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Record Not found in AlX but record re... mukun264 COBOL Programming 5 Fri Mar 24, 2017 9:28 am
No new posts Count the length of the record & ... sreekusr DFSORT/ICETOOL 4 Thu Mar 23, 2017 7:52 pm
No new posts IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm
No new posts To Merge mutliple records into a sing... anandgbe DFSORT/ICETOOL 6 Wed Feb 22, 2017 8:49 am
No new posts VALIDATE NULL VALUE IN DB2 CASE STATE... useit DB2 5 Thu Feb 09, 2017 4:34 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us