Home » SQL & PL/SQL » SQL & PL/SQL » Sql query
Sql query [message #262641] Tue, 28 August 2007 00:00 Go to next message
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 #262649 is a reply to message #262641] Tue, 28 August 2007 00:31 Go to previous messageGo to next message
hasan_uiu
Messages: 18
Registered: August 2007
Location: Dhaka
Junior Member

SELECT SUBSTR(x.value1,1, INSTR(x.value1,'-',1,1 )-1)FROM WEO_SAMP_TEXT x WHERE x.key_val =199933;
Re: Sql query [message #262651 is a reply to message #262649] Tue, 28 August 2007 00:40 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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
Re: Sql query [message #262708 is a reply to message #262689] Tue, 28 August 2007 02:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
it's giving too many value error

Count the parenthesis.

Regards
Michel
Re: Sql query [message #262779 is a reply to message #262689] Tue, 28 August 2007 05:00 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
why the select? You don't want any other records involved, do you?
Previous Topic: case in where ... what i m doing wrong??
Next Topic: Could you, please, explain this query? (merged & renamed by LF)
Goto Forum:
  


Current Time: Wed Dec 07 20:40:21 CST 2016

Total time taken to generate the page: 0.07733 seconds