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 |
|
|