| Sql query [message #262641] |
Tue, 28 August 2007 00:00  |
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
|
Member |
|
|
Am having a table with struct like
CREATE TABLE WEO_SAMP_TEXT (KEY_VAL NUMBER,VALUE1 VARCHAR2(20))
Am having values like
key_val value1
199933 1803-cm-vehicle
199933 195-vm-vehicle
167777 19077-dd-vehicle
I need a query which should give me an output as
value1
1803
195
for the input key_val=199933
|
|
|
|
|
|
| Re: Sql query [message #262651 is a reply to message #262649] |
Tue, 28 August 2007 00:40  |
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
|
Member |
|
|
While selecting the data , Starting value should be a number.
ex
tg-1903-cm_vehicle
1855-ff-vehicle
tg-155-cm_vehicle
among these values i need 1855-ff-vehicle only.
|
|
|
|
| Re: Sql query [message #262654 is a reply to message #262651] |
Tue, 28 August 2007 00:44 |
 |
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
add the condition "and substr(value1,1,1) in ('0','1',...,'9')" or "substr(value1,1,1) between '0' and '9'".
Regards
Michel
|
|
|
|
| Re: Sql query [message #262689 is a reply to message #262654] |
Tue, 28 August 2007 01:24 |
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
|
Member |
|
|
I need to update that table only with the number which is appearing initially.
ex
original value
key_val value1
199933 1803-cm-vehicle
199933 195-vm-vehicle
167777 19077-dd-vehicle
i need update statement which should finally appears as
key_val value1
199933 1803
199933 195
167777 19077
so i have tried with the query
UPDATE weo_SAMP_TEXT SET value1=( SELECT SUBSTR(x.value1,1, INSTR(x.value1,'-',1 )-1) , INSTR(x.value1,'-',1 )
FROM weo_SAMP_TEXT x WHERE x.KEY_VAL =1989 AND SUBSTR(x.value1,1, 1) IN('0','1','2','3','4'))
it's giving too many value error
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
