Solved

How to truncate a string

Posted on 2014-12-17
4
88 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
free scanner TWAIN can be operated with a Web application 9 68
Writing and Selling Web App Based on Google Sheets 2 73
Help on model clause 5 37
QQ problem 22 42
A Change in PHP Behavior with Session Write Short Circuit (http://php.net/manual/en/book.session.php#116217) (Winter 2014)** With the release of PHP 5.6 the session handler changed in a way that many think should be considered a bug.  See the note …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

790 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