Home » SQL & PL/SQL » SQL & PL/SQL » sql help
sql help [message #226330] Fri, 23 March 2007 04:56 Go to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
i have table

empno
12~
123~
1234~

my requirement is.
i want to

empno
12
123
1234
Re: sql help [message #226337 is a reply to message #226330] Fri, 23 March 2007 05:05 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
SQL> WITH yourtable AS
  2   ( SELECT '12~'   empno FROM dual UNION ALL
  3     SELECT '123~'  empno FROM dual UNION ALL
  4     SELECT '1234~' empno FROM dual
  5   )
  6  SELECT TO_NUMBER(SUBSTR(empno,1,length(empno)-1)) method_1
  7       , TO_NUMBER(REPLACE(empno,'~','')) method_2
  8  FROM yourtable
  9  /

  METHOD_1   METHOD_2
---------- ----------
        12         12
       123        123
      1234       1234
Forget the WITH part, start reading at the "SELECT".

MHE
Re: sql help [message #226350 is a reply to message #226337] Fri, 23 March 2007 05:56 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
sorry iam asking agian
empno
12~122222
123~444444
1234~77777777

my requirement is.
i want to

empno
12
123
1234




Re: sql help [message #226354 is a reply to message #226350] Fri, 23 March 2007 06:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator


substr(empno,1,instr(empno,'~')-1)

Regards
Michel
Re: sql help [message #226358 is a reply to message #226350] Fri, 23 March 2007 06:19 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
If you are on 10g, you can consider:

SQL> select regexp_substr('1234~7777','[^~]*') from dual;

REGE
----
1234


Re: sql help [message #226364 is a reply to message #226358] Fri, 23 March 2007 06:43 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
sorry iam asking agian
empno
12~122222~123~12345~
123~444444
1234~77777777

my requirement is.
i want to

empno
12
123
1234

iam using oracle 9i

Re: sql help [message #226365 is a reply to message #226364] Fri, 23 March 2007 06:44 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
pavuluri wrote on Fri, 23 March 2007 12:43
sorry iam asking agian


And this didn't work for you?

Michel Cadot wrote on Fri, 23 March 2007 12:06


substr(empno,1,instr(empno,'~')-1)

Regards
Michel



MHE

Re: sql help [message #226368 is a reply to message #226365] Fri, 23 March 2007 06:48 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
thanks,
its working
Re: sql help [message #226371 is a reply to message #226368] Fri, 23 March 2007 07:31 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
sorry for the Trouble
empno
12~122222~123~12345~
123~444444
1234~77777777

my requirement is.
i want to

empno
122222
444444
77777777
Re: sql help [message #226372 is a reply to message #226371] Fri, 23 March 2007 07:36 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Say "AAAAAAA" and wait until the spoon enters your wide-open mouth.
Re: sql help [message #226373 is a reply to message #226372] Fri, 23 March 2007 07:39 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
sorry man
thanks
srinivas
Re: sql help [message #226663 is a reply to message #226373] Mon, 26 March 2007 02:29 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
yes

i got it

select substr(empno,instr(empno,'~')+1,instr(empno,'~')+2) from rama1
thanks,
srinivas
Re: sql help [message #226808 is a reply to message #226663] Mon, 26 March 2007 11:06 Go to previous message
Ericle
Messages: 44
Registered: April 2006
Location: United States of America ...
Member

pavuluri wrote on Mon, 26 March 2007 03:29
yes

i got it

select substr(empno,instr(empno,'~')+1,instr(empno,'~')+2) from rama1
thanks,
srinivas


Close, but not quite. In the case of '12~122222~123~12345~' you get '12222' instead of the desired '122222'. In the case of '1234~77777777', you get '7777777' instead of the desired '77777777'.

This would work in all cases:

select substr(empno,instr(empno,'~')+1,case when instr(empno,'~',1,2) != 0 then instr(empno,'~',1,2) - instr(empno,'~') - 1
 when instr(empno,'~') != 0 then length(empno) - instr(empno,'~') else length(empno) end)
from rama1

[Updated on: Mon, 26 March 2007 11:51]

Report message to a moderator

Previous Topic: FATAL ERROR : Inconsistent Database ( urgent)
Next Topic: Need help on primary key
Goto Forum:
  


Current Time: Fri Dec 02 14:21:04 CST 2016

Total time taken to generate the page: 0.18501 seconds