Selecting only numbers from a field

[#1]

I need a table in QV containing a students year. The information is available in the SQL DB, but only as a number in a field containing class information.

Student_level

load

PersonID as %StudentID,

Year;

sql SELECT

PersonID,

left(Class,1) as Year

FROM Class

Where (Title like '%A' or Title like '%B' or Title like '%C' or Title like '%D' or Title like '%E' or Title like '%F' or Title like '%G')

and CHAR_LENGTH(Title) <4;

The SQL table looks like this:

PersonID, Class

1, 2A

2, 2A

3, 10B

4, 1G

5, 6C

and so on.

Unfortunately the above statement will return "1" as Year for both PersonID 3 and 4. Is there a way to strip away the characters, leaving just the numbers behind? Either in the SQL-statement or in the QV loading script?

I'm, as always, grateful for all help.

[hic · Accepted solution]

Yes, for instance

     KeepChar(Class,'0123456789')

HIC

[giakoum]

use Keepchar() function. try below mentioned syntex.

Ex: Keepchar('12ab98q', '0123456789') 

[#4]

Hey Joachim,

Either Keepchar or Purgechar can be used.

Keepchar( 'Field' ,'0123456789') 

or

Purgechar( 'Field', 'abcdefghijklmnopqrstuvwxyz')

Thanks

AJ

[#5]

Of course! Thank you.