How to exctract numeric from string

[abbasaif]

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

 

[Olaf Doschke]

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:

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

 

[GriffMG]

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

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 [Smile]ing

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. ​

 

[Olaf Doschke]

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

 

[abbasaif]

Perfect!!

I got it.

Thanks


Abbasaif

 

[Olaf Doschke]

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

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

 

[abbasaif]

Thanks Olaf

Abbasaif

 

[mjcmkrsr]

Hi,

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

 

[GriffMG]

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

Regards

Griff
Keep [Smile]ing

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. ​

 

[Olaf Doschke]

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

 

[mjcmkrsr]

Hi,

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

MK

 

[GriffMG]

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

Regards

Griff
Keep [Smile]ing

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. ​

 

[Olaf Doschke]

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