[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 date

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;

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store