String and Arithmetic Expressions in SQL Server

The intention of this post is to explain the regularly used string and arithmetic expressions.

• CHARINDEX: This function searches for a substring in a string, and returns the position.
  Example: SELECT CHARINDEX(‘Data’, ‘BigDatanSQL’)
  Given that “Data” begins at position 4 in “BigDatanSQL,” this returns 4.
• CONCAT: This function concatenates (joins) the provided strings. The strings are to be separated by comma (,).
  Example: SELECT CONCAT(‘BigData’,’n’,’SQL’)
  Result will be “BigDatanSQL”
• CONCAT_WS: In a manner similar to the one above, the user-defined separator is used in concatenating supplied strings or column values.
  Example: SELECT CONCAT_WS(‘-‘,’BigData’,’n’,’SQL’)
  The result will be – BigData-n-SQL
• LEFT: The LEFT() function extracts a number of characters from a string (starting from left)
  Example: SELECT LEFT(‘BigDatanSQL’,3)
  The digit 3 denotes three characters. As a result, it returns “Big” from the string’s left side.
• LEN: The LEN() function returns the length of a string. Before and after spaces won’t be calculated.
  Example: SELECT LEN(‘BigDatanSQL ‘)
  This returns the total length of the string that is- 11

• LOWER:  This function returns the lowercase of a string. All the characters in the string can be converted into lowercase characters.
  Example: SELECT LOWER(‘BigDatanSQL’)
  This returns all lowercase of the string that is- bigdatansql
• LTRIM: This function helps in removing the spaces from the left side of the string.
  Example: SELECT LTRIM(‘ BigDatanSQL ‘)
  This returns- BigDatanSQL without the starting space.
• RIGHT: The RIGHT() function extracts a number of characters from a string (starting from right)
  Example: SELECT RIGHT(‘BigDatanSQL’,3)
  The digit 3 denotes three characters. As a result, it returns “SQL” from the string’s right side.
• RTRIM: This function helps in removing the spaces from the right side of the string.
  Example: SELECT RTRIM(‘ BigDatanSQL ‘)
  This returns- BigDatanSQL without the ending space.
• SUBSTRING: The SUBSTRING() function extracts some characters from a string. This requires a string, the number of characters to fetch from the start position, and the start position.
  Example: SELECT SUBSTRING(‘BigDatanSQL’,4,4)
  Given that the first number (4), which specifies the start position, and the second (4), which indicates the length, this returns “Data”.
• TRIM: This function eliminates the spaces before and after a string.
  Example: SELECT TRIM(‘  BigDatanSQL  ‘)
  This returns “BigDatanSQL”
• UPPER:This function returns the uppercase of a string. All the characters in the string can be converted into uppercase characters.
  Example: SELECT UPPER(‘BigDatanSQL’)
  This returns all lowercase of the string that is- BIGDATANSQL
• DATEDIFF: This function returns the difference between two dates.
  Example: SELECT DATEDIFF(d,’2022-08-10′, ‘2022-08-20’)
  This can be used to identify the number of days, hours, and minutes between two dates.
• GETDATE: This is a frequently used function. This makes it easier to get the current date. Additionally, it facilitates comparison with other dates for the purpose of producing reports. GETDATE is quite helpful when the report need is “previous seven days” or “past 269 days”.
  Example: SELECT GETDATE();

• DATENAME: This returns the specified part of the date. This function requires the interval and the date string.
  Example: SELECT DATENAME(day, ‘2022/08/19 15:03’),DATENAME(hour, ‘2022/08/19 15:03’)
  This returns 19 and 15.
• DATEPART: This returns a specified part of a date. The specification can be year, quarter, month, day of year, day, week, week day, hour, minute, second, etc.
  Example: SELECT DATEPART(yyyy, ‘2022/08/19’), DATEPART(year, ‘2022/08/10’)
  Both returns- 2022. You can use ‘ýyyy’ or ‘year’ or ‘yy’ for the year.
• CAST: A value of any type can be converted into the desired datatype using the CAST() function.
  Example: SELECT CAST(123 AS VARCHAR(10))
  This returns 123 however as a string.
• CONVERT: Similar to the CAST, CONVERT function is also helpful in converting specific data type value into another. Both does the same thing however CAST is ANSI standard however CONVERT is SQL Server specific.
  Example: SELECT CONVERT(VARCHAR(10),123)
  This returns 123 however as a string.
• SUM: This is quite often used arithmetic function. Helps in summing up the numbers/ column values.
  Examples: SELECT SUM(10+65)
  SELECT SUM(Payment) FROM tbPayments;

• AVG: This is another often used arithmetic function that helps in calculating the average of the values.
  Examples: SELECT Year, AVG(Payment) FROM tbPayments GROUP BY Year

• COUNT: This is used to fetch the number of rows from a table for the given criteria.
  Examples: SELECT COUNT(*) FROM Table.
  This returns the total number of rows of the specified table.
  SELECT COUNT(*) FROM Table WHERE Column2 = ‘SomeValue’;
  This gives the total number of rows in the table that matches the stated criteria.
• DISTINCT: This is used to fetch the unique values.
  Example: SELECT DISTINCT ColumnName FROM TableName
  The above example returns unique values from the table.
• TOP: This is used to return the specified number of rows from a table. It is equal to LIMIT in MySQL and other RDBMSs.
  Example: SELECT TOP 10 ColumnName FROM TableName;
  The above example returns 10 rows from the table.

Hope you find this article helpful.

On this website, there are numerous examples for each function. Please have a look.