Home » SQL & PL/SQL » SQL & PL/SQL » String problem
String problem [message #235645] Mon, 07 May 2007 09:52 Go to next message
kanis
Messages: 61
Registered: November 2006
Member
I have one problem ..
string like
<string>,<string>,<string>

now i need to check whether the
the length of each string ie separated by comma should not be more than nine characters.
can it possible in a single query??
can any one give me the sol~n.

[Updated on: Mon, 07 May 2007 09:53]

Report message to a moderator

Re: String problem [message #235658 is a reply to message #235645] Mon, 07 May 2007 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is possible.
Oracle version?

Regards
Michel
Re: String problem [message #235783 is a reply to message #235658] Tue, 08 May 2007 00:34 Go to previous messageGo to next message
kanis
Messages: 61
Registered: November 2006
Member
oracle ver. 9i
Re: String problem [message #236067 is a reply to message #235783] Tue, 08 May 2007 23:48 Go to previous messageGo to next message
kanis
Messages: 61
Registered: November 2006
Member
Can any one please response the problem???
Re: String problem [message #236070 is a reply to message #235645] Tue, 08 May 2007 23:55 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Th solution is a Small Matter Of Programming (SMOP).
PL/SQL might be a useful choice.
Re: String problem [message #236097 is a reply to message #235645] Wed, 09 May 2007 00:52 Go to previous messageGo to next message
madhusudaniyaar
Messages: 1
Registered: May 2007
Location: Hyderabad
Junior Member

select case when
length(substr('sam,peteranderson,michelle',1,(instr('sam,peteranderson,michelle',',',1)-1)))>9
then 'first string length is greater than 9'
when
length(substr('sam,peteranderson,michelle',(instr('sam,peteranderson,michelle',',',1,1)),instr('sam,peteranderson,michelle',',',1,2)) ) >10
then 'second string length is greater than 9'
when
length(substr('sam,peteranderson,michelle',instr('sam,peteranderson,michelle',',',-1),length('sam,peteranderson,michelle')))>10
then 'second string length is greater than 9'
else 'fine'
end case
from dual;


this query would solve your problem
Re: String problem [message #236099 is a reply to message #236097] Wed, 09 May 2007 00:58 Go to previous messageGo to next message
kanis
Messages: 61
Registered: November 2006
Member
thanks for the help ..
but it is not sure whether only 3 substring will be there or more than that separated by comma..
also no need to specify the the string where length is more than 9 ..
Only need to know if the whole string containing any substring which is in comma is more than length 9 or not ..


Re: String problem [message #236120 is a reply to message #235783] Wed, 09 May 2007 01:35 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> var str varchar2(100)
SQL> exec :str := 'sam,peteranderson,michelle'

PL/SQL procedure successfully completed.

SQL> with
  2    data as (
  3      select max(length(substr(:str,
  4                               instr(','||:str||',', ',', 1, rn),
  5                               instr(','||:str||',', ',', 1, rn+1)
  6                               - instr(','||:str||',', ',', 1, rn) - 1))) maxlg
  7      from ( select rownum rn from dual 
  8             connect by level 
  9                          <= length(:str)-length(replace(:str,',',''))+1 )
 10    )
 11  select case when maxlg > 9 then 'NOK' else 'OK' end tst
 12  from data
 13  /
TST
---
NOK

1 row selected.

Regards
Michel
Previous Topic: problem in assigning serial number to records fetched by a query
Next Topic: Date shows 00/00/0000
Goto Forum:
  


Current Time: Sun Dec 04 06:56:01 CST 2016

Total time taken to generate the page: 0.07501 seconds