SQL DATA MANIPULATION

Hi there 👋

You’re wandering what’s this all about so first let me enlightened you. i will be creating a blog everyday from Day 1 to Day 100 to share to you guys what i’ve learned for that day. this journey will focus on data specifically in sql so let’s start.

Topic for today are:

• SQL LEFT FUNCTION For efficient Data Extraction.
• SUBSTRING or SUBSTR
• trunc function

So now you’re also wandering again that i am not start in the basics of the sql like the background or syntax i jump from the data manipulations so we will go there. This is Day 0 its like a cannon episode haha. enough with the chichat let’s start.

SQL LEFT FUNCTION
left function is extracting character from a column there’s a two argument that needed in performing an left function.

LEFT(column_name, number)

The first argument column_name specifies the column from which you want to extract characters.

The second argument which is number determines the number of characters you want to extract from the left side of the specified column’s value.

For Example:
We have Product table
| id | name | description |
|—-|————|————————–|
| 1 | Product A | Description of Product A |
| 2 | Product B | Description of Product B |
| 3 | Product C | Description of Product C |

So if we want to limit the output of the description because its too long then we can use LEFT function to limit the characters of the description column. Lets limit the the fetch description in 5 characters only. this is the sample query for that.

SELECT name, LEFT(description, 5) AS description FROM Product

then the output of that is this
| name | description |
|————|————-|
| Product A | Descr |
| Product B | Descr |
| Product C | Descr |

you see the description value is cut in 5 characters so thats the uses of left function, We also have RIGHT its same as left but right counting element is start in the right value of the column name.

SUBSTRING() OR SUBSTR()
This function is same on the left function but the substring requires 3 argument.

SUBSTRING(column name, start(n), end(n));

• column name: Column name or string that you want to extract.
• start(n): The starting position from which you want to begin extracting characters.
• end(n): The length of the substring you want to extract

It’s easy right?

now let’s make a query, Here’s the sample Product Table.

SELECT name, SUBSTRING(description, 11, 5) AS description FROM Product;

THE OUTPUT:
| name | description |
|————|————- |
| Product A | of Pr |
| Product B | of Pr |
| Product C | of Pr |

Explanation:
We start the extraction in the 11 char of the description column then the length of the extraction is 5 so we count five starting from element char we also included the spaces.

TRUNC()
The TRUNC() function is used to truncate a date or a number to specific unit or measure. its something similar for ROUND but the ROUND is rounding a number in TRUNC we’re just eliminating the number or decimal.

TRUNC function accept two arguments first is the column name containing number or dates, but the second argument is like optional so we can exclude or includes that its depends on the situation.

Sample query
This is the 3 different example of trunc() function

TRUNC(salary) — removing the decimal for salary column
ex: 123.00 -> 123

TRUNC(salary, 2) — 123.18730 -> 100.18700

TRUNC(salary, -2) — 123.18730 -> 100.00000

Utilizing the TRUNC() function to group numbers into specific ranges like thousands or hundreds can greatly facilitate data analysis and interpretation.

Nice, now that you learned how to use the LEFT(), SUBSTRING(), TRUNC try to apply it in your project and explore it a little bit more advance.

If you guys have questions or clarifications regarding on my blog post please do a comment and share your thoughts about it and then we can discuss it.

If you enjoy this type of content and would like to join me on my journey of personal growth in SQL, please hit the like button

*Thank you so much and have a nice day! *