SQL String Functions

Next Post
By Admin at 14 Jun 2010, 15:37 PM
  • SQL Server provides built-in functions to handle strings. Each function performs an operation on a string and returns a string or numeric value.

    Function NameDescriptionExample
    ASCII

    This function accepts a character or character expression as input and returns the ASCII code value of the character. If the input is a character expression then it returns the ASCII code value of the left most character.

    Syntax

    ASCII(character) or ASCII(character_expression)

    1. SELECT ASCII('a')

    Output:

    97

    1. SELECT ASCII('b')

    Output:

    98

    1. SELECT ASCII('abc')

    Output:

    97

    CHAR

    It performs just the opposite operation of ASCII function. The CHAR function accepts an integer value within the ASCII code range and returns the character corresponding to the ASCII code.

    The input value must be in the range 0 to 255. NULL value will be returned if the input value is outside the range.

    Syntax

    CHAR(integer)

    1. SELECTCHAR(97)

    Output:

    a

    1. SELECTCHAR(65)

    Output:

    A

    1. SELECTCHAR(256)

    Output:

    NULL

    NCHAR

    This function takes in an integer value and returns the Unicode character value defined for the integer value by Unicode Standard.

    The integer value can be in the range 0 to 65535. Outside this range returns a NULL value.

    Syntax

    NCHAR(integer)

    1. SELECTNCHAR(65)

    Output:

    A

    1. SELECTNCHAR(66)

    Output:

    B

    1. SELECTNCHAR(324)

    Output:

    ń

    1. SELECTNCHAR(65600)

    Output:

    NULL

    CHARINDEX

    The CHARINDEX function accepts two strings and an optional starting position. It Searches string2 for string1 and returns the starting position if string1 is found in string2, otherwise returns zero. If the optional parameter start_position is given then the search begins from the mentioned starting position of string2.

    Syntax

    CHARINDEX(string1,string2) or CHARINDEX(string1,string2,start_position)

    1. SELECT CHARINDEX('blue','The sky is blue.')

    Output:

    12

    1. SELECT CHARINDEX('s','The sky is blue.',1)

    Output:

    5

    1. SELECT CHARINDEX('s','The sky is blue.',8)

    Output:

    10

    1. SELECT CHARINDEX('c','The sky is blue.')

    Output:

    0

    PATINDEX

    This function searches a string for the given pattern. If the given pattern is found in the string then it returns the starting position of the first occurrence of the pattern.

    Syntax

    PATINDEX('%pattern%',String1)

    1. SELECT PATINDEX('% %','The sky is blue.')

    Output:

    4

    1. SELECT PATINDEX('%is%','The sky is blue.')

    Output:

    9

    SPACE

    This function accepts a positive integer value and returns as many white spaces as the input value.

    Syntax

    SPACE(integer)

    1. SELECT'Hello'+SPACE(0)+'World'

    Output:

    HelloWorld

    1. SELECT'Hello'+SPACE(1)+'World'

    Output:

    Hello World

    STR

    The STR function converts a number from numeric data type to character data type. In other words, it takes in a number and returns the number as a string.

    The default output string length will be 10. We can explicitly mention the output string length as the second input parameter.

    The third parameter which is optional is used to mention the number of decimal places in the output parameter. The precision of the output string can be up to 16. When the precision value is greater than 16, the output value will be truncated to 16 decimal places.

    Syntax

    STR(float_number) or STR(float_number, string_length) or STR(float_number, string_length, decimal_precision)

    1. SELECT STR(34343.3434343)

    Output:

    34343

    1. SELECT STR(34343.3434343,5)

    Output:

    34343

    1. SELECT STR(34343.3434343,9,3)

    Output:

    34343.343

    REPLACE

    It replaces all occurrences of the given string pattern in the source string with the replacement string.

    Syntax

    REPLACE ( source_string, string_pattern, string_replacement)

    1. SELECTREPLACE('The sky is blue.','sky','sea')

    Output:

    The sea is blue.

    STUFF

    The STUFF function removes the characters from source string at the start position to the specified length and then inserts the inserts the string to be stuffed into the source string at the start position.

    Syntax

    STUFF(source_string, start_position, length, instertion_string)

    1. SELECT STUFF('The sky is blue.',5,3,'sea')

    Output:

    The sea is blue.

    REPLICATE

    This REPLICATE function accepts a string as input and repeats the given string for the specified number of times.

    Syntax

    REPLICATE(string1, number_of_repetitions)

    1. SELECT REPLICATE('A',3)

    Output:

    AAA

    SUBSTRING

    The SUBSTRING function can be used to get a part of a string.

    Syntax

    SUBSTRING(string1,start_position, length)

    1. SELECTSUBSTRING('www.sql-programmers.com',5,15)

    Output:

    sql-programmers

    LEFT

    This function accepts an input string and returns the specified length string from the left hand side of the input string.

    Syntax

    LEFT(string1, length)

    1. SELECTLEFT('Hello World',5)

    Output:

    Hello

    RIGHT

    This function accepts an input string and returns the specified length string from the right hand side of the input string.

    Syntax

    RIGHT(string1, length)

    1. SELECTRIGHT('Hello World',5)

    Output:

    World

    LEN

    The LEN returns the length of a string. i.e., returns the count of characters in a string. This function ignores the blank spaces, if any, at the end of the input string.

    Syntax

    LEN(string1)

    1. SELECT LEN('Hello World')

    Output:

    11

    1. SELECT LEN('Hello World ')

    Output:

    11

    REVERSE

    It returns the reverse of the input string.

    Syntax

    REVERSE(string1)

    1. SELECT REVERSE('Hello World')

    Output:

    dlroW olleH

    LOWER

    It converts all upper case letters in a given input string to lower case letters

    Syntax

    LOWER(string1)

    1. SELECTLOWER('Hello World')

    Output:

    hello world

    UPPER

    It converts all lower case letters in a given input string to upper case letters.

    Syntax

    UPPER(string1)

    1. SELECTUPPER('Hello World')

    Output:

    HELLO WORLD

    LTRIM

    The LTRIM function removes the blank spaces at the beginning of the input string i.e., the leading blanks are removed.

    Syntax

    LTRIM(string1)

    1. SELECT LTRIM(' Hello World')

    Output:

    Hello World

    RTRIM

    The RTRIM function removes the blank spaces at the end of the input string i.e., the trailing blanks are removed.

    Syntax

    RTRIM(string1)

    1. SELECT RTRIM('Hello World ')

    Output:

    Hello World


    Comments

     

    Post a comment

    Please correct the following: