×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

format Varchar2 field as number . . unless it contains a letter.

thread149-1788305

format Varchar2 field as number . . unless it contains a letter.

format Varchar2 field as number . . unless it contains a letter.

(OP)
I'm having a strange sort of day and my mind is jumbled. I haven't posted here in years but always got the help I needed when I have posted. I'm hoping for some help again. I'm sure on any other day the solution would come to me but it's just one of those.

I have a field which is stored as varchar. Most of the values are numeric except for about 3%. There's good reason for that and I don't think it is something that will change. However, I need to export this into excel as a a number EXCEPT for the 3% which are alphanumeric. I need those to remain alphanumeric. I don't think the VAL function will work because that will just return the numeric portion of the field and not retain the letter.

If I use if numerictext then tonumber it will show a zero for the field...

*sigh*

Any ideas? I should just sleep on it but I need it done sooner than later and don't have the time to put it aside for too long.

Regards,

jk

RE: format Varchar2 field as number . . unless it contains a letter.

Not sure if the isnumeric would help. It should only be true if the field just returns a number not alphanumeric.

RE: format Varchar2 field as number . . unless it contains a letter.

(OP)
Thanks kray4660, I tried that but it;s looking for a numeric value for the else clause. I want it to either return the numeric value if it is only numeric and the alphanumeric if it is alphanumeric.

RE: format Varchar2 field as number . . unless it contains a letter.

I think you would need to do this from within excel after export.

In Crystal, a formula result can only be a single data type, ie all text or all numeric. You therefore can't have a formula where the result is either text or numeric based on some condition (such as containing alpha characters).


Cheers
Pete

RE: format Varchar2 field as number . . unless it contains a letter.

(OP)
OK Thanks Pete-that seems to make sense of what I'm seeing. Excel it is!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close