Examples of SET Variable
Let’s look at some examples of using SET variable in MariaDB.
Example 1: Setting Integer Variables
Query:
Assigning the value 10 to the variable my_integer. Now my_integer holds the value 10.
SET @my_integer = 10;
We can see the output by executing the below statement.
Query:
SELECT @my_integer;
Output:
Explanation: We can clearly saw the my_integer hold 10 values.
Example 2: Setting String Variables
Query:
Assigning the value Hello GFG to the variable my_string. Now, my_string holds the value Hello GFG.
SET @my_string = 'Hello, GFG!';
Check output by executing the below command.
Query:
SELECT @my_string;
Output:
Explanation: We can clearly set the string in the my_string.
Example 3: Setting Decimal Variables
Query:
Assigning the value 8.5 to the variable my_decimal. Now, my_decimal holds the value 8.5.
SET my_decimal =8.5;
Check Output:
SELECT @my_decimal;
Output:
Explanation: We have successfully Set the Decimal Variables
Example 4: Calculating with SET Variables
You can also perform calculations and store the result in a SET variable:
Query:
SET @num1 = 15;
SET @num2 = 20;
SET @result = @num1 + @num2;
In this query, num1 and num2 are two variables which are sets to 15 and 20 respectively, and then we are calculating the sum of these two variables which will get stored in the result variable.
See the result by executing the below statement.
Query:
SELECT @result;
Output:
We can see below that the output is sum of both variables.
Explanation: We have successfully Calculate with SET Variables as a 40 output.
Example 5: Concatenating Strings
SET variables can concatenate strings for various purposes:
Query:
SET @first_name = 'Minal';
SET @last_name = 'Pandey';
SET @full_name = CONCAT(@first_name, ' ', @last_name);
In this query, we have two variables first_name and last_name which are sets to Minal and Pandey respectively. CONCAT keyword is use to concatenate the string. Here also we concatenates the variables with a space in between to form the full name, which is stored in the variable @full_name.
You can see the output by executing the below command.
Query:
SELECT @full_name;
Output:
Explanation: We have successfully Concatenate the two Strings.
Example 6: Retrieving Data from Queries
You can store query results in SET variables for further processing:
Query:
SET @max_salary = (SELECT MAX(salary) FROM employees);
The query SET @max_salary is assigning the maximum value of the salary column from the employees_a table to the user-defined variable @max_salary. This query finds the maximum salary from the employees_a table and stores it in the variable @max_salary. After executing this query, @max_salary will hold the maximum salary value from the employees_a table.
You can see the output by executing the below command.
Query:
SELECT @max_salary;
Output:
Explanation: We have successfully Retrieving the Data from Queries
Example 7: Incrementing a Counter
SET variables can be used as counters:
Query:
SET @counter = 0;
SET @counter = @counter + 1;
Here we have set the counter variable to 0 and then we are increments it by 1.
Check Output:
SELECT @counter;
Output:
Explanation: We have successfully set the Incrementing a Counter.
SET Variable in MariaDB
In MariaDB, the SET statement is a main tool in variable handling. Users can assign values to variables, operate with them, and control database operations in various respects. This article includes a look at the SET variable usage in MariaDB and its syntax together with some examples.