Use of SQL Server Functions left (), charindex (), and stuff ()

 

1. Left ()
Left (<character_expression>, <integer_expression>)
Returns character_expression, which starts from integer_expression at the left.

 

 
Select left ('abcdef', 3)
 

 
-- ABC
 

2. charindex ()
Charindex (<'substring _ expression' >,< expression>)

Returns the starting position of a specified substring in a string.

Here, substring _ expression is the character expression to be searched, and expression can be a string or a column name expression. If no substring is found, the return value is 0.
This function cannot be used for text and image data types.

 

 
Select charindex ('cd', 'abcdefg') -- 3 select charindex ('ac', 'abcdefg') -- 0
 

 

3. Stuff ()

 
Select stuff ('abcde', 2,3, 'mmmmmm ')
 

 
-- Ammme
 

 

-- Removes three characters starting from the second position (character B) from the first string (ABCDE,
-- Insert the second string at the start of the deletion, and create and return a string.