26 Essential SQL Server String Functions

26 Essential SQL Server String Functions

by
Spread the love
         

String manipulation is essential for managing and transforming textual data when working with SQL Server. SQL Server offers a robust set of string functions that empower developers to extract insights, clean data, and simplify complex transformations. In this blog, we will explore 26 essential SQL Server string functions, providing step-by-step guidance, real-world examples, and advanced use cases to master data manipulation.

1. ASCII() and CHAR()

Purpose:

  • ASCII(): Returns the ASCII value of the first character in a string.
  • CHAR(): Converts an ASCII value to its corresponding character.
SELECT ASCII('A') AS ASCII_Value, CHAR(65) AS Char_Value;

--Output
--ASCII_Value = 65
--Char_Value = A

Real-World Use:

Identify invalid characters in customer names.

SELECT CustomerName, ASCII(SUBSTRING(CustomerName, 1, 1)) AS ASCII_Value
FROM Customer
WHERE ASCII(SUBSTRING(CustomerName, 1, 1)) NOT BETWEEN 65 AND 90;

2. CHARINDEX() and PATINDEX()

Purpose:

  • CHARINDEX(): Returns the position of a substring in a string.
  • PATINDEX(): Similar to CHARINDEX(), but supports wildcard patterns.
SELECT CHARINDEX('net', 'dotnetinfinity') AS Position

--Output
--4

3. CONCAT() and CONCAT_WS()

Purpose:

  • CONCAT(): Combines multiple strings into one.
  • CONCAT_WS(): Same as CONCAT(), but allows a separator.
SELECT CONCAT('Dotnet', ' ', 'Infinity') as FullName

--Output
--Dotnet Infinity

SELECT CONCAT_WS('-', 'Dotnet', 'Infinity', 'Blog') AS FullDetails
--Output
--Dotnet-Infinity-Blog

4. DIFFERENCE() and SOUNDEX()

Purpose:

  • DIFFERENCE(): Compares the similarity of two strings using SOUNDEX.
  • SOUNDEX(): Generates a phonetic representation of a string.
SELECT SOUNDEX('dotnet') AS Sound, DIFFERENCE('dotnet', 'dot') AS Similarity;

--Output
--Sound = D353
--Similarity = 3

5. FORMAT()

Purpose:

Formats a string or number value according to a specified format.

DECLARE @d AS DATE = GETDATE();

SELECT FORMAT(@d, 'dd/MM/yyyy', 'en-US') AS 'Date',
       FORMAT(999999999, '###-##-####') AS 'Custom Number';

--Output
--Date = 31/12/2024
--Custom Number = 999-99-9999

You can check all other formats from here.

6. LEFT(), RIGHT(), and SUBSTRING()

Purpose:

  • LEFT(): Extracts characters from the start.
  • RIGHT(): Extracts characters from the end.
  • SUBSTRING(): Extracts a substring based on position.
SELECT LEFT('dotnetinfinity', 5) AS FirstFive, RIGHT('dotnetinfinity', 3) AS LastThree, SUBSTRING('dotnetinfinity', 2, 5) AS MiddleFive

--Output
--FirstFive = dotne
--LastThree = ity
--MiddleFive = otnet

7. LEN() and TRIM() (SQL Server 2017+)

Purpose:

  • LEN(): Returns the length of a string.
  • TRIM(): Removes spaces from both ends of a string.
SELECT LEN('dotnetinfinity') AS NameLength, TRIM('   dotnetinfinity   ') AS TrimmedName

--Output
--NameLength = 14
--TrimmedName = dotnetinfinity

8. LTRIM() and RTRIM()

Purpose:

  • LTRIM(): Removes spaces from the left side.
  • RTRIM(): Removes spaces from the right side.
SELECT LTRIM('  dotnetinfinity') AS LTrimmed, RTRIM('dotnetinfinity  ') AS RTrimmed;

--Output
--LTrimmed = dotnetinfinity
--RTrimmed = dotnetinfinity

9. NCHAR() and UNICODE()

Purpose:

  • NCHAR(): Returns the Unicode character for a given integer.
  • UNICODE(): Returns the Unicode value of the first character.
SELECT NCHAR(8364) AS EuroSymbol, UNICODE('A') AS UnicodeValue;

--Output
--EuroSymbol = €
--UnicodeValue = 65

10. QUOTENAME()

Purpose:

Adds delimiters (e.g., brackets) to a string.

SELECT QUOTENAME('dotnetinfinity') AS QuotedName

--Output
--QuotedName = [dotnetinfinity]

11. REPLACE() and TRANSLATE() (SQL Server 2017+)

Purpose:

  • REPLACE(): Replaces occurrences of a substring.
  • TRANSLATE(): Replaces multiple characters at once.
SELECT REPLACE('dotnetinfinity', 'dotnet', '.net') AS UpdatedName,TRANSLATE('dotnetinfinity', 'in', '12') AS TransformedName

--Output
--UpdatedName = .netinfinity
--TransformedName = dot2et12f121ty

12. REPLICATE() and SPACE()

Purpose:

  • REPLICATE(): Repeats a string a specified number of times.
  • SPACE(): Generates a string of spaces.
SELECT REPLICATE('*', 10) AS Stars, SPACE(5) + 'IndentedText' AS Indented;

--Output
--Stars = **********
--TransformedName =      IndentedText

13. STR()

Purpose:

Converts a numeric value to a string with formatting.

SELECT STR(123.458989, 6, 2) AS FormattedNumber;

--Output
--FormattedNumber = 123.46

14. STUFF()

Purpose:

Inserts a string into another string, replacing characters.

SELECT STUFF('123456', 2, 3, 'ABC') AS ModifiedString;

--Output
--ModifiedString = 1ABC56

15. REVERSE()

Purpose:

Reverses a string.

SELECT REVERSE('dotnetinfinity') AS ReversedName

--Output
--ReversedName = ytinifnitentod

Conclusion

Mastering SQL Server string functions equips you to easily handle complex data transformations. These functions are invaluable for data cleaning, formatting, and retrieval. By combining them effectively, you can unlock the full potential of your SQL queries and streamline your workflows. Start practising these functions today to elevate your SQL skills!


Spread the love