5 TIPS AND TRICKS WITH SQL

SQL is a very interesting language which makes us handle the data really well and has many built in functions to facilitate the same.

Here are 5 terms and tricks that I learnt with my experience in working with SQL.

“TO_DATE” and “TO_CHAR” functions

Ever wondered if we wrote the below queries, what will the difference be?

1.      SELECT SYSDATE FROM DUAL;

2.      SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY') FROM DUAL

3.      SELECT TO_DATE(SYSDATE ,'DD/MM/YYYY') FROM DUAL;

1st and 3rd will actually give same values as below:

22-MAY-17

What is the point of mentioning the format in the TO_DATE function then?

TO_DATE function basically checks if the date entered in the first parameter can be converted to the mentioned format or no. Suppose, if you give a number instead of date like this:

SELECT TO_DATE (‘11’,’DD/MM/YYYY’) FROM DUAL;

It will throw error.

While, 2nd gives you the date in the required format:

22/05/2017

This basically converts the first parameter to the given format if it is capable of same.

If the format isn’t possible, it throws error.

NULL and ‘blank space’

Have you ever tried inserting a blank to a column and then query it for being null?

Let us look at some example.

CREATE TABLE IPL_2017

 (PLAYER_NAME VARCHAR2(50), SCORE NUMBER (10) );

Now, we will insert two rows as below:

1.      INSERT INTO IPL_2017 VALUES('CHRIS GAYLE', '');

2.      INSERT INTO IPL_2017 VALUES('HARBHAJAN SINGH',NULL);

Now, when you query for '' data like this:

SELECT * FROM IPL_2017 WHERE SCORE ='';

No rows are populated.

But, if you query this:

SELECT * FROM IPL_2017 WHERE SCORE IS NULL;

Both the rows will appear.

Basically, Oracle considers both as same but you cannot select a '' value with a “is null”.

SUBSTR and INSTR functions

SUBSTR, as name suggests, is used to take out a substring from a string. It gives you certain number of characters from a string which is passed as a parameter.

Examples:

Multiple things are possible with SUBSTR:

1.      Take out characters from a position till the number of characters mentioned from there.

For e.g.: “I am writing an article” is the string.

SUBSTR (“I am writing an article”, 3, 5) – Here 3 is the starting position and 5 is the number of characters.

Result will be: “am wr”, space is considered a character too.

2.      Take out characters starting from a position till the end.

For e.g.: “I am writing an article” is the string.

SUBSTR (“I am writing an article”, 3) – Here 3 is the starting position and since no number of characters are defined, it returns the whole string from there.

Result will be “am writing an article”

3.      Take substring starting from the end of the string.

For e.g.: “I am writing an article” is the string.

SUBSTR (“I am writing an article”, -3, 2) – Here -3 is nothing from 3 characters from the end for 5 characters.

Result will be: “cl”

4.      If length i.e. the last parameter is given as negative, then NULL is returned.

INSTR function is used to give the “position” of a substring from a string. Unlike how we extract the substring itself using the SUBSTR function, this returns the position of the same.

Few scenarios:

1.      Get the position of a mentioned substring from a mentioned string starting from a mentioned position.

INSTR (“Banana”, “na”,1,2)

Search for “na” starting from 1st position but return the second occurrence of the substring position.

Possible positions of “na” are: “3” and “5”. Since the 4th parameter is “2” so “5” will the output.

2.      If we do not give the 3rd and 4th parameter or if we give “1” and “1” as the 3rd and 4th parameter, it is treated as same.

INSTR (“Banana”,” na”,1,1) – 3

INSTR (“Banana”,” na”) – 3

3.      If the start position is negative, then search starts from behind by the number of characters mentioned and searches for the string towards the beginning.

INSTR (“Banana”,”na”, -3,1) – 3. It started from last 3rd character “a” towards the beginning to find the 1st occurrence of the substring which is 3.

4.      If the substring isn’t found, 0 is returned.

NOTE: Output is “null” when substring is not found while using SUBSTR but “0” is returned when substring is not found while using INSTR.

LTRIM and RTRIM

Generally, there is a notion that these functions are used to remove spaces from the mentioned string from left or right side of the string. But that’s not the only thing its used for.

It can be used to remove the mentioned patterns from the string too.

Few examples to explain these functions with the results returned from the queries:

1.      SELECT LTRIM ('   HI',' ') FROM DUAL; - ‘HI’

2.      SELECT LTRIM ('   HI') FROM DUAL; - ‘HI’

3.      SELECT RTRIM ('   HI',' ‘) FROM DUAL; - '   HI'

4.      SELECT LTRIM('HZHIHI','H') FROM DUAL; - ‘ZHIHI’

5.      SELECT RTRIM('HZH','H') FROM DUAL; - ‘HZ’

6.      SELECT RTRIM('HZHIHI','HI') FROM DUAL; - ‘HZ’

As it is very clear from the examples above, depending on LTRIM or RTRIM, the pattern is taken as characters and searched from left or right, respectively and is used to remove it till it finds any other character in the string. If no pattern is given, space is considered as the pattern and string is treated using the same.

Delete duplicate rows from a table

There can be multiple criteria before deleting a row in a table. Most common one is depending on a column value which can be done by using a simple WHERE clause.

Suppose, if you have a scenario in which you must delete the duplicate row, keeping only one row out of the duplicates.

We need to use the row id for the same. like below:

DELETE FROM    Table_name A

WHERE

 a.rowid >    ANY (

    SELECT         B.rowid

    FROM Table_name B);

Similarly, if you want to delete only the rows which have duplicates for only particular columns.

Suppose, below is the data in a table:

We need to delete the rows with same scores, only one row should remain:

DELETE FROM

  IPL_TABLE A WHERE

 a.rowid >

  ANY (SELECT     B.rowid

    FROM  IPL_TABLE B

    WHERE

       A.runs_scored = B. runs_scored);

Above mentioned are very simple things that I came across while working on SQL. If you go deeper in PL/SQL or complicated SQL queries, there is much more.