Character Function


Character  functions
1)      Concat()
         It concatenates two strings
        Select concat(‘hello’,’world’) from dual;
          o/p :  helloworld
2)      Substr()
     Maximum 3 arguments
  Select substr(‘helloworld’ ,2,3)from dual
  o/p :: el
3)      Instr()
It gives you portion of character
Select instr(‘helloworld’,’l’) from dual;
o/p:: 3
4)      Lpad()::
Minimum 2 and maximum 3 argument accepted
Select lpad(salary,8) from employees;
 o/p::   -------4000

 select lpad(salary,8,’*’) from employees;
 o/p::  ****4000

5) Rpad()
   
 Select rpad(salary,8) from employees;
o/p ::    4000----
Select rpad(salary,8,’*’) from employees;
 o/p ::  4000****

 6)Trim()::
i)it removes corresponding character in given table
                    ii) case sensitive
 Select last_name, trim(‘k’ from  last_name) from employees;
Input
output
King
ing

  
 i  i) Ltrim()::  at most two arguments. first argument is character and second one is optional
    select last_name ,ltrim(last_name,’t’)from employees;
Input
output
taylor   
aylor   


 ii) Rtirm()::
  select last_name  rtrim(last_name,’r’)from employees;
Input
output
taylor   
taylo   



7)Replace()::
---- replaces the strings, number
 --It takes 3 argument.
---all 3 arguments are necessary for execution
--- all  strings are case sensitive.
Syntax::: replace(string to be searched,search key, replacemeny string)
 Eg::  select last_name,replace(last_name, ’el’,’ola’) from employees;
Input
output
olaa
olaa

explanation::
i)  search key is el it will search in all records whole ‘el ‘ string and make changes respectively

8) translate()::
  It Translate specific  strings,number
---it takes 3 argument
--- case sensitive..
Syntax::  translate(string1,string to replace,replacement string)
Eg) select first_name,translate(first_name,’aljojd’,’pkgph’)

o/p:: what all letters will be there in “string to replace field” all letter separately ie.. a,l,j,o,I,d  will be checked in records and corresponding letter will be translated.
 Ie.. a àp, Làk,iàg and so on;
Where in replace() it will consider as whole string






9)initcap()
  It converts first letter of record in upper case
Select initcap(first_name) from employees;
Input
output
taylor
Taylor

10)upper()
 It converts all records in upper case
 Select upper(first_name) from employees;
Input
output
taylor
TAYLOR


11)lower()
 It converts all records in lower case
 Select lower(first_name) from employees;

Input
output
TAyloR
taylor



12)length()
    Gives length of the string
   Select length(first_name) from employees;
Input
output
geeta 
5





Arithmatic functions

Abs()
--returns absolute values
--- In case there are records in salary in negative it will return positive result as we are using absolute function
--select abs(salary) fom employees;
 
Input
output
1.45   
1.45

Ceil()
I              it returns smallest integer greater than or equal to its argument
               Floor()
               It will return smallest integer less than or equal to its argument
              Select floor(salary) from employees;
Input
Output ceil()
Output floor()
1.1
2
1
3.8
4
3
-1.2
-1
-2
-2.9
-2
-3
-154.00
-154
-154
-154.11
-154
-155

Ln():: it will return natural log
Query::
Select f_no,ln(f_no) “natural log” from math;


Mod():
 It returns remainder of specified column
Eg) select mod(7,2) from dual;

Mod()
Output
7,2
1
2,7
2
-4,-2
0
1.5,5.0
0


Sqrt ()::
----  Returns squareroot of given records.
----cannot handle negative values as their value are undefined
Syntax::
 Select sqrt(4)from dual;
Input
Output
4
2
-25
error
To handle such issues we can make use of absolute functions
And select sqrt(abs(-2.5))from employees;



Comments

Post a Comment

Popular posts from this blog

Dependency Management - Dependencies in Server-Side PL/SQL

Dependency Management - Dependencies in Server-Side PL/SQL If you’re working with server-side PL/SQL programs, you can use the server’s data dictionary to explore usage relationships in quite a bit of detail. Here’s a simple illustration of this rule in action, using the data dictionary to give us eyes into the database. Let’s say that you have a package named bookworm on the server. In this package is a function that selects from the books table. If you create the table and then create the package, expect to see the following: SQL> SELECT object_name, object_type, status 2 FROM USER_OBJECTS 3 WHERE object_name = 'BOOKWORM'; OBJECT_NAME OBJECT_TYPE STATUS ------------------------------ ------------------ ------- BOOKWORM PACKAGE VALID BOOKWORM PACKAGE BODY VALID That is, there are two objects with the name BOOKWORM; the first is the package spec and the second is the body. ...
Read more

Dependencies Among Schema Objects

Image
Dependencies Among Schema Objects The definitions of some objects, including views and procedures, reference other objects, such as tables. As a result, the objects being defined are dependent on the objects referenced in their definitions. This chapter discusses the dependencies among schema objects and how Oracle automatically tracks and manages these dependencies. This chapter contains the following topics: Introduction to Dependency Issues Resolution of Schema Object Dependencies Object Name Resolution Shared SQL Dependency Management Local and Remote Dependency Management Introduction to Dependency Issues Some types of schema objects can reference other objects as part of their definition. For example, a view is defined by a query that references tables or other views. A procedure's body can include SQL statements that reference other objects of a database. An object that references another object as part of its definition is called a dependent object, while ...
Read more

Dependency Injection in PL/SQL – Remove Hard Coded Dependencies from your code

Image
Dependency Injection in PL/SQL – Remove Hard Coded Dependencies from your code Frequently our code has dependencies. Some are intrinsic dependencies, like the packages we need to invoke and the type definitions we rely on. Other dependencies are more like configuration settings, not really an intrinsic part of the code at all. Like the parameters in a discount calculcation or the name of the file our Logging component should write or the URL at which a WebService can be accessed. In this article we briefly discuss the Dependency Injection design pattern and continue to investigate a possible implementation in PL/SQL. The Dependency Injection design pattern has been introduced by Martin Fowler – see for example Inversion of Control Containers and the Dependency Injection pattern at http://www.martinfowler.com/articles/injection.html – and can be seen as a specialization of the Inversion of Control pattern. Dependency Injection is about the principle of separating configuration from us...
Read more