SQL Operators Practical: String and Numeric Calculations

Introduction

SQL operators are fundamental to manipulating data in relational databases. They enable users to perform arithmetic, string, and logical operations on data, facilitating complex queries and transformations. This guide focuses on string and numeric operators, which are critical for tasks such as data concatenation, substring extraction, and numerical calculations. Understanding these operators enhances proficiency in SQL, particularly when working with diverse data types and scenarios.

String Operators

String operators are used to manipulate text data, enabling operations like concatenation, substring extraction, and pattern matching. Key operators include:

• CONCAT: Combines two strings.

  SELECT CONCAT('Hello', ' World') AS greeting;  
  -- Output: Hello World  
  

• SUBSTRING: Extracts a substring from a string.

  SELECT SUBSTRING('Hello World', 2) AS part;  
  -- Output: ello World  
  

• LEFT: Returns the first n characters of a string.

  SELECT LEFT('Hello World', 5) AS result;  
  -- Output: Hello  
  

• RIGHT: Returns the last n characters of a string.

  SELECT RIGHT('Hello World', 4) AS result;  
  -- Output: World  
  

• INSTR: Returns the position of the first occurrence of a substring.

  SELECT INSTR('Hello World', 'o') AS position;  
  -- Output: 4  
  

• CASE: Performs conditional string comparisons.

  SELECT CASE WHEN 'Apple' > 'Banana' THEN 'Apple' ELSE 'Banana' END AS result;  
  -- Output: Apple  
  

• LIKE: Matches patterns using wildcard characters.

  SELECT * FROM fruits WHERE LIKE '%%' AND name LIKE '%Apple%';  
  -- Output: Apple, Banana  
  

• STRCMP: Compares two strings lexicographically.

  SELECT STRCMP('Apple', 'Banana') AS result;  
  -- Output: -1  
  

Numeric Operators

Numeric operators perform arithmetic operations on numbers, supporting addition, subtraction, multiplication, division, modulus, and null handling. Key operators include:

• ADD: Adds two numbers.

  SELECT ADD(10, 20) AS sum;  
  -- Output: 30  
  

• SUBTRACT: Subtracts one number from another.

  SELECT SUBTRACT(20, 10) AS difference;  
  -- Output: 10  
  

• MULTIPLY: Multiplies two numbers.

  SELECT MULTIPLY(5, 4) AS product;  
  -- Output: 20  
  

• DIVIDE: Divides two numbers.

  SELECT DIVIDE(10, 2) AS quotient;  
  -- Output: 5  
  

• MOD: Returns the remainder of a division.

  SELECT MOD(10, 3) AS remainder;  
  -- Output: 1  
  

• NULL: Handles null values in operations.

  SELECT NULLIF(10, 5) AS result;  
  -- Output: 10  
  

Combining String and Numeric Operators

Mixing string and numeric operators allows versatile data manipulation. For example:

• CONCAT with numeric values:

  SELECT CONCAT('Total:', ADD(100, 200)) AS total;  
  -- Output: Total: 300  
  

• String comparisons:

  SELECT CASE WHEN 'Apple' > 'Banana' THEN 'Apple' ELSE 'Banana' END AS result;  
  -- Output: Apple  
  

• Pattern matching with numeric values:

  SELECT * FROM products WHERE name LIKE '%%' AND price = 100;  
  -- Output: Products with name starting with any character and price 100  
  

Conclusion

String and numeric operators are indispensable in SQL for transforming and querying data. Mastery of these operators enables efficient data manipulation, complex queries, and robust data analysis. By understanding their syntax, use cases, and combinations, users can enhance their SQL skills and handle diverse data scenarios effectively. Best practices include using proper data types, avoiding implicit type conversions, and leveraging built-in functions for clarity and performance.