Skip to main content

String Functions

                                      STRING FUNCTIONS


➤Lower:-This function will return character,with all letter in lower case.
                       
     Example: SELECT LOWER('ANKIT') FROM DUAL;

NOTE:- "DUAL is demo table in SQL which contain one Row and one Column."

➤INITCAP:-This function will return string with the first letter in upper case.
             
     Example:SELECT INITCAP('ANKIT') FROM DUAL;

➤UPPER:-This function will return,with all leter to uppercase.

     Example:SELECT UPPER('Ankit') FROM DUAL;

➤SUBSTR:-This function will return the selected charcter from the string or a portion of charcter by mentioning the position of the character in the string.The starting charcter will be 'm' and exceeding upto 'n' result upto the end character.

     Example:SELECT SUBSTR('SECURE',2,4) FROM DUAL;

➤LTRIM:-This function will remove character from the left in the string.With initia character removed upto first character not in set.

     Example:SELECT LTRIM('ANKIT','A') FROM DUAL;

➤RTRIM:-This function will remove character from the right in the string.With final character removed after the last character not in set.

     Example:SELECT RTRIM('ANKIT','T') FROM DUAL;

➤LPAD:-This function will return the character left padded to length 'n'.
                    
     Example:SELECT LPAD('ANKIT',10,'*') FROM DUAL;

➤RPAD:-This function will return character right padded to length 'n' with the character in 'char2', replicated as many times as necessary.If 'char2' is omitted right-pad with blanks.

     Example:SELECT RPAD(NAME,10,'X') FROM CLIENT_MASTER WHERE                               NAME='ANKIT';                   
Labels:

Comments

Post a Comment

Popular posts from this blog

DDL Queries

                        DDL(DATA DEFINITION LANGUAGE)             DDL  refers to "Data Definition Language", a subset of SQL statements that change the structure of the database schema in some way, typically by creating, deleting, or modifying schema objects such as databases, tables, and views.    ➤CREATE ➤ALTER ➤DROP ➤TRUNCATE   ➤RENAME ➥ Create Query:- ➧     CREATE TABLE EMP(EMPID NUMBER(5),NAME VARCHAR2(15), SALARY  NUMBER(7,2),EMAIL VARCHAR(20)); ➧    CREATE TABLE DEPT AS SELECT * FROM DEPARTMENTS; "This Query will Create Table when we want to copy the data of any table into new table.Here old table is DEPARTMENTS. ➥ Alter Query :-  ALTER' COMMAND IS USED TO MODIFY TABLE STRUCTURE. ➧     ALTER TABLE EMP MODIFY EMPID NUMBER(10);  "this will change the data type of any attribute in the table." ➧ ALTER TABLE EMP RENAME COLUMN NAME TO FIRST_NAME; ➧  ALTER TABLE EMP DROP COLUMN HIRE_DATE;
Post a Comment
Read more

Constraints

                                    CONSTRAINTS Constraints is a rule which enforce entry of only valid data in table. Constraints prevent the deletion of a table if there are dependencies. Types of Constraints:-   ➥ PRIMARY KEY= (unique + not null)   ➥ FOREIGN KEY   ➥ NOT NULL   ➥ UNIQUE   ➥ CHECK ➧PRIMARY KEY:- Specifies the column that uniquely identifies a row in the table. The identified columns must be defined as NOT NULL.  ➧FOREIGN KEY:- Specifies that the values in the column must correspond to values in a referenced primary key or unique key column. ➧NOT NULL:- Specifies that this column cannot hold NULL values. ➧UNIQUE:- Specifies that values in the column must be unique. Column can contain NULL value. ➧CHECK:- Specifies Domain for Column values. A CONSTRAINT can be one of the following: * A column-level constraint * A table-level constraint ➮ Primary Key: SQL> CREATE TABLE EMP(ID NUMBER(3) PRIMAR
Post a Comment
Read more

Aggregate And Numeric Functions.

                   Aggregate And Numeric Functions SQL has many built-in functions for performing processing on string or numeric data. ➥ AGGREGATEE FUNCTIONS:-    ➧ Average function : Returns average value of 'n', ignoring null values.         Example: SE LECT AVG(sell_price) from product;      ➧ COUNT  :Counts rows in a specified table or view.        Example: SELECT    COUNT ( * ) FROM products ;     ➧ MIN  : G ets the minimum value in a set of values.         Example: SELECT MIN ( unitsinstock ) FROM   products ;     ➧ MAX  : Gets the maximum value in a set of values.         Example: SELECT    MAX ( unitsinstock ) FROM    products ;     ➧ SUM  :Ca lculates the sum of values       Example:SELECT  SUM(bal_due) from client_master;         ➥ NUMERIC FUNCTIONS:- ABS(number) Returns the absolute positive value of an expression. Syntax : ABS(expression) Example : SELECT ABS(-1.0), ABS(0.0), ABS(1.0) Output : 1.0   .0   1.0 CEIL(numb
Post a Comment
Read more