Keyword | Description | Examples |
|---|---|---|
@toCharacter() | Converts the input to Character data type. | select @toCharacter(batch_cd) as batchCode from ci_batch_ctrl |
@toDate() | Converts the input to Date data type. | select @toDate(last_update_dttm) as lastUpdateDate from ci_batch_ctrl |
@toNumber() | Converts the input to Number data type. | select @toNumber(next_batch_nbr) from ci_batch_ctrl |
@currentDate | Fetches the current date. CAUTION: The Oracle functions SYSDATE and CURRENT_DATE should not be used because they do not properly cater for adjusting dates from the database time zone to the installation time zone, if needed. | select batch_cd, @currentDate as today from ci_batch_ctrl |
@currentTimestamp | Fetches the current date / time. CAUTION: The Oracle functions SYSTIMESTAMP and CURRENT_TIMESTAMP should not be used because they do not properly cater for adjusting the date / time from the database time zone to the installation time zone, if needed. | select batch_cd from ci_batch_ctrl where last_update_dttm > @currentTimestamp |
@concat | Combines the result list of two or more columns. | select batch_cd @concat next_batch_nbr concatNbr from ci_batch_ctrl |
@substr(string, start) | String is the input String that you are trying to get a substring of. Start is the position of the character for the output results. | select batch_cd batchCode from ci_batch_ctrl Result: TESTCD select @substr(batch_cd,3) batchCode from ci_batch_ctrl Result: STCD |
@substr(string, start, end) | String is the input String that you are trying to get a substring of. Start is the position of the character for the output results. End is the number of characters required in the output from starting position. | Select batch_cd batchCode from ci_batch_ctrl Result: TESTCD select @substr(batch_cd,3,2) batchCode from ci_batch_ctrl Result: ST |
@trim | Trims the white spaces of the output on both sides. | select @trim(batch_cd) as batchCode from ci_batch_ctrl |
The following syntax is related to ‘fuzzy’ searching. It is only applicable if Oracle DB Text is enabled and a context text index has been created. Refer to Advanced Search Options for more information. | ||
@fuzzy(string, score, numresult, ‘weight’) | String is the input value for the search. Score is the degree of ‘fuzziness’. Valid values are between 1 - 80. The higher the number the more precise the search. Default is 60. Numresults is the number of variations to consider for the string. Valid values are between 1 and 5000. Default is 100. Indicate ‘weight’ to signal that the results are returned in order of weight. Leave this setting off to indicate that the results are returned in order of score. | Set score to 70, number results to 6, and specify weight. select user_id, last_name from sc_user where contains(last_name, @fuzzy(:F1,70, 6, 'weight')) > 0 |
@fuzzy(string) | This returns a string result from the fuzzy expansion operation where the default value of 60 is assumed for the score and the default value of 100 is assumed for the numresult. | To use default values: select user_id, last_name from sc_user where contains(last_name, @fuzzy(:F1))> 0 |
@fuzzy(string, score) | This returns a string result from the fuzzy expansion operation with the score specified and the default value of 100 for the numresult. | Set score to 70. select user_id, last_name from sc_user where contains(last_name, @fuzzy(:F1,70)) > 0 |
@fuzzy(string, score, numresult) | This returns a string resulted from the fuzzy expansion operation with the similarity score and the numresults specified. | Set score to 70, number results to 6. select user_id, last_name from sc_user where contains(last_name, @fuzzy(:F1,70, 6)) > 0 |