How to truncate a string

Posted on 2014-12-17
Medium Priority
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]
Question by:Becky Edwards
LVL 78

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]

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

Accepted Solution

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

LVL 28

Expert Comment

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

Author Closing Comment

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

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to selectively show certain fields based on user input using rules to gather relevant information and data from your forms. The rules feature provides you with an opportunity…
Suggested Courses

624 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