Advanced string functions in SQL Server

 Advanced string functions in SQL Server that are useful for complex string manipulation tasks:

1. STRING_AGG

• Purpose: Concatenates values from a group into a single string, with a specified separator.
• Example:

  SELECT STRING_AGG(column_name, ', ') AS concatenated_string
  FROM your_table;
  

• Use Case: Aggregating multiple rows of data into a single comma-separated string.

2. FORMAT

• Purpose: Returns a value formatted according to a specified format and culture.
• Example:

  SELECT FORMAT(GETDATE(), 'dd/MM/yyyy') AS formatted_date;
  

• Use Case: Formatting dates, numbers, or other values into specific string representations.

3. STUFF

• Purpose: Deletes a specified length of characters and inserts another string into the original string.
• Example:

  SELECT STUFF('Hello World', 6, 5, 'SQL') AS result;
  

• Output: Hello SQL
• Use Case: Replacing part of a string with another string.

4. REVERSE

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

  SELECT REVERSE('SQL Server') AS reversed_string;
  

• Output: revreS LQS
• Use Case: Checking palindromes or reversing strings for specific logic.

5. PATINDEX

• Purpose: Returns the starting position of the first occurrence of a pattern in a string.
• Example:

  SELECT PATINDEX('%Server%', 'SQL Server 2024') AS position;
  

• Output: 5
• Use Case: Finding the position of a substring using wildcard patterns.

6. TRANSLATE

• Purpose: Replaces a sequence of characters in a string with another sequence of characters.
• Example:

  SELECT TRANSLATE('1234-5678', '12345678', 'ABCDEFGH') AS translated_string;
  

• Output: ABCD-EFGH
• Use Case: Substituting characters in a string.

7. TRIM

• Purpose: Removes leading and trailing spaces or specified characters from a string.
• Example:

  SELECT TRIM('!.' FROM '...Hello World...') AS trimmed_string;
  

• Output: Hello World
• Use Case: Cleaning up strings by removing unwanted characters from both ends.

8. CHARINDEX

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

  SELECT CHARINDEX('World', 'Hello World') AS position;
  

• Output: 7
• Use Case: Locating a substring within a string without using wildcards.

9. REPLACE

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

  SELECT REPLACE('Hello World', 'World', 'SQL') AS replaced_string;
  

• Output: Hello SQL
• Use Case: Replacing specific parts of a string.

10. LEFT and RIGHT

• Purpose: Extracts a specified number of characters from the left or right side of a string.
• Example:

  SELECT LEFT('SQL Server', 3) AS left_string, RIGHT('SQL Server', 6) AS right_string;
  

• Output: SQL, Server
• Use Case: Extracting substrings from the beginning or end of a string.

These advanced string functions are essential for complex text processing and manipulation tasks in SQL Server.