Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Yes, for instance
KeepChar(Class,'0123456789')
HIC
use Keepchar() function. try below mentioned syntex.
Ex: Keepchar('12ab98q', '0123456789')
Hey Joachim,
Either Keepchar or Purgechar can be used.
Keepchar( 'Field' ,'0123456789')
or
Purgechar( 'Field', 'abcdefghijklmnopqrstuvwxyz')
Thanks
AJ
Of course! Thank you.