Built-in Functions (Transact-SQL)

CHARINDEX - Searches an expression for another expression and returns its starting position if found.

DECLARE @FullName VARCHAR(100)

SET @FullName = 'Clark Kent'

select CHARINDEX(' ', @FullName)
Output – 6

STUFF - The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
--Syntax STUFF(character_expression , start , length , replaceWith_expression)

DECLARE @FullName VARCHAR(100)

DECLARE @Alias VARCHAR(20)

SET @FullName = 'Clark Kent'

SET @Alias = ' "Superman" '

SELECT STUFF(@FullName, CHARINDEX(' ', @FullName), 1, @Alias) AS [FullName]

Output - Clark "Superman" Kent

SELECT STUFF('abcdef', 2, 3, 'ijklmn');

Output - aijklmnef 

Replace -  Replaces all occurrences of a specified string value with another string value.

Syntax - REPLACE (string_expression , string_pattern , string_replacement)

SELECT REPLACE('ABCDEFGH','AB','K')

Output – KCDEFGH

REPLICATE - Repeats a string value a specified number of times.

Syntax- REPLICATE ( string_expression ,integer_expression ) 

SELECT 'ID'+ REPLICATE('0', 4) ;-- output:ID0000

Reverse - Returns the reverse order of a string value.

Syntax - REVERSE ( string_expression )

SELECT REVERSE('ABCD') 

Output – DCBA

ASCII – returns ASCII value of a specified string

SELECT ASCII('A')  

Output – 65

Concat – Returns a string that is the result of concatenating two or more string values.

select CONCAT('AB','C'),CONCAT ( 'Happy ', 'Birthday ', 11, '/', '25' )

Output- ABC   Happy Birthday 11/25

LEFT - Returns the left part of a character string with the specified number of characters.

SELECT LEFT('abcdefg',2)      Output - ab

SELECT RIGHT('abcdefg',2)      Output - fg

SELECT LEN('abcdefg')      Output – 7 

SELECT LOWER('ABCDEFG') AS Lower      Output - abcdefg

SELECT UPPER('abcdefg') as uppar;      Output – ABCDEFG

LTRIM - Returns a character expression after it removes leading blanks.

select LTRIM(' abcdefg dfj')     Output – abcdefg dfj

select RTRIM('abcdefg dfj ')     Output – abcdefg dfj

STR - Returns character data converted from numeric data.

Syntax - STR ( float_expression [ , length [ , decimal ] ] )

SELECT STR(123.45, 6, 1);

GO

Output - 123.5

SUBSTRING - Returns part of a character, binary, text, or image expression in SQL Server.

Syntax- SUBSTRING ( expression ,start , length )

SELECT x = SUBSTRING('abcdef', 2, 3);

Output -  bcd

CHAR – Converts an int ASCII code to a character.

SELECT char(65)

Output – A

SPACE – Returns a string of repeated spaces.

Syntax- SPACE ( integer_expression )

SELECT  SPACE(20)+'abcdefg'+ ','

Output -                      abcdefg,