Part 2 SQL functions- character function -case manipulation functions
Previous Video Link :: http://youtu.be/5rx8Q4x4-qI
Tool used in this tutorial is command prompt.
This tutorial series is part of SQL expert exam certification training. if you are preparing for SQL certification you can use my tutorials. This SQL Tutorial is a part of free training.
Copy Cloud referral link || Use this link to join copy cloud and get 20GB of free storage
Thanks for linking, commenting, sharing and watching more of our videos
This is Manish from RebellionRider.com
As discuss in last video we have 3 case manipulation functions.
In case you miss the SQL functions intro video you can find the link in the description below.
First let's see what these functions are.
Upper (): This function takes a parameter converts it into uppercase and returns the new string.
Initcap (): this function also takes parameter and converts the initial letter of or parameter into uppercase and returns the new string.
Lower (): This function takes a parameter converts it into lowercase and returns the new string.
Ok let's do some practical
For this practical we will be using 2 tables
First is dual table
Which is a dummy table provided by oracle?
And I have created another table again by the name of example. This will be our second table.
Let's start with SQL function upper ( );
SELECT upper('hello world') FROM dual;
As you can see I have used "hello world" completely in lowercase as parameter to SQL upper function.
Here, our result has given us a completely new hello world string which is in uppercase.
Similarly you can also use SQL upper function to perform other DML
This time we will work on our second table EXAMPLE;
insert into example values (upper('manish'));
first parentheses for values and second for SQL function upper.
Let's check the result. As you can see here inserted value is in upper case.
Lets compare it with simple insert query
Insert into example values ('manish');
Execute it, i think now it's clear that when we perform simple insert DML the inserted value will be the same you provide.
Similarly let's try SQL function initcap();
SELECT initcap('hello world') FROM dual;
As you see here in result the initial letters of string hello world are in uppercase.
Let's do the insert query
insert into example values (initcap('hello world'));
initial letters of string also are in uppercase.
Let's try SQL function lower();
This time we have to write our hello world in all caps.
SELECT lower('HELLO WORLD') FROM dual;
String totally in lower case. Similarly you can user SQL function lower in other DML like insert and all.
Hello Manish, I've watched a lot of your videos in this chenel. but I have an unresolved problem. I want to know how to create a capital character at the end of a name. for example: initcap (first_name) the result -> "Steven". but how to make the letter "N" in the name Steven into capital letters. the result "SteveN" ??
thanks Manish, keep on making tutorials to help everyone :-).
while running the below sql command create table example(column varchar2(30)); I am getting the below error
Error starting at line : 13 in command -
create table exemp
Error report -
SQL Error: ORA-00904: : invalid identifier
00904. 00000 - "%s: invalid identifier"
However if I remove the column by col the query is executed successfully and the table is created. Can you tell me what is wrong with the "column"
+RamaKrishna Arikatla Your comment has become the star comment of the day :-) and is being featured with #MorningMotivation on all my social media. Make sure to visit and Check
My Twitter: https://twitter.com/RebellionRider
My Facebook: https://www.facebook.com/imthebhardwaj
Hi Manish i'm preparing for 1z0-051 exam do you have any suggestion for that exam?booked the exam for next week but don't know if i'm fully prepare. Can you please help me to know the keys I' mean what are the topics cover in the exam.
For towns, each building is described, along with what and who can you can talk to, who to buy skills from, and what quests are available. For the outlying areas, the dungeons are listed.
Dungeon maps are not given -- they would be too extensive to fit easily into a web page and the automapping in the game is excellent. Also, every dungeon should be explored completely to get all of the loot, but only puzzles and hidden locations are described. I also skip most of the fighting because it isnt something that you can easily describe, nor does it matter in most places, except that you have to survive it. I do list the creatures that you will encounter in a dungeon or grid location to give you an idea of how difficult the location is.
Stores are listed with a "buy" and "sell". The "buy" value is multiplied by the items value to determine the price you have to pay for it. The "sell" value is divided by the items value to determine the price you can sell it to the store for. Higher is always worse, and a "buy" or "sell" of 1 means that you are buying/selling an item at cost.
Every location has a "reset" timer. This starts when you first enter the area, and after it "goes off", the entire grid square resets: monsters reappear and random treasure is replaced. Nonrandom treasure (including most stat-gaining liquids) is not replaced. All dungeons have a reset of 2 years (24 months), unless otherwise noted. Overland areas have reset times listed with their descriptions.
Artifacts are unique items that can be found. They come in two flavors: Minor artifacts are always benificial and have a value of 20000gp. Major artifacts always have a drawback, but their benificial powers are much stronger. They have a value of 30000gp. There are 15 minor and 15 major artifacts -- some of these artifacts are placed at specific locations; others are randomly generated.
Table of Contents.