Solved

How to truncate a string

Posted on 2014-12-17
4
86 Views
Last Modified: 2015-01-06
This may sound simple to you, but not to me.  I need to create a formula in Oracle Crystal that removes the first 4 characters in a field and leaves me with the rest of the field to show.  Then I want to be able to use this new formula/field to evaluate the contents and provide a select statement from it.

Example:  "ICD-401.01"  This is the contents of the field.  I need a new field with just 401.01 in it and then reference that field in a select statement that says {field} = [401.01, 401.02, 401.11, 456.24]
0
Comment
Question by:Becky Edwards
4 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40506130
I'm not a Crystal person but can help with the SQL.

Turning ICD-401.01 into 401.01 is easy:
select substr('ICD-401.01',5) from dual;

I have no idea what you mean by:
reference that field in a select statement that says {field} = [401.01, 401.02, 401.11, 456.24]

Maybe:
select 'Found it' from dual where substr('ICD-401.01',5) in (401.01, 401.02, 401.11, 456.24);
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 40506370
Try this

Mid({FIeld},4) IN ['401.01',  '401.02', '401.11', '456.24'] 

Open in new window


If you need to use then numeric strings as numbers then you need to convert the string

Val(Mid({FIeld},4)) IN  [401.01, 401.02, 401.11, 456.24] 

Open in new window


mlmcc
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 40512281
Hi, do you need it in oracle or some other ?
0
 

Author Closing Comment

by:Becky Edwards
ID: 40534160
This worked perfectly.  Thank you.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
oracle rollup query 3 50
Server error in 'XXXXXXXX' application. 12 72
Outer Query not returning data - SQL HELP 16 50
Queries 15 34
Introduction A frequently used term in Object-Oriented design is "SOLID" which is a mnemonic acronym that covers five principles of OO design.  These principles do not stand alone; there is interplay among them.  And they are not laws, merely princ…
It’s a strangely common occurrence that when you send someone their login details for a system, they can’t get in. This article will help you understand why it happens, and what you can do about it.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now