Character manipulation functions Give some examples

Hi Kiran Kumar,

Character manipulation functions are used to modify, extract, analyze string data, etc. Useful when working with textual data in queries, reports, stored procedures, etc.

Following are common character manipulation functions in SQL Server:

No	Function	Use or description
1	LEN()	Returns the number of characters in a string (excluding trailing spaces)
2	LEFT() and RIGHT()	Extracts a specified number of characters from the left or right side of a string
3	SUBSTRING()	Extracts a substring from a string starting at a specified position
4	CHARINDEX()	Returns the position of a substring within a string
5	REPLACE()	Replaces all occurrences of a substring with another substring
6	LTRIM() and RTRIM()	Removes leading or trailing spaces from a string
7	UPPER() and LOWER()	Converts a string to uppercase or lowercase
8	REPLICATE()	Repeats a string a specified number of times
9	REVERSE()	Reverses the characters in a string

Examples:

SELECT LEN('Pankaj Patel') AS Length;-- Output: 12

SELECT LEFT('Pankaj Patel', 6) AS FirstName;-- Output: Pankaj

SELECT RIGHT('Pankaj Patel', 5) AS LastPart;-- Output: Patel

SELECT SUBSTRING('Pankaj Patel', 8, 5) AS LastName;-- Output: Patel

SELECT CHARINDEX('Patel', 'Pankaj Patel') AS Position;-- Output: 8

SELECT REPLACE('Hello World', 'World', 'SQL') AS ReplacedText;-- Output: Hello SQL

SELECT LTRIM('   Hello') AS NoLeadingSpaces;

SELECT RTRIM('Hello   ') AS NoTrailingSpaces;

SELECT UPPER('sql server') AS UpperCase;-- Output: SQL SERVER

SELECT LOWER('SQL SERVER') AS LowerCase;-- Output: sql server

SELECT REPLICATE('*', 5) AS Stars;-- Output: *****

SELECT REVERSE('SQL') AS Reversed;-- Output: LQS

Hope this will help you!

In SQL Server, character manipulation functions are built-in string functions that allow you to manipulate, search, and transform character (string) data. These functions are especially useful when dealing with text-based data stored in CHAR, VARCHAR, NCHAR, or NVARCHAR columns.

1. LEN() – Get the length of a string (number of characters)

SELECT LEN('SQL Server') AS Length; -- Output: 10

2. LTRIM() and RTRIM() – Remove leading or trailing spaces

SELECT LTRIM(' Hello') AS NoLeadingSpaces; -- Output: 'Hello' 

SELECT RTRIM('Hello ') AS NoTrailingSpaces; -- Output: 'Hello'