SUBSTRING Function

The SQL Server (Transact-SQL) SUBSTRING functions allows you to extract a substring from a string.

EX. SELECT SUBSTRING('Techaray.com', 1, 4);

Result: 'Tech'

LEFT

LEFT function starts BEFORE the left-most character of a string and moves to the right.

SELECT LEFT('Techaray.com', 3)

Result :’Tec’

RIGHT

RIGHT function starts AFTER the right-most character and moves inwards to the left.

SELECT RIGHT('Techaray.com', 3)

Result: ‘com’

CHARINDEX

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

SELECT CHARINDEX('aray', 'Techaray.com');

Result :5

LEN

The LEN function returns the length of an input string in terms of the number of characters.

SELECT LEN('XYZ')

Result:3

DATALENGTH

The DATALENGTH function returns the length of the input in terms of number of bytes.

Result:6

REPLACE

REPLACE function, you can replace in an input string provided as the first argument

all occurrences of the string provided as the second argument, with the string provided

as the third argument. For example, the expression REPLACE('.1.2.3.', '.', '/') substitutes all occurrences

of a dot (.) with a slash (/), returning the string '/1/2/3/'.

 

REPLICATE

The REPLICATE function allows you to replicate an input string a requested number of

times. For example, the expression REPLICATE('0', 10) replicates the string '0' ten times, returning

'0000000000'.

STUFF

The STUFF function operates on an input string provided as the first argument; then, from

the character position indicated as the second argument, deletes the number of characters

indicated by the third argument. Then it inserts in that position the string specified as the

fourth argument. For example, the expression STUFF(',x,y,z', 1, 1, '') removes the first character

from the input string, returning 'x,y,z'.