t/SQL/SQLScalar1.sql> REM test.sql
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> create table test1 (col1 char, col2 number, col3 char, col4 char);
Create Table : test1
tablename : test1
column col1 : c
column col2 : n
column col3 : c
column col4 : c
table test1 created
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> insert into test1 values ('aa', 1, 'I have trailing spaces ', 'AAA',
> 'bb', 2, ' and many leading spaces too ', 'BBB',
> 'cc', 3, ' and special chars like %^&*() ' , 'CCC' );
inserted 3 rows into table test1.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select * from test1;
col1 col2 col3 col4
____ ____ ____ ____
aa 1 I have trailing spaces AAA
bb 2 and many leading spaces too BBB
cc 3 and special chars like %^&*() CCC
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> REM perl functions
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select chomp(col1)||chomp(col3)||col1 from test1;
chomp(col1)||chomp(col3)||col1
______________________________
aaI have trailing spaces aa
bb and many leading spaces too bb
cc and special chars like %^&*() cc
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select chop(col1)||chop(col3)||col1 from test1;
chop(col1)||chop(col3)||col1
____________________________
aI have trailing spaces aa
b and many leading spaces toobb
c and special chars like %^&*() cc
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select chr(65) from dual;
chr(65)
_______
A
1 row selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> REM FIX
t/SQL/SQLScalar1.sql> select crypt(col3, col1) from test1;
crypt(col3, col1)
_________________
aa5KO//iCgd62
bb3xX2yYycZTU
ccvPDYkTj1Tcw
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select index('defabc', 'abc') from dual;
index('defabc', 'abc')
______________________
3
1 row selected.
t/SQL/SQLScalar1.sql> select index('abc', 'def') from dual;
index('abc', 'def')
___________________
-1
1 row selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select lc(col4) from test1;
lc(col4)
________
aaa
bbb
ccc
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select lcfirst(col4) from test1;
lcfirst(col4)
_____________
aAA
bBB
cCC
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select length(col1) from test1;
length(col1)
____________
2
2
2
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> rem reverse of chr
t/SQL/SQLScalar1.sql> select ord(col1) from test1;
ord(col1)
_________
97
98
99
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select quurl( pack('n/A*',col1)) from test1;
quurl( pack('n/A*',col1))
_________________________
%00%02aa
%00%02bb
%00%02cc
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select reverse(col3) from test1;
reverse(col3)
_____________
secaps gniliart evah I
oot secaps gnidael ynam dna
)(*&^% ekil srahc laiceps dna
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> REM FIX
t/SQL/SQLScalar1.sql> REM select rindex(col1) from test1;
t/SQL/SQLScalar1.sql> select rindex('defabc', 'abc') from dual;
rindex('defabc', 'abc')
_______________________
3
1 row selected.
t/SQL/SQLScalar1.sql> select rindex('abc', 'def') from dual;
rindex('abc', 'def')
____________________
-1
1 row selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select sprintf('number: %d',col2) from test1;
sprintf('number: %d',col2)
__________________________
number: 1
number: 2
number: 3
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select substr(col1, 1, 1) from test1;
substr(col1, 1, 1)
__________________
a
b
c
3 rows selected.
t/SQL/SQLScalar1.sql> select substr(col1, -1) from test1;
substr(col1, -1)
________________
a
b
c
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select uc(col3) from test1;
uc(col3)
________
I HAVE TRAILING SPACES
AND MANY LEADING SPACES TOO
AND SPECIAL CHARS LIKE %^&*()
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select ucfirst(col1) from test1;
ucfirst(col1)
_____________
Aa
Bb
Cc
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select abs(col2) from test1;
abs(col2)
_________
1
2
3
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> REM FIX
t/SQL/SQLScalar1.sql> select atan2(2,1) from dual;
atan2(2,1)
__________
1.10714871779409
1 row selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select cos(col2) from test1;
cos(col2)
_________
0.54030230586814
-0.416146836547142
-0.989992496600445
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select exp(col2) from test1;
exp(col2)
_________
2.71828182845905
7.38905609893065
20.0855369231877
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select hex(col1) from test1;
hex(col1)
_________
170
187
204
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> REM FIX
t/SQL/SQLScalar1.sql> select int(234.25) from dual;
int(234.25)
___________
234
1 row selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select log10(col2) from test1;
log10(col2)
___________
0
0.301029995663981
0.477121254719662
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> REM FIX
t/SQL/SQLScalar1.sql> select oct('0xAE') from dual;
oct('0xAE')
___________
174
1 row selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> REM need to test rand
t/SQL/SQLScalar1.sql> REM select rand(col2) from test1;
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select sin(col2) from test1;
sin(col2)
_________
0.841470984807897
0.909297426825682
0.141120008059867
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select sqrt(col2) from test1;
sqrt(col2)
__________
1
1.4142135623731
1.73205080756888
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select srand(col2) from test1;
srand(col2)
___________
1
1
1
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select perl_join('howdy', col1, col4, col3) from test1;
perl_join('howdy', col1, col4, col3)
____________________________________
aahowdyAAAhowdyI have trailing spaces
bbhowdyBBBhowdy and many leading spaces too
cchowdyCCChowdy and special chars like %^&*()
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> REM SQL string functions
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select concat(col1, col2, col3) from test1;
concat(col1, col2, col3)
________________________
aa1I have trailing spaces
bb2 and many leading spaces too
cc3 and special chars like %^&*()
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select greatest(col1, col3) from test1;
greatest(col1, col3)
____________________
aa
bb
cc
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select initcap(col3) from test1;
initcap(col3)
_____________
I Have Trailing Spaces
And Many Leading Spaces Too
And Special Chars Like %^&*()
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select initcap('aaaa bbbb aaa bbb aa bb a b') from dual;
initcap('aaaa bbbb aaa bbb aa bb a b')
______________________________________
Aaaa Bbbb Aaa Bbb Aa Bb A B
1 row selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select initcap('hi man how are you,dude,kk*ll123gg&ff') from dual;
initcap('hi man how are you,dude,kk*ll123gg&ff')
________________________________________________
Hi Man How Are You,Dude,Kk*Ll123gg&Ff
1 row selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select initcap('hi man how are you,dude,kk.*()()ll123gg&ff') from dual;
initcap('hi man how are you,dude,kk.*()()ll123gg&ff')
_____________________________________________________
Hi Man How Are You,Dude,Kk.*()()Ll123gg&Ff
1 row selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select least(col1, col3) from test1;
least(col1, col3)
_________________
I have trailing spaces
and many leading spaces too
and special chars like %^&*()
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select lower(col3) from test1;
lower(col3)
___________
i have trailing spaces
and many leading spaces too
and special chars like %^&*()
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select 'XX'||lpad(col1, 11, 'zz')||'XX' from test1;
'XX'||lpad(col1, 11, 'zz')||'XX'
________________________________
XXzzzzzzzzzaaXX
XXzzzzzzzzzbbXX
XXzzzzzzzzzccXX
3 rows selected.
t/SQL/SQLScalar1.sql> select 'XX'||ltrim(col3)||'XX' from test1;
'XX'||ltrim(col3)||'XX'
_______________________
XXI have trailing spaces XX
XXand many leading spaces too XX
XXand special chars like %^&*() XX
3 rows selected.
t/SQL/SQLScalar1.sql> select ltrim('abababcdcddceeeeabababcdcddc', 'abcd') from dual;
ltrim('abababcdcddceeeeabababcdcddc', 'abcd')
_____________________________________________
eeeeabababcdcddc
1 row selected.
t/SQL/SQLScalar1.sql> select replace(col3, 'a', 'REPLACE') from test1;
replace(col3, 'a', 'REPLACE')
_____________________________
I hREPLACEve trREPLACEiling spREPLACEces
REPLACEnd mREPLACEny leREPLACEding spREPLACEces too
REPLACEnd speciREPLACEl chREPLACErs like %^&*()
3 rows selected.
t/SQL/SQLScalar1.sql> select 'XX'||rpad(col1, 11, 'zz')||'XX' from test1;
'XX'||rpad(col1, 11, 'zz')||'XX'
________________________________
XXaazzzzzzzzzXX
XXbbzzzzzzzzzXX
XXcczzzzzzzzzXX
3 rows selected.
t/SQL/SQLScalar1.sql> select 'XX'||rtrim(col3)||'XX' from test1;
'XX'||rtrim(col3)||'XX'
_______________________
XXI have trailing spacesXX
XX and many leading spaces tooXX
XX and special chars like %^&*()XX
3 rows selected.
t/SQL/SQLScalar1.sql> select rtrim('abababcdcddceeeeabababcdcddc', 'abcd') from dual;
rtrim('abababcdcddceeeeabababcdcddc', 'abcd')
_____________________________________________
abababcdcddceeee
1 row selected.
t/SQL/SQLScalar1.sql> select soundex(col3) from test1;
soundex(col3)
_____________
I136
A535
A532
3 rows selected.
t/SQL/SQLScalar1.sql> select translate(col3, 'abcdefghijklmnopqrstuvwxyz',
> '~!@#$%^&*()--+=[]{};:<>012')
> from test1;
translate(col3, 'abcdefghijklmnopqrstuvwxyz',
'~!@#$%^&*()--+=[]{};:<>012')
____________________________________________________________________________________________________
I &~;$ {[~***,^ ]+~@$]
~,# +~,> *$~#*,^ ]+~@$] {--
~,# ]+$@*~* @&~[] **)$ %^&*()
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select upper(col1) from test1;
upper(col1)
___________
AA
BB
CC
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> REM SQL math functions
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select cosh(col2) from test1;
cosh(col2)
__________
1.54308063481524
3.76219569108363
10.0676619957778
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select ceil(col2) from test1;
ceil(col2)
__________
1
2
3
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select floor(col2) from test1;
floor(col2)
___________
1
2
3
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select ln(col2) from test1;
ln(col2)
________
0
0.693147180559945
1.09861228866811
3 rows selected.
t/SQL/SQLScalar1.sql> select logN(10, 100) from dual;
logN(10, 100)
_____________
2
1 row selected.
t/SQL/SQLScalar1.sql> select mod(22, col2) from test1;
mod(22, col2)
_____________
0
0
1
3 rows selected.
t/SQL/SQLScalar1.sql> select power(col2, 10) from test1;
power(col2, 10)
_______________
1
1024
59049
3 rows selected.
t/SQL/SQLScalar1.sql> select round(col2*1.253) from test1;
round(col2*1.253)
_________________
1
3
4
3 rows selected.
t/SQL/SQLScalar1.sql> select round(col2*1.253, 1) from test1;
round(col2*1.253, 1)
____________________
1.3
2.5
3.8
3 rows selected.
t/SQL/SQLScalar1.sql> select round(col2*10.253, -1) from test1;
round(col2*10.253, -1)
______________________
10
20
30
3 rows selected.
t/SQL/SQLScalar1.sql> select sign(col2) from test1;
sign(col2)
__________
1
1
1
3 rows selected.
t/SQL/SQLScalar1.sql> select sign(0) from test1;
sign(0)
_______
0
0
0
3 rows selected.
t/SQL/SQLScalar1.sql> select sign(col2*(-5)) from test1;
sign(col2*(-5))
_______________
-1
-1
-1
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select sinh(col2) from test1;
sinh(col2)
__________
1.1752011936438
3.62686040784702
10.0178749274099
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select tan(col2) from test1;
tan(col2)
_________
1.5574077246549
-2.18503986326152
-0.142546543074278
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select tanh(col2) from test1;
tanh(col2)
__________
0.761594155955765
0.964027580075817
0.995054753686731
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select trunc(col2*1.243) from test1;
trunc(col2*1.243)
_________________
1
2
3
3 rows selected.
t/SQL/SQLScalar1.sql> select trunc(col2*1.243,2) from test1;
trunc(col2*1.243,2)
___________________
1.24
2.48
3.72
3 rows selected.
t/SQL/SQLScalar1.sql> select trunc(col2*11.243, -1) from test1;
trunc(col2*11.243, -1)
______________________
10
20
30
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> REM SQL conversion functions
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select ascii(col1) from test1;
ascii(col1)
___________
97
98
99
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> REM FIX
t/SQL/SQLScalar1.sql> REM select instr(col1) from test1;
t/SQL/SQLScalar1.sql> select instr('abc', 'defabc') from dual;
instr('abc', 'defabc')
______________________
0
1 row selected.
t/SQL/SQLScalar1.sql> select instr('abc', 'def') from dual;
instr('abc', 'def')
___________________
0
1 row selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select nvl(col1) from test1;
nvl(col1)
_________
aa
bb
cc
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> REM Genezzo functions
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select quurl(col3) from test1;
quurl(col3)
___________
I%20have%20trailing%20spaces%20%20%20%20%20%20%20
%20%20%20%20%20and%20many%20leading%20spaces%20too%20
%20%20%20and%20special%20chars%20like%20%25%5E%26%2A%28%29%20%20
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select quurl2(col3) from test1;
quurl2(col3)
____________
I have trailing spaces
and many leading spaces too
and special chars like %25^&*()
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> select unquurl(col3) from test1;
unquurl(col3)
_____________
I have trailing spaces
and many leading spaces too
and special chars like %^&*()
3 rows selected.
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> drop table test1;
dropped table test1
t/SQL/SQLScalar1.sql>
t/SQL/SQLScalar1.sql> commit;
saved tablespace SYSTEM