String Function

Select len('sagar')--5 -- length of the String

Select lower('InDiA')--india ---Convert String in Lower character
Select Upper('InDiA')--INDIA---Convert String in Upper/ Capital character

Select left('Sagar',2)--Sa ---------Return us left Characters
select Right('Sagar',4)--agar---------Return us Right Characters

Select Ltrim('    sagar')---sagar --------Trim left white space
Select RTrim('   sagar     ')---   sagar--------Trim right white space
Select Ltrim(RTrim('   sagar     '))---sagar
Select Trim('   sagar     ')---sagar -------Trim both side white space

Select Reverse('Amol')--ragas ----Reverse String

Select Substring('maharashtra',1,10)--maharashtr ---------Return us SubString
Select Substring('maharashtra',5,3)--ras

Select Replace('Sagar','a','for')---Sforgforr ---------Replace character/expression in string

Select charIndex('Sagar','Hi sagar',0)--4th position-------Return Position of the character.
Select charIndex('r','sagar',0)--5th position

------------------------------------------------------------------
Space()-----usage for the SPACE function is to replicate spaces for a string

SELECT 'sagar'+ SPACE(2) + 'sonawane'
---sagar,          sonawane
Select replicate('Zebra',10)----TestNameXXXXX

------------------------------------------------------------------

Stuff Function

SELECT STUFF('SAGAR', 2, 3, 'INDIA'); --SINDIAR
--2 -from char
--3 length of str 
--SAGAR- main string   AGA- replace string from 2 to 4- as 2,3,4 
--SINDIAR 

--Drop Table Customer

Create Table Customer
(
[CustomerID] int,
[CustomerName] varchar(50),
[BankName] varchar(50)
)
GO
Insert into Customer Values (1,'SAGAR','Axis')
Insert into Customer Values (1,'SAGAR','HDFC')
Insert into Customer Values (1,'SAGAR','ICICI')
Insert into Customer Values (2,'Amol','Axis')
Insert into Customer Values (2,'Amol','HDFC')
GO
Select * from Customer

SELECT distinct [CustomerID], [CustomerName],
STUFF(
(SELECT ', ' + A.[BankName] 
FROM Customer A
WHERE A.[CustomerID]=B.[CustomerID] FOR XML PATH(''))
,1,1,'') 
As [Bank Accounts]
FROM Customer B
 

OR 

SELECT [CustomerID], [CustomerName],
STUFF(
(SELECT ', ' + A.[BankName] 
FROM Customer A
WHERE A.[CustomerID]=B.[CustomerID] FOR XML PATH(''))
,1,1,'') 
As [Bank Accounts]
FROM Customer B
GROUP BY [CustomerID],[CustomerName]

--output 

CustomerID CustomerName Bank Accounts
1 SAGAR Axis, HDFC, ICICI
2 Amol Axis, HDFC
------------------------------------------------------------------
Select ASCII('A')---65----Returns Int.
Select ASCII('ABC')---65---First Char taken.
Select ASCII('c')---99

Select Char(65)---c---Returns char
Select Char(256)---NULL
Select Char(240)---ð
Select Char(200)---È
Select Char(-5)-- NULL
Select Char(255)--ÿ
Select Char(32)--space

--This function works just like ASCII, except it accepts the UNICODE character value as input.
--This is useful when you are working with international character sets. 
--The function UNICODE returns the integer value for the first character.

Select Unicode(N'सोपे')--2360
Select Unicode(N'Simple')--83
Select Unicode(N'S')--83
Select Unicode(N's')--115
Select Unicode(N'A')--65

--The function Nchar returns the UNICODE character for the integer value int_expression.
--The NCHAR function works exactly like CHAR except it returns the UNICODE character. 
--This function is useful if you are working with large international character sets.

Select NCHAR(65)--A
Select NCHAR(2361)--स
Select NCHAR(83)--S
Select NCHAR(115)--s