» Documentation » SQL Reference

String Functions

__NOTOC__

These functions can manipulate string values.

CONCAT

Syntax

CONCAT(string_1,string_2 ... string_n) 

Concatenates all of the string arguments given. The resulting string will be truncated to 8 KB if it is over.

REPLACE

Syntax

REPLACE(haystack, needle, replacement) 

Searches the string haystack looking for occurances of needle, if any are found they are replaced with replacement and the new string is returned.

LEN

Syntax

LEN(string) 

Returns the character length of the string given.

SUBSTRING

Syntax

SUBSTRING(input,start[,length]) 

Returns the portion of input starting at the character at start_int (where 0 is the first character). If length_int is specified and is greater than zero, only length_int characters of the string will be returned.

If any of the arguments is NULL, the result will be NULL. If the substring requested doesn't fall entirely within the string, an error will be returned.

RTRIM

Syntax

RTRIM(string) 

Returns a copy of string with any trailing white-space characters removed.

LTRIM

Syntax

LTRIM(string) 

Returns a copy of string with any leading white-space characters removed.

RPAD

Syntax

RPAD(string,pad_size[,pad_string = ' ']) 

Returns a copy of string , padded on the right with pad_string until it is no less than pad_size characters long. If string is longer than pad_size characters to begin with, the string is returned without modification.

LPAD

Syntax

LPAD(string,pad_size[,pad_string = ' ']) 

Returns a copy of string , padded on the left with pad_string until it is no less than pad_size characters long. If string is longer than pad_size characters to begin with, the string is returned without modification.

MD5

Syntax

MD5(string) 

Returns an hexadecimal string representation of the MD5 hash of string.

PATINDEX

Syntax

PATINDEX( '%pattern%', expression) 

Returns the starting position of the first occurrence of a pattern in a specified expression, or zero if the pattern is not found.

Example

SELECT PATINDEX ( '%is%', 'This is a Test');
->3

SELECT PATINDEX ( '%Test', 'This is a Test');
->11

SELECT PATINDEX ( 'T%', 'This is a Test');
->1