×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!
  • Students Click Here

*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

How to exctract numeric from string

How to exctract numeric from string

How to exctract numeric from string

(OP)
Hi

How can I extract numberic digits from a Alphanumeric field as shown in image.
I want to extract it and generate the index on it.

Thanks

Saif

RE: How to exctract numeric from string

There is a tricky double use of CHRTRAN to remove all non-digits and then take the VAL of the remaining digits:

1. step; Remove all digits and thereby know all non-digits within the data.
2. step: take these known non-digits as the ones to remove and thereby keep the digits.

In short:

CODE

SELECT VAL(CHRTRAN(Code,CHRTRAN(Code,'1234567890',''),'')) as ID FROM Yourtable 

I just notice, that it also handles the cases with a C suffix (like CA321C), but you'll have the same value multiple times. This will not give you a primary key, at best a foreign key. And actually, if that's the case, you better aim to determine a numeric primary key in the table for which these codes are the current primary key.

Then you can also just use an autoincrementing integer and simply create a sequence of numbers not necessarily the numeric part of the code. It bears no real advantage to have a key that has a relationship with some other real-world attribute, even if that real-world attribute is a key or serial number for anyone or any other database in the world. You can only and forever guarantee a self-defined sequence number or other generated key value to be unique and non-changing within all lifetime of data. Including to not ever let it become reused.

So I would actually not recommend generating IDs from the digits of these codes.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: How to exctract numeric from string

That's a rather nifty solution Olaf, I would have been a long time before I came up with that...

My solution would have been

CODE

SELECT VAL(SUBSTR(CODE,3,255)) as ID from YourTable 

Because VAL drops out when it hits a non numeric such as the suffix

Regards

Griff
Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

RE: How to exctract numeric from string

It's not my original idea, but yes, you ccn also make use of that VAL() feature to ignore trailing letters. I think the non-unique nature of these numbers makes it a bad idea overall, but within the possibilities to work out numbers from an alphanumeric mix of characters removing non-digits can also be problematic in cases like a12x34z you would end up with 1234 whereas that number was never in consecutive places. So you got to be careful anyway, what you pick.

CA seems to be a filter coming from the textbox above the Code header. There might be codes starting with three letters or just one. So the givens are also still ambiguous, as so often.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: How to exctract numeric from string

(OP)
Perfect!!

I got it.

Thanks


Abbasaif

RE: How to exctract numeric from string

Obviously too late, but you could use both solutions and find discrepancies, which rule about which to pick:

CODE

SELECT Code, VAL(SUBSTR(CODE,3,255)) as ID1, VAL(CHRTRAN(Code,CHRTRAN(Code,'1234567890',''),'')) as ID2 FROM Yourtable INTO CURSOR TEST
Browse For Id1<>Id2 

If that sees no differences you could use both.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: How to exctract numeric from string

(OP)
Thanks Olaf

Abbasaif

RE: How to exctract numeric from string

Hi,

Quote:


I want to extract it and generate the index on it.

What do you expect from THAT index? Indexing on the CA field (as it is) or index on SUBSTR(CA, 3, 4) will yield the same results since all its values seem to start with "CA" - or do I miss something?

hth

MK

RE: How to exctract numeric from string

an index like that would be on the number for CA and CB and...

Regards

Griff
Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

RE: How to exctract numeric from string

Likely, this was not as I thought to get a primary key out of this, perhaps the index only is meant for sorting by the numeric part of the code.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: How to exctract numeric from string

Hi,

Ok, but what's this index worth since it yields the same result for field values as different as "CA0123", "CA123A", "CBA123" ...

MK

RE: How to exctract numeric from string

I don't know - we're just kickin' an idea around!

Regards

Griff
Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

RE: How to exctract numeric from string

When the codes combine multiple meanings, being able to sort independently might be the need, as simple as that. And yes, the better solution would be splitting the code into its parts. But that may also not be as straight forward. Just because we only see codes starting with 2 letters, that's not necessarily the pattern of all codes. The optional suffix already hints on more complexity, some optional portion.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

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