الدرس السادس

to_date( string1, [ format_mask ], [ nls_************************************************ ] )

to_date('2003/07/09', 'yyyy/mm/dd') would return a date value of July 9, 2003. 
to_date('070903', 'MMDDYY') would return a date value of July 9, 2003. 
to_date('20020315', 'yyyymmdd') would return a date value of Mar 15, 2002.

last_day( date )

add_months( date1, n )

add_months('01-Aug-03', 3) would return '01-Nov-03' 
add_months('01-Aug-03', -3) would return '01-May-03' 
add_months('21-Aug-03', -3) would return '21-May-03' 
add_months('31-Jan-03', 1) would return '28-Feb-03'

next_date(date1,n)

NEXT_DAY('01-01-2006',5) RETURN '06-01-2006'
NEXT_DAY('15-01-2006',-5) RETURN '10-01-2006'

next_day('01-Aug-03', 'TUESDAY')

trim( [ leading | trailing | both  [ trim_character ]  ]   string1 )

trim('   tech   ') سوف نرجع القيمة  بدون فراغات'tech' 
trim(' '  from  '   tech   ') سوف ترجع الكلمة بدون فراغات ولاحظا اننا استخدمنا from 'tech' 
trim(leading '0' from '000123') سوف يتم حذف الاصفار من بداية الجملة'123' 
trim(trailing '1' from 'Tech1') سوف يتم حذف الاواحد من الآخر'Tech' 
trim(both '1' from '123Tech111') سوف يتم حذف الرقم واحد من اليمين والشمال'23Tech'

ltrim( string1, [ trim_string ] )

ltrim('   tech'); would return 'tech' 
ltrim('   tech', ' '); would return 'tech' 
ltrim('000123', '0'); would return '123' 
ltrim('123123Tech', '123'); would return 'Tech' 
ltrim('123123Tech123', '123'); would return 'Tech123' 
ltrim('xyxzyyyTech', 'xyz'); would return 'Tech' 
ltrim('6372Tech', '0123456789'); would return 'Tech'

ltrim('xyxzyyyTech', 'xyz'); would return 'Tech'

ltrim( '6372Tech', '0123456789'); would return 'Tech'

rtrim( string1, [ trim_string ] )

rtrim('tech   '); would return 'tech' 
rtrim('tech   ', ' '); would return 'tech' 
rtrim('123000', '0'); would return '123' 
rtrim('Tech123123', '123'); would return 'Tech' 
rtrim('123Tech123', '123'); would return '123Tech' 
rtrim('Techxyxzyyy', 'xyz'); would return 'Tech' 
rtrim('Tech6372', '0123456789'); would return 'Tech'

rtrim('Techxyxzyyy', 'xyz'); would return 'Tech' 
rtrim('Tech6372', '0123456789'); would return 'Tech'

lpad( string1, padded_length, [ pad_string ] )

lpad('tech', 7); would return '   tech' 
lpad('tech', 2); would return 'te' 
lpad('tech', 8, '0'); would return '0000tech' 
lpad('tech on the net', 15, 'z'); would return 'tech on the net' 
lpad('tech on the net', 16, 'z'); would return 'ztech on the net'

rpad( string1, padded_length, [ pad_string ] )

rpad('tech', 7); would return 'tech   ' 
rpad('tech', 2); would return 'te' 
rpad('tech', 8, '0'); would return 'tech0000' 
rpad('tech on the net', 15, 'z'); would return 'tech on the net' 
rpad('tech on the net', 16, 'z'); would return 'tech on the netz'

substr( string, start_position, [ length ] )

substr('This is a test', 6, 2) would return 'is' 
substr('This is a test', 6) would return 'is a test' 
substr('TechOnTheNet', 1, 4) would return 'Tech' 
substr('TechOnTheNet', -3, 3) would return 'Net' 
substr('TechOnTheNet', -6, 3) would return 'The' 
substr('TechOnTheNet', -8, 2) would return 'On'

instr( string1, string2, [ start_position ], [ nth_appearance ] )

instr('Tech on the net', 'e') would return 2; the first occurrence of 'e' 
instr('Tech on the net', 'e', 1, 1) would return 2; the first occurrence of 'e' 
instr('Tech on the net', 'e', 1, 2) would return 11; the second occurrence of 'e' 
instr('Tech on the net', 'e', 1, 3) would return 14; the third occurrence of 'e' 
instr('Tech on the net', 'e', -3, 2) would return 2.

length( string1 )

length(NULL) would return NULL. 
length('') would return NULL. 
length('Tech on the Net') would return 15. 
length('Tech on the Net ') would return 16.

translate( string1, string_to_replace, replacement_string

translate('1tech23', '123', '456); would return '4tech56' 
translate('222tech, '2ec', '3it'); would return '333tith'

replace('123123tech', '123'); would return 'tech' 
replace('123tech123', '123'); would return 'tech'

replace('222tech, '2', '3'); would return '333tech' 
replace('0000123', '0'); would return '123' 
replace('0000123', '0', ' '); would return '    123'

concat( string1, string2 )

concat('Tech on', ' the Net'); would return 'Tech on the Net'. 
concat('a', 'b') would return 'ab'. 
'Tech on'|| ' the Net'  would return 'Tech on the Net'. 
'a'|| 'b' would return 'ab'.

emp_no        emp_name                     birth_date
----------          -----------------------------      ----------------
10                    ali ahmed ali                   20-01-1970
20                    Admin05 05 05                20-02-1976 
30                    ahmed moheme             20-01-1960 
40                   50dfgdr 50     20-10-1950

انتهى الدرس