Welcome to WebmasterServe!

FREE TO JOIN! Join us now to engage in informative and friendly discussions about Webmastering, SEO, SEM, Internet Marketing, Programming, Graphic Design, Online Jobs and more. What are you waiting for? Ready to join our friendly community? It takes just one minute to register.

Dismiss Notice

Join WebmasterServe Forums 
Join the discussion! Have a better idea or an opinion? It takes just one minute to register Click Here to Join

Tutorial Top 10 Sql Query By Using Functions In Database

Discussion in 'Database Management' started by Swati Mishra, Jan 30, 2016.

  1. Swati Mishra

    Yellow Belt

    Joined:
    Dec 28, 2015
    Messages:
    644
    Ratings:
    +105 / -0
    String Function:

    [​IMG]

    In the database sql server provide the string function, character expressions are come under the string function. This function is used for the operation on the character strings. String function is also convert data into char or varchar datatype.


    Syntax is:
    SELECT function_name (parameters)
    Where function_name is the string name and the parameters are used for the String function .
    Now we will describe below all the String functions:


    Function name Parameters
    ASCII (character_expression)
    CHAR (integer_expression)
    CHARINDEX (pattern_expression)
    DIFFERENCE (character_expression1,character_expression2 )
    LEFT (character_expression, integer_expression)
    LEN (character_expression)
    LOWER (character_expression)
    LTRIM (character_expression)
    PATINDEX (%pattern%,expression)
    REVERSE (character_expression)
    RIGHT (character_expression,integer_expression)
    RTRIM (character_expression)
    SPACE (integer_expression)
    STR (float_expression,length,decimal)
    STUFF (character_expression1,start, length, character_expression2)
    SUBSTRING (expression, start, length)
    UPPER (character_expression)


    Now we will discuss the examples of the above string functions through SQL QUERY:
    SQL QUERY for ASCII function :
    SELECT ASCII(‘ABC’)
    In above query will display the ASCII code of left most character A.


    SQL QUERY for CHAR function:
    SELECT CHAR(65)
    In above query will display the character which is the value of this ASCII code 65.


    SQL QUERY for CHARINDEX function
    SELECT CHARINDEX(‘o’,’Good’)
    In above query will display the starting position of the specified pattern in the expression.


    SQL QUERY for DIFFERENCE function:
    SELECT DIFFERENCE(‘GOOD’,goo)
    In above query compare two strings and will display the similarity between these two strings.


    SQL QUERY for LEFT function:
    SELECT LEFT(‘WEBMASTER’,3)
    In above query will display the part of character string equal to integer expression character from the left.


    SQL QUERY for LEN function:
    SELECT LEN(‘WEBMASTER’)
    In above query will display the length of character in the character expression .


    SQL QUERY for LOWER function:
    SELECT LOWER(“WEBMASTER’)
    In above Query will convert the character in to lower case.


    SQL QUERY for LTRIM function:
    SELECT LTRIM(‘ WEBMASTER’)
    In above query will display the result without any space. LTRIM function will remove the leading blanks from the character string.


    SQL QUERY for PATINDEX function:
    SELECT PATINDEX(‘%MASTER%’,’WEBMASTER’)
    In above query will display the starting position of the first occurrence of the pattern in specified expression.


    SQL QUERY for REVERSE function:
    SELECT REVERSE(‘WEBMASTER’)
    In above query will display the reverse of the character expression.


    SQL QUERY for RIGHT function:
    SELECT RIGHT(‘WEBMASTER’,6)
    In above query will display the part of character string, extracting from the right number of character string in the integer expression.


    SQL QUERY for RTRIM function:
    SELECT RTRIM(‘WEBMASTER ’)
    In above query will display after removing any trailing blanks from the character string.

    [​IMG]

    SQL QUERY for SPACE function:
    SELECT ‘WEBMASTER’+SPACE(2)+’SERVE’
    In above query will display the result and create the space inserted between the first and the second word in the expression.


    SQL QUERY for STUFF function:
    SELECT STUFF(‘blank’,3,3,’i’)
    In above query will display the result, it deletes length characters from character expression from the start and then inserts the character expression 2 into the character expression 1 at the start position.


    SQL QUERY for SUBSTRING function;
    SELECT SUBSTRING(‘WEBMASTER’,2,1)
    In above query will display the result which is part of character string. This expression return the length characters from the starting position of the expression.


    SQL QUERY for UPPER function:
    SELECT UPPER (‘webmaster’)
    In above query will display the result after converting the character string in the upper case.
     
  2. Manish Mishra

    Yellow Belt

    Joined:
    Dec 22, 2015
    Messages:
    1,025
    Ratings:
    +159 / -0
    I believe these are the different function to get printed the different word stream. am i right? Many of these codes i am familiar with but have been long time using any of them, though. Thanks swati for posting this wonderful post.
     

Share This Page