technonotes-hacker

Posted on Nov 1

SQL - Dia 7

Single Row Function

7 types
Case Manipulation Function : upper , lower & Initcap
Character Manipulation : Instr , Substr , Length , Replace , Reverse , Translate , Trim , Ltrim , Rtrim , Lpad & Rpad.
Number
Date Function
General Function
Null
Conversion

Initcap

First letter in caps

> -- select * from HR.COUNTRIES;
> -- select * from HR.COUNTRIES where COUNTRY_NAME='BRAZIL'
> -- select * from HR.COUNTRIES where COUNTRY_NAME=Initcap('BRAZIL')
> -- select *, UPPER(COUNTRY_NAME) from HR.COUNTRIES where COUNTRY_NAME=Initcap('BRAZIL')
> -- select HR.COUNTRIES.*, UPPER(COUNTRY_NAME) from HR.COUNTRIES where COUNTRY_NAME=Initcap('BRAZIL')
> -- select HR.COUNTRIES.*, UPPER(COUNTRY_NAME) AS COUNTRY from HR.COUNTRIES where COUNTRY_NAME=Initcap('BRAZIL')
> select C.*, UPPER(COUNTRY_NAME) AS COUNTRY from HR.COUNTRIES C where COUNTRY_NAME=Initcap('BRAZIL')

Instr

Tells the number of the Position.
To find Letter position.
Number as Output.
Number wont change when to count from front or reverse.
1 -- > Front
-1 --> Reverse
Last one in the bracket is *OCCURRENCE *.
instr(letter, which one you need , front or reverse , occurrence )

-- select 'APPLEE' FROM dual;
-- select INSTR('APPLEE','P',1,1) FROM dual;
select INSTR('APPLEE','P',1,2) FROM dual;
select INSTR('APPLEE','P',-1,2) FROM dual;

Substr

Character as output.
substr ( 'letter' , start , how many character/letter u need to be displayed )
substr ( 'letter', 2 ) --> it will print all letters after 2.
'-' reverse.

select substr('APPLEE',3,2) FROM dual;
select substr('APPLEE',-3,2) FROM dual;

Length

To find the length of the character.
For NULL there is no length.
Space will be considered.

-- select length('brettlee') from dual;
-- select DEPARTMENT_NAME, length(DEPARTMENT_NAME) as no_of_letters from hr.DEPARTMENTS;
select '',length(''),length(null) from dual;
select '',length(' '),length(null) from dual;

Reverse

Numbers,it won't accept for reverse function.

Replace

replace the word.
replace('word','exact_word','replace_with_this');
If the second argument is not present in the first then replace won't work.
word to word we need to use 'Replace'.
character/letter to character/letter use 'Translator'.

-- select replace('hcl technology','technology',techno) from dual;
-- select replace('hcl technology','technology','techno') from dual;
-- select replace('hcl technology','soft','techno') from dual;
-- select replace('hcl technology','technology') from dual;
select translate('hcl','hl','ts') from dual;

Trim

space can be trimmed.

Lpad

to add.
To add in both sides that's the task.

-- select lpad((rpad('infosys','9','&')),'11','&') from dual;
select rpad(lpad('INFOSYS',length('INFOSYS')+2,'*') ,length(lpad('INFOSYS',length('INFOSYS')+2,'**'))+2,'*') from dual;

Notes

dual is a dummy table
always single quotes for string.
Data in the table is sensitive --> always remember.
Query you can write in any format but data is sensitive.
Can we put , after * ? Yes , you need to put the table name in the * then there wont be error.
AS is the column name or alternate name.
AS can be used only in the select column.
AS can't be used for table.
Alias name won't be saved in database.
Alias = Alternate Name
String = Group of letters

Top comments (2)

Boopathi • Nov 2

This is a really helpful breakdown of SQL functions! It's clear and concise, and the examples are very useful. I'm especially interested in learning more about the REPLACE and TRANSLATE functions.