SQL Part 2: Funky Scalar Function

by Algirdas Grajauskas

Continuing from the previous post

UPPER - Converts everything to capital, you can not use * to capitalize every column name as it will return an error (Ex. 1), therefore it is only possible to upper one column at a time.

Example 1

LOWER - Is the opposite of lower, it ends up putting everything in lower case.

SUBSTRING - Substring checks inside a string of a column field which can then later be used in WHERE to compare if it has the specific characters, as seen in example 2 I was looking for 'wes'

Example 2

REPLACE - Is used to take one value and replace it with another one, it should always be used in the SELECT area in order to be able to see it. (Examples 3 and 4) Allowing you to have some fun interactions, for example Jacksonbanana :)

Example 3
Example 4

LEN - Returns you the length of the value in a text, this can be used to find strings that are longer than a certain amount and return only them. (Example 5)

Example 5

Returns all the sub-categories whose name has more than 8 characters. While the other bits in the query existed there to just spice it up, such as using year() to return year, month() to return month, and using aggregations for quantity.

ROUND - Is used to round numeric fields to the number of decimals specified. Examples 6 and 7

Example 6

Example 7

GETDATE - Think of it as a function which returns the current system date and time. Example 8 and 9

Example 8
Example 9

These are the scalar functions which are commonly used in SQL query.  Thank you for reading.


Algirdas Grajauskas

Fri 29 Jul 2022

Thu 28 Jul 2022

Wed 27 Jul 2022