String
Functions in Oracle SQL
POC done By Rajesh
create table customer(
cust_id number primary key,
cust_name varchar2(50),
loc varchar2(20),
mobile_no number not null unique,
item_id number,
item_name varchar2(50),
total_items number,
total number
);
desc customer;
INSERT ALL
INTO CUSTOMER VALUES (1, 'Amit Sharma', 'Mumbai', 9800000001, 5001,
'Laptop', 1, 55000)
INTO CUSTOMER VALUES (2, 'Priya Rai', 'Delhi', 9800000002, 5002,
'Smartphone', 2, 40000)
INTO CUSTOMER VALUES (3, 'John Doe', 'Bangalore', 9800000003, 5003,
'Headphones', 1, 2500)
INTO CUSTOMER VALUES (4, 'Sneha V.', 'Pune', 9800000004, 5004,
'Monitor', 1, 12000)
INTO CUSTOMER VALUES (5, 'Rahul K.', 'Chennai', 9800000005, 5005,
'Keyboard', 1, 1500)
INTO CUSTOMER VALUES (6, 'Anita B.', 'Hyderabad', 9800000006, 5006,
'Mouse', 1, 800)
INTO CUSTOMER VALUES (7, 'Vikram S.', 'Kolkata', 9800000007, 5007,
'Tablet', 1, 15000)
INTO CUSTOMER VALUES (8, 'Meera P.', 'Ahmedabad', 9800000008, 5008,
'Printer', 1, 10000)
INTO CUSTOMER VALUES (9, 'Suresh R.', 'Jaipur', 9800000009, 5009,
'Speaker', 1, 3000)
INTO CUSTOMER VALUES (10, 'Deepa M.', 'Lucknow', 9800000010, 5010,
'Webcam', 1, 2500)
INTO CUSTOMER VALUES (11, 'Karan T.', 'Surat', 9800000011, 5011,
'Router', 1, 4500)
INTO CUSTOMER VALUES (12, 'Pooja L.', 'Kanpur', 9800000012, 5012, 'Hard
Drive', 1, 6000)
INTO CUSTOMER VALUES (13, 'Arjun W.', 'Nagpur', 9800000013, 5013,
'Smartwatch', 1, 5000)
INTO CUSTOMER VALUES (14, 'Sonia G.', 'Indore', 9800000014, 5014,
'Camera', 1, 35000)
INTO CUSTOMER VALUES (15, 'Rohan D.', 'Thane', 9800000015, 5015, 'Mic',
1, 2000)
SELECT * FROM dual;
select * from customer;
-------------String
Functions-----------
----substring-----
The contiguous string is the extracted
from the original string.
select substr(cust_name,1,3) from
customer;
select substr(cust_name, 5) from customer;
select substr(cust_name, -4) from
customer;
select substr(cust_name, 1) from customer;
-----INSTRING-------
This in-built string function is used to
deal with position of the string(original).
select instr(cust_name, ' ') as
space_position
from customer
where instr(cust_name, ' ')>0;
select loc, instr(upper(loc),'A') as
pos_of_A
from customer
where instr(upper(loc),'A')>0;
select mobile_no, instr(mobile_no, 98) as
starts_at
from customer
where instr(mobile_no, 98)>0;
----TRIM----
To remove the unwanted characters from the
string given.
We have two types of trims:
1. Ltrim:
This trim is used to trim from the left side characters which are specified
from the string.
2. Rtrim:
This trim is used to trim the right side characters which are specified from
the string.
select trim(cust_name) from customer;
select ltrim(cust_name) from customer;
select Rtrim(mobile_no, 98) from customer;
select ltrim(mobile_no, 90) from customer;
----PADDING-----
Padding is the process of adding specific
characters(like zeros, characters, symbols) to a string to make it reach a
fixed, desired length.
There are two types of paddings:
1. LPAD:
This adds characters to the start(left-side) of the string. It is commonly used
for aligning numbers or masking sensitive data.
2. RPAD:
This add characters from right-side of the string.
SELECT LPAD(mobile_no, 5, '*') AS mobile,
CUST_NAME
FROM customer;
select rpad(cust_id, 2, '.') from
customer;
----ORDERBY----
It is used to sort the result set of the
query in either ascending or descending order.
SELECT LOC, CUST_NAME
FROM CUSTOMER
ORDER BY LOC ASC;
SELECT CUST_NAME, ITEM_NAME, TOTAL
FROM Customer
ORDER BY TOTAL DESC;
-----GROUPBY-------
A sql command used to bundle rows together
that have the same values in specific values.
Groupby function is used mostly for
aggregate functions(max, min, count, avg).
SELECT LOC, SUM(TOTAL) AS TOTAL_REVENUE,
SUM(TOTAL_ITEMS) AS ITEMS_SOLD
FROM CUSTOMER
GROUP BY LOC;
SELECT CUST_NAME, AVG(TOTAL) AS AVG_SPEND
FROM CUSTOMER
GROUP BY CUST_NAME;
No comments:
Post a Comment