[DATABASE] CAST function, LIKE operator, FORMAT function in T-SQL
Today I encountered the familiar problem that occured to me a few times while writing SQL scripts. Well, as you can guess from the title of this post, the problem is related to “CAST” function and “LIKE” operator. Besides these 2, let’s review about “FORMAT” function.
CAST() function:
The CAST() function converts a value (of any type) into a specified datatype.
For instance:
CAST ( ID into varchar ) : ID as integer, turn an integer into a string
CAST (25.78 into INT): turn a float into an integer
CAST ( challenge_count into FLOAT): turn an integer into a decimal number
LIKE operator:
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
There are two wildcards often used in conjunction with the LIKE operator:
- The percent sign (%) represents zero, one, or multiple characters
- The underscore sign (_) represents one, single character
Okay, now let’s combine these 2 together:
→ Find all values that the ID contains number 6.
SELECT * FROM CITY
WHERE CAST(ID as varchar) LIKE “%6%”.
→ Find all values of day in format “DD-MM-YY HH:MM:SS”
SELECT CAST(day as datetime) as date FROM CITY;
FORMAT() function:
The FORMAT() function formats a value with the specified format.
Use the FORMAT() function to format date/time values and number values. For general data type conversions, use CAST() or CONVERT().
Format a sequence of number into a formatted sequence:
SELECT FORMAT(123456789, ‘##-##-#####’);
or:
DECLARE @d DATE = GETDATE();
SELECT FORMAT( @d, ‘dd/MM/yyyy’, ‘en-US’ ) AS ‘Date’
Format into 4 decimal values:
SELECT FORMAT(ROUND(SCORE,4), “F4”) FROM CITY;
