|
View previous topic :: View next topic
|
| Author |
Message |
jeeva84
New User
Joined: 18 Apr 2008
Posts: 7
Location: India
|
|
|
|
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 |
|
 |
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
ashimer,
i don't understand how LCASE(CITY) will result in Miami, South Dakota, Miami-Dade? |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
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 |
|
|
jeeva84
New User
Joined: 18 Apr 2008
Posts: 7
Location: India
|
|
|
|
ok thnx Ashimer and Dick.
Is their any other way to achive this. |
|
| Back to top |
|
|
nabarundas
New User
Joined: 21 Jun 2007
Posts: 28
Location: pune
|
|
|
|
hi,
Try this:
SELECT CONCAT(SUBSTR(city,1,1),LCASE(SUBSTR(city,2)))
FROM <TableName>
Regards,
Nabarun |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
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 |
|
|
jeeva84
New User
Joined: 18 Apr 2008
Posts: 7
Location: India
|
|
|
|
| Thanks Ashimer. |
|
| Back to top |
|
|
jeeva84
New User
Joined: 18 Apr 2008
Posts: 7
Location: India
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
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 |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| 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 ibmmainframes.com/viewtopic.php?t=31255&highlight=[/url] |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
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 |
|
|
|
|
