| Author |
Message |
crystalgal
Groupie
Joined: 20 Feb 2009
Location: United States
Online Status: Offline
Posts: 66
|
   Topic: Substring and Instr together
Posted: 25 Oct 2012 at 4:16am |
|
Hello,
I am having an issue using Substr and Instr and need some help.
right now I have
field:
asdfg - itsnoteasy - whattodo
I don't need - whattodo from the field
I used substr(field, instr(field,'-',1)) as name,
but not working.
Any help is appreciated.
TIA
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 25 Oct 2012 at 4:55am |
do you mean you want to return
"asdfg - itsnoteasy"
?
|
|
IP Logged |
|
crystalgal
Groupie
Joined: 20 Feb 2009
Location: United States
Online Status: Offline
Posts: 66
|
Posted: 25 Oct 2012 at 4:57am |
|
yes and I tried all the combinations after Instr but is not working.
Thank you.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 25 Oct 2012 at 5:07am |
|
mid({table.field},1,instrrev({table.field}," - "))
|
|
IP Logged |
|
crystalgal
Groupie
Joined: 20 Feb 2009
Location: United States
Online Status: Offline
Posts: 66
|
Posted: 25 Oct 2012 at 5:34am |
|
Thank you but is it possible to use substr and instr as I need this field not as a formula and I am trying to use as a sql in the report.
TIA
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 25 Oct 2012 at 5:45am |
|
I do not understand what you mean by "use as a sql in the report"?
|
|
IP Logged |
|
crystalgal
Groupie
Joined: 20 Feb 2009
Location: United States
Online Status: Offline
Posts: 66
|
Posted: 25 Oct 2012 at 5:58am |
|
I am using pl/sql to get the fields for this report and this particular field I need as a parameter. I have seen that if it is a formula, Crystal does not show in the list to pick for the parameter.
Thank you
Edited by crystalgal - 25 Oct 2012 at 6:04am
|
|
IP Logged |
|
crystalgal
Groupie
Joined: 20 Feb 2009
Location: United States
Online Status: Offline
Posts: 66
|
Posted: 25 Oct 2012 at 6:19am |
|
I got by using this.
substr(field,0, instr(field,'-',1,2)-1)
Thank you very much. You are always there/here when I need it.:-)
|
|
IP Logged |
|
|
|
