Using Expressions in PS Query
From TekiWiki
This whole wiki has now moved to http://www.springboardsolutions.com/wiki
Go to this page Using Expressions in PS Query
| Line 13: | Line 13: | ||
===Function: Substr === | ===Function: Substr === | ||
'''Usage: substr(A.DESCR,start_position,number_of_characters) ''' | '''Usage: substr(A.DESCR,start_position,number_of_characters) ''' | ||
| + | |||
'''Usage: substr(A.DESCR,start_position) ''' | '''Usage: substr(A.DESCR,start_position) ''' | ||
| + | |||
'''Usage: substr(A.DESCR,-start_position,number_of_characters) ''' | '''Usage: substr(A.DESCR,-start_position,number_of_characters) ''' | ||
| + | |||
This extracts a part of a text column. | This extracts a part of a text column. | ||
'''Examples''' | '''Examples''' | ||
| + | |||
:Substr(A.DESCR,3) - The description without the first three characters | :Substr(A.DESCR,3) - The description without the first three characters | ||
===Function: Translate === | ===Function: Translate === | ||
'''Usage: translate(A.DESCR,'List1','List2') ''' | '''Usage: translate(A.DESCR,'List1','List2') ''' | ||
| + | |||
'''Usage: translate(A.DESCR,'X1234567890','X') ''' | '''Usage: translate(A.DESCR,'X1234567890','X') ''' | ||
| + | |||
Translates characters in the column found in first list with corresponding character in the second list. | Translates characters in the column found in first list with corresponding character in the second list. | ||
'''Examples''' | '''Examples''' | ||
| + | |||
:translate(NATIONAL_ID, | :translate(NATIONAL_ID, | ||
'0123456789'||'abcdefghijklmnopqrstuvwxyz'||'ABCDEFGHIJKLMNOPQRSTUVWXYZ' | '0123456789'||'abcdefghijklmnopqrstuvwxyz'||'ABCDEFGHIJKLMNOPQRSTUVWXYZ' | ||
,rpad('9',10)||rpad('x',26)||rpad('X',26)) | ,rpad('9',10)||rpad('x',26)||rpad('X',26)) | ||
| - | + | ||
| + | Shows the format of NATIONAL_ID - useful in a group by query | ||
:translate(TextField,'-0123456789','-') - Removes digits from text field. | :translate(TextField,'-0123456789','-') - Removes digits from text field. | ||
