Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Substring in Select statement Post Reply Post New Topic
Author Message
crystalgal
Groupie
Groupie
Avatar

Joined: 20 Feb 2009
Location: United States
Online Status: Offline
Posts: 66
Quote crystalgal Replybullet Topic: Substring in Select statement
    Posted: 11 Jun 2010 at 7:39am
Hello all,
I am trying to include the substring in my select statement and something is not right, can anyone help.
 
I need to get 2 different words "Campus" and "DL" from the string.
They do not have specific lenth so I tried following

select (substr('string1', Instr('string','Campus',1,1)) from table

select (substr('string1', Instr('string1','DL',1,1)) from table

but it is not working. and is there a wayto concatinate 2 substr statements?
 
Really appreciate any help. TIA
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 11 Jun 2010 at 7:53am

is this in SQL server?  If so, the syntax is not right... it would be like:

select substring(string1, charindex('Campus', string), a length) as x from table
 
if the substrings are in the same record, you can concatenate like:
select substring(string1, charindex('Campus', string), a length) + substring(string1, charindex('DL', string), a length) as x from table.
 
if the length is not predetermined, you will need to determine a method to find the 'end' of the string.  Say there is a '.'(period) that is the end of the string you are looking for, you could do something like:
select substring(string1, charindex('Campus', string), charindex('.', string) - charindex('Campus', string) - 6 + 1) as x from table.
 
- 6 is for the length of Campus, plus 1 is to include the endpoints.
 
HTH 
 
 
IP IP Logged
crystalgal
Groupie
Groupie
Avatar

Joined: 20 Feb 2009
Location: United States
Online Status: Offline
Posts: 66
Quote crystalgal Replybullet Posted: 11 Jun 2010 at 8:01am
It's not SQl server. It is Oracle.
 
TIA
 
IP IP Logged
crystalgal
Groupie
Groupie
Avatar

Joined: 20 Feb 2009
Location: United States
Online Status: Offline
Posts: 66
Quote crystalgal Replybullet Posted: 11 Jun 2010 at 8:03am
They are in the same column but not in the same record.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 11 Jun 2010 at 9:02am
You're going to have to use a case statement.  Something like this:
 
select
case
  when stringfield like '%Campus%' then substr(stringfield, Instr('string','Campus',1,1))
  when stringfield like '%DL%' then (substr(stringfield, Instr('string1','DL',1,1))
  else ''
end as myCalculatedField
from myTable
 
-Dell
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.