SQL Server provides a variety of string functions that allow you to manipulate and work with string data. Here’s a summary of some of the most commonly used string functions in SQL Server, along with brief descriptions and examples for each.

1. LEN()

   • Description: Returns the number of characters in a string.
   • Example:

     sql
     1SELECT LEN('Hello, World!') AS StringLength;  -- Returns 13

2. UPPER()

   • Description: Converts a string to uppercase.
   • Example:

     sql
     1SELECT UPPER('Hello, World!') AS UpperCaseString;  -- Returns 'HELLO, WORLD!'

3. LOWER()

   • Description: Converts a string to lowercase.
   • Example:

     sql
     1SELECT LOWER('Hello, World!') AS LowerCaseString;  -- Returns 'hello, world!'

4. SUBSTRING()

   • Description: Extracts a substring from a string starting at a specified position.
   • Example:

     sql
     1SELECT SUBSTRING('Hello, World!', 1, 5) AS Substring;  -- Returns 'Hello'

5. CHARINDEX()

   • Description: Returns the starting position of a specified substring within a string.
   • Example:

     sql
     1SELECT CHARINDEX('World', 'Hello, World!') AS Position;  -- Returns 8

6. REPLACE()

   • Description: Replaces all occurrences of a specified substring with another substring.
   • Example:

     sql
     1SELECT REPLACE('Hello, World!', 'World', 'SQL Server') AS ReplacedString;  -- Returns 'Hello, SQL Server!'

7. LTRIM()

   • Description: Removes leading spaces from a string.
   • Example:

     sql
     1SELECT LTRIM('   Hello, World!') AS TrimmedString;  -- Returns 'Hello, World!'

8. RTRIM()

   • Description: Removes trailing spaces from a string.
   • Example:

     sql
     1SELECT RTRIM('Hello, World!   ') AS TrimmedString;  -- Returns 'Hello, World!'

9. TRIM()

   • Description: Removes both leading and trailing spaces from a string (available in SQL Server 2017 and later).
   • Example:

     sql
     1SELECT TRIM('   Hello, World!   ') AS TrimmedString;  -- Returns 'Hello, World!'

10. CONCAT()

    • Description: Concatenates two or more strings into one string.
    • Example:

      sql
      1SELECT CONCAT('Hello', ', ', 'World!') AS ConcatenatedString;  -- Returns 'Hello, World!'

11. FORMAT()

    • Description: Formats a value based on a specified format.
    • Example:

      sql
      1SELECT FORMAT(GETDATE(), 'yyyy-MM-dd') AS FormattedDate;  -- Returns current date in 'YYYY-MM-DD' format

12. STRING_AGG()

    • Description: Concatenates values from multiple rows into a single string with a specified separator (available in SQL Server 2017 and later).
    • Example:

      sql
      1SELECT STRING_AGG(Name, ', ') AS AllNames
      2FROM Employees;  -- Returns a comma-separated list of employee names

13. ASCII()

    • Description: Returns the ASCII value of the leftmost character of a string.
    • Example:

      sql
      1SELECT ASCII('A') AS AsciiValue;  -- Returns 65

14. CHAR()

    • Description: Returns the character corresponding to the specified ASCII value.
    • Example:

      sql
      1SELECT CHAR(65) AS Character;  -- Returns 'A'

15. REVERSE()

    • Description: Reverses the order of characters in a string.
    • Example:

      sql
      1SELECT REVERSE('Hello, World!') AS ReversedString;  -- Returns '!dlroW ,olleH'

We hope you now have a clear understanding of the various string functions available in SQL Server.