This content originally appeared on DEV Community and was authored by technonotes-hacker

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.

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

This content originally appeared on DEV Community and was authored by technonotes-hacker

Print Share Comment Cite Upload Translate Updates

APA

technonotes-hacker | Sciencx (2024-11-01T22:38:38+00:00) SQL – Dia 7. Retrieved from https://www.scien.cx/2024/11/01/sql-dia-7/

MLA

" » SQL – Dia 7." technonotes-hacker | Sciencx - Friday November 1, 2024, https://www.scien.cx/2024/11/01/sql-dia-7/

HARVARD

technonotes-hacker | Sciencx Friday November 1, 2024 » SQL – Dia 7., viewed ,<https://www.scien.cx/2024/11/01/sql-dia-7/>

VANCOUVER

technonotes-hacker | Sciencx - » SQL – Dia 7. [Internet]. [Accessed ]. Available from: https://www.scien.cx/2024/11/01/sql-dia-7/

CHICAGO

" » SQL – Dia 7." technonotes-hacker | Sciencx - Accessed . https://www.scien.cx/2024/11/01/sql-dia-7/

IEEE

" » SQL – Dia 7." technonotes-hacker | Sciencx [Online]. Available: https://www.scien.cx/2024/11/01/sql-dia-7/. [Accessed: ]

rf:citation

» SQL – Dia 7 | technonotes-hacker | Sciencx | https://www.scien.cx/2024/11/01/sql-dia-7/ |

Please log in to upload a file.

There are no updates yet.
Click the Upload button above to add an update.

You must be logged in to translate posts. Please log in or register.