Home / SQL-Tut / SQL Server Functions - String Functions

SQL Server Functions - String Functions


Following are the SQL server String Functions and their use in SQL.

  • ASCII
    • - The ASCII function returns the ASCII code for the particular/specified character.

    Syntax

    ASCII('character')


    Example

    SELECT ASCII('A') as Code_of_character


    Output

    Code_of_character
    65


    If more than one characters are specified, the ASCII functions will return the code for the first character of the word , other characters will be ignored.

    Example

    SELECT ASCII('Tech') as Code_of_character


    Output

    Code_of_character
    84


  • CHAR
    • - The CHAR function is the opposite of ASCII function. It will return the Character of the specified code in the expression.

    Syntax

    CHAR('number')


    Example

    select char('65') as Character_Of_Code


    Output

    Character_of_code
    A


  • CHARINDEX
    • - The CHARINDEX function returns the integer stating the location of a substring in a specified string.

    Syntax

    CHARINDEX(substring,string,start_position)

    Parameters
    substring-The substring to find in string.This is required.
    string- The string to find in. This is required.
    start_position- The is the position in string from the search will start- first position is 1. It is an optional parameter.


    Example

    select CHARINDEX('v','vishal') as Position_Of_Character


    Output

    Position_Of_Character
    1


  • CONCAT
    • - The CONCAT function Concatenate two or more strings together.

    Syntax

    CONCAT(string_1,string_2,........string_n)

    Parameters
    string_1,string_2...string_n- strings to concat.

    Example

    select CONCAT('google','.com') as Concatenated_string


    Output

    Concatenated_string
    google.com




  • DATALENGTH
    • - This function gives the length of an expression in bytes.

    Syntax

    DATALENGTH(expression)

    Parameters
    expression. string of which the length to calculate.

    Example

    select DATALENGTH('google') as Length_Of_String


    Output

    Length_Of_String
    6
    Note- DATALENGTH function also calculate the space.

    Example

    select DATALENGTH('google ') as Length_Of_String


    Output

    Length_Of_String
    7


  • LEFT
    • - The LEFT function extracts a substring from the string starting from the left.

    Syntax

    LEFT(string,no_of_characters)

    Parameters
    string- strings from which the characters need to extract.
    no_of_characters- no of characters to extract.

    Example

    select LEFT('google',2) as Extracted_String


    Output

    Extracted_String
    go
    Note- LEFT function also takes space into consideration if provided.

    Example

    select LEFT(' google',2) as Extracted_String


    Output

    Extracted_String
    g


  • LEN
    • - The LEN function return the length of the string.

    Syntax

    LEN(expression)

    Parameters
    string - to calculate length.

    Example

    select LEN('google') as Length_Of_String


    Output

    Length_Of_String
    6
    Note- The LEN function does not count trailing spaces but it does count leading spaces in expression.

  • LOWER
    • - The LOWER function return the string into lowercase letter.

    Syntax

    LOWER (expression)

    Parameters
    string - to lowercase.

    Example

    select LOWER('GOOGLE') as Lowercase_String


    Output

    Lowercase_String
    google


  • UPPER
    • - The UPPER function return the string into uppercase letter.

    Syntax

    UPPER (expression)

    Parameters
    string - to uppercase.

    Example

    select UPPER('google') as Uppercase_String


    Output

    Uppercase_String
    GOOGLE


  • LTRIM
    • - The LTRIM function removes the leading spaces.

    Syntax

    LTRIM(expression)

    Parameters
    string - to remove leading spaces from.

    Example

    select LTRIM(' google' ) as removedleadingspace


    Output

    removedleadingspace
    google


  • RTRIM
    • - The RTRIM function removes the trailing spaces.

    Syntax

    RTRIM(expression)

    Parameters
    string - to remove leading spaces from.

    Example

    select RTRIM('google ' ) as removedtrailingspace


    Output

    removedtrailingspace
    google


  • PATINDEX
    • - The PATINDEX function returns the location of pattern in a string.

    Syntax

    PATINDEX (expression)

    Parameters
    pattern- the pattern to find. The pattern must be provided with wildcard characters
    string- the string to search in.

    Wildcard characters

    %- matches string with any length including zero.
    _ - matches single character.
    []- matches any characters provided in the bracket.
    [^]- matches any characters which are not in the bracket.

    Example

    select PATINDEX('%goo%','google') as location_Of_Searched_Pattern


    Output

    location_Of_Searched_Pattern
    google
    Note- If supplied pattern not found , the function returns zero.

  • REPLACE
    • - The REPLACE function replaces the characters in the given string with another sequence of characters.

    Syntax

    REPLACE (string, string_to_replace,replacement_string)

    Parameters
    string - in which the characters to replace.
    string_to_replace- the string to replace in string.
    replacement_string- string to replace for string_to_replace.
    Example

    select REPLACE('KGS','K','S') as replaced_string


    Output

    replaced_string
    SGS


  • RIGHT
    • - The RIGHT function return the substring from a string starting from the right.

    Syntax

    RIGHT(string,no_of_characters)

    Parameters
    string - the string from which the characters need to extract from .
    no_of_characters - number characters to extract.

    Example

    select RIGHT('google',3) as Extracted_From_Right


    Output

    Extracted_From_Right
    gle


  • SPACE
    • - The SPACE function returns the string with given spaces.

    Syntax

    SPACE(number)

    Parameters
    number- number of spaces to return.

    Example

    select SPACE(10) as SpaceReturn


    Output

    SpaceReturn


  • STR
    • - The STR function returns the string representation of the number.

    Syntax

    STR(number,length,decimal_places)

    Parameters
    number- number to convert to string.
    length- the length of the returned string. Default length is 10
    decimal_places- the number of decimals to display.

    Example

    select STR(54,4,1) as returnednumber


    Output

    returnednumber
    54.0


  • STUFF
    • - The STUFF function deletes given number of characters from the string and inserts another characters at the position starting from the given position.

    Syntax

    STUFF(source_string,start,length,another_string)

    Parameters
    source_string- the string to modify.
    start- the position in source string from which to delete characters and start inserting another characters.
    length- the number of character to delete from source string.
    another_string- the string to insert in place of deleted characters.

    Example

    select STUFF('DJJEE',1,3,'ggg') as returnedstring


    Output

    returnedstring
    gggEE


  • SUBSTRING
    • - The SUBSTRING function extract substring from given string.

    Syntax

    SUBSTRING(source_string,start,number_of_characters)

    Parameters
    source_string- the string to modify.
    start- the position to start from.
    number_of_characters- the number of character to extract.

    Example

    select SUBSTRING('google',1,3) as returnedstring


    Output

    returnedstring
    goo
    SQL Server Functions - String Functions SQL Server Functions - String Functions Reviewed by LanguageExpert on June 24, 2018 Rating: 5

    No comments

    Subscribe to: Post Comments (Atom)