[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

How to truncate a string

Posted on 2014-12-17
4
Medium Priority
?
98 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 77

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 101

Accepted Solution

by:
mlmcc earned 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Learn how to set-up custom confirmation messages to users who complete your Wufoo form. Include inputs from fields in your form, webpage redirects, and more with Wufoo’s confirmation options.

650 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