1 / 17

String Functions

String Functions. ASCII(char) RETURNS ASCII value of character(single) passed as argument. Returns corresponding Ascii value SELECT ASCII('A') FROM dual; SELECT ASCII('Z') FROM dual; SELECT ASCII('a') FROM dual; SELECT ASCII('z') FROM dual; SELECT ASCII(' ') FROM dual;.

brent
Download Presentation

String Functions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. String Functions ASCII(char) RETURNS ASCII value of character(single) passed as argument. Returns corresponding Ascii value • SELECT ASCII('A') FROM dual;SELECT ASCII('Z') FROM dual;SELECT ASCII('a') FROM dual;SELECT ASCII('z') FROM dual;SELECT ASCII(' ') FROM dual;

  2. CASE Related Functions • UPPER(ch) Returns upper case of the string passed as arguments. SELECT UPPER(‘M salim') FROM dual; • LOWER(ch) • Returns Lower case of the string passed as arguments. SELECT LOWER(‘M salim') FROM dual; Initial Letter Upper Case SELECT INITCAP(‘M salim') FROM dual;

  3. CHR(n) • Returns character values corresponding to ascii n • SELECT(CHR(68) || CHR(65) || CHR(78)) FROM dual;SELECT(CHR(68) || CHR(97) || CHR(110)) FROM dual; • SELECT 'i WISH LINE BREAK HERE'||CHR(10)||'pakistan computer Bureau' AS RESULT FROM DUAL; • SQL> / • RESULT • ----------------------------------------- • i WISH LINE BREAK HERE • PAKISTAN COMPUTER BUREAU CONCAT(chr1, chr2) Concatenate chr1 and chr2 (equivalent to the || operator) SELECT CONCAT(‘Muhammad ', ‘ zubair') from dual; LENGTHString Length LENGTH(t) Length (expressed in characters) of t SELECT LENGTH(‘M Arshad') FROM dual; LPAD LPAD(t,n[,k]) Left-pad t with sequence of characters in k to length n SELECT LPAD('Aamir khan', 25, 'x') FROM dual select lpad(stname, 15, '*') from pst • /

  4. RPAD • RPAD(t,n[,k])Right-pad t with k to length n (the default k is a space) SELECT RPAD('Aamir khan', 25, 'x') FROM dual; Left Trim LTRIM(t[,k]) the ltrim function removes all specified characters from the left-hand side of a string. SELECT '->' || LTRIM('   Jamil   ') || '<-' FROM dual; SELECT '->' || LTRIM('xxx Jamil   ') || '<-' FROM dual; SELECT '->' || LTRIM('xxxJamil   ', 'x') || '<-' FROM dual; SELECT '->' || LTRIM('xxxJamil   ', CHR(120)) || '<-' FROM dual • SELECT '->' || LTRIM('xyxJamil   ', 'xy') || '<-' FROM dual • SELECT '->' || LTRIM('xyxJamil   ', 'xyj') || '<-' FROM dual RTRIM RTRIM(t[,k]) The Rtrim function removes all specified characters from the left-hand side of a string. SELECT '->' || RTRIM('   Dan Morgan  ') || '<-' FROM dual; SELECT '->' || RTRIM('   Dan Morganxxx') || '<-' FROM dual; Follwing two statements prosduce the same result SELECT '->' || RTRIM('   Dan Morganxxx', 'xxx') || '<-' FROM dual; SELECT '->' || RTRIM('   Dan Morganxxx', 'x') || '<-' FROM dual TRIM Trim Spaces select trim(' m aslal ') from dual;

  5. Trim. the trim function removes all specified characters either from the beginning or the ending of a string.TRIM(<character_to_trim> FROM <string_or_column>) • SELECT TRIM('D' FROM 'Dan MorgDanD') FROM dual • SELECT TRIM(CHR(68) FROM 'Dan Morgan') FROM dual; • select trim (' fdf ') from dual; • trim( [ leading | trailing | both  [ trim_character ] FROM ]   string1 ) • trim('   tech   ') would return 'tech' • trim(' '  from  '   tech   ') would return 'tech' • trim(leading '0' from '000123') would return '123' • trim(trailing '1' from 'Tech1') would return 'Tech' • trim(both '1' from '123Tech111') would return '23Tech'

  6. select trim ( BOTH FROM ' 01120 ') from dual • => If we skip trimming character then by default spaces are trimmed.

  7. SUBSTR (Substring) Built-in String Function • SUBSTR(STR1, POS , LEN ) • SELECT SUBSTR('Take the first four characters', 1, 4) FIRST_FOUR FROM dual; • SELECT SUBSTR('Take the first four characters', 16, 4) MIDDLE_FOURFROM dual; • SELECT SUBSTR('Take the first four characters', 16) SIXTEEN_TO_END FROM dual; • SELECT SUBSTR('Take the first four characters', -4) FINAL_FOUR FROM dual; • In last example minus shows items are to picked up from end and the attachec no (i.e 4) means take 4 characters.. Thus no need of 2nd arguments.

  8. INSTR (Instring) Built-in String Function • INSTR(STR1, STR2 ,POS, NTH )    • SELECT INSTR('Take the first four characters', 'a', 1, 1) FOUND_1 FROM dual; • SELECT INSTR('Take the first four characters', 'a', 1, 2) FOUND_2FROM dual; • SELECT INSTR('Take the first four characters', 'four', 1, 1) MCHARS FROM dual; • SELECT INSTR('Take the first four characters', 'a', -1, 1) REV_SRCHFROM dual; • select instr('Take the first four characters' ,'a', -1, 2) rev_two from dual;

  9. Numeric Functions • ABS the abs function returns the absolute value of a number. • SELECT ABS(-100) FROM dual; • AVGReturns the average of a column of numbers • select avg(sal) from emp; • COUNT returns the number of rows in a query. • SELECT COUNT(*) FROM all_objs; • CEILCEIL(<value>)Smallest integer greater than or equal to a decimal value • SELECT CEIL(12345.67) FROM dual; • FLOOR FLOOR(<column>/numeric value) Returns the largest integer less than or equal to a decimal value • SELECT FLOOR(12345.67) FROM dual; • GREATEST GREATEST(<value>, <value>, .... ) the greatest function returns the greatest value in a list of expressions. • SELECT GREATEST(9, 67.6, 10) FROM dual; • LEAST Returns the smallest of multiple values • SELECT LEAST(9, 67.6, 10) FROM dual;

  10. EXPReturns e raised to to an exponential power • SELECT 2.71828183 * 2.71828183 FROM dual;SELECT EXP(2) FROM dual; • LN LN(<value>) Returns the natural log of a number • SELECT LN(2) NATURAL_LOG FROM dual; • LOG LOG(<m_value>,<n_value>) Returns the logarithm, base m of n • SELECT LOG(10,100) FROM dual;SELECT LOG(100,10) FROM dual; • MAX MAX(<column_name>) Returns the maximum value returned by a query • select max(sal) from emp; • MIN MIN(<column_name>) Returns the minimum value returned by a query

  11. MEDIAN MEDIAN(<column_name>) Returns the middle value of a set • select median(sal) from emp • STDDEV STDDEV(<expression>) • SELECT STDDEV(salary) AS DEVIATION FROM employees; • VARIANCE VARIANCE(<value>) • SUM SUM(<column_name>) • MOD • SELECT MOD(3, 2) FROM dual;SELECT MOD(6, 2) FROM dual;  • REMAINDER REMAINDER(<m_value>, <n_value>) the remainder from dividing m by n. • SELECT REMAINDER(2,3) FROM dual; • NVL NVL(<expression>, <return_value>) Returns a Value if the Expression IS NULL • Select nvl(comm, 0) from emp; • POWER POWER(<m_value>, <n_value>) • SELECT 2*2*2 FROM dual;SELECT POWER(2,3) FROM dual; • ROUND ROUND(<value>, <integer>) Returns a value rounded to integer places • SELECT ROUND(3.1415926, 4) FROM dual; • TRUNC TRUNC(<value>, <decimal_places>) Truncates a Number to the Specified Number of Decimal Places • SELECT TRUNC(15.79, 1) FROM dual;SELECT TRUNC(15.79, -1) FROM dual;

  12. Translate • Translate replaces by position, the first character of the list to match is replaced by the first character of the replacement list. The second character with the second, and if there are characters in the list to match that do not have positional equivalents in the replacements list they are dropped. • SELECT TRANSLATE('comma,delimited,list', ',', '|') FROM dual; • SELECT TRANSLATE('So What', 'ah', 'e') FROM dual;

  13. Date • Current Date CURRENT_DATE SYSDATE • Format Day Month Year D MM YY DD MON YYYY  SELECT TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY HH:MI:SS') FROM dual;SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') FROM dual;

  14. CREATE TABLE t (datecol1 DATE,datecol2 DATE,datecol3 DATE)PCTFREE 0;INSERT INTO t VALUES (SYSDATE+23, SYSDATE-10, SYSDATE-24);INSERT INTO t VALUES (SYSDATE-15, SYSDATE, SYSDATE+15);INSERT INTO t VALUES (SYSDATE-7, SYSDATE-18, SYSDATE-9);COMMIT;SELECT * FROM t;SELECT GREATEST(datecol1, datecol2, datecol3)FROM t;

  15. LAST_DAY LAST_DAY(<date>) Returns The Last Date Of A Month • SELECT LAST_DAY(datecol1) FROM t; • SELECT to_char(LAST_DAY(datecol1), 'day') FROM t; • SELECT to_char(LAST_DAY(datecol1), 'dd') FROM t • LEAST Return the Earliest Date • SELECT LEAST(datecol1, datecol2, datecol3) FROM t; • MAX MAX(<date>) Return the Latest Date • SELECT MAX(datecol1) FROM t; • MIN MIN(<date>) Return the Earliest Date

  16. SELECT TO_CHAR(TO_DATE('10:30:18', 'HH24:MI:SS'), 'HH24SP:MISP:SSSP')FROM dual;SELECT TO_CHAR(TO_DATE('01-JAN-2008', 'DD-MON-YYYY'), 'DDSP-MONTH-YYYYSP')FROM dual;

  17. Conversion Functions • the to_number function converts a string to a number. • TO_NUMBER( string1, [ format_mask ]) • to_number('1210.73', '9999.99') would return the number 1210.73 to_number('546', '999') would return the number 546 to_number('23', '99') would return the number 23 • to_number('1210.73'') would return the number 1210.73 • TO_CHAR( value, [ format_mask ]) • the to_char function converts a number or date to a string. • to_char(1210.73, '9999.9') would return '1210.7' • to_char(1210.73, '9,999.99') would return '1,210.73' • to_char(1210.73, '$9,999.00') would return '$1,210.73' • to_char(21, '000099') would return '000021'

More Related