Use String Functions to Manipulate String Values in Sql Server: SQL Programming

In Sql Programming, programmer can use the string functions to manipulate the string values in the result set. There are list of string functions used in sql server and explained in this article. For example, to display only the first eight characters of the values in a column, you can use the left ( ) string function.

String functions are used with the char and varchar data types. The SQL Server provides string functions that can be used as a part of the character expression. These functions are used for various operations on string.

Syntax:
    SELECT function_name (parameters)

Where

• Function_name is the name of the function
• parameters are the required parameters for the string function.

The following table lists the string functions provided by SQL Server

• Ascii, returns the ASCII code of the leftmost character, e.g. SELECT ascii (‘ABC’) will return ascii code of 'A'.
• Char, return the character equivalent of the ASCII code value, e.g. SELECT char (65)   
• Charindex, returns the starting position of the specified pattern in the expression e.g. SELECT charindex (‘E’, ‘HELLO’)
• Difference, compares two strings and evaluates the similarity between them, returning a value from 0 through 4. The value 4 is the best match e.g. SELECT difference (‘HELLO’, ‘hell’)
• Left, returns apart of the character string equal in size to the integer_expression    characters from the left e.g. SELECT left(‘RICHARD’, 4) will return RICH
• Len, returns the number of characters in the character_expression e.g. SELECT len(‘RICHARD’)
• Lower, returns after converting character_expression to lower case e.g. SELECT lower (‘RICHARD’)
• Ltrim, removes leading blanks from the character expression e.g. SELECT ltrim (‘RICHARD’)
• Patindex, returns staring position of the first occurrence of the pattern in the specified expression, or zeros if the pattern is not found e.g. SELECT patindex (‘%BOX%’, ‘ACTIONBOX’)
• Reverse, returns reverse of the character_expression e.g. SELECT reverse (‘ACTION’)
• Right, returns a part of the character string, after extracting from the right the number of characters specified in the integer_expression e.g. SELECT right (‘RICHARD’, 4) will return HARD
• Rtrim, returns after removing any trailing blanks from the character expression e.g. SELECT rtrim (‘RICHARD   ’)
• Space, spaces are inserted between the first and second word e.g. SELECT ‘RICHARD’+space (2)+’HILL’, will add two spaces between 1st and 2nd word.
• Str, converts numeric data to character data where the length is the total length, including the decimal point, the sign, the digits, and the spaces and the decimal is the number of places to the right of the decimal point e.g. SELECT str (123.45, 6, 2)
• Stuff, deletes the number of characters as specified in the character_expression1 from the start and then inserts char_expression2 into character_expression1 at the start position e.g. SELECT stuff (‘Weather’, 2,2, ‘I’) will returns ‘wither’.
• Substring, returns the part of the source character string from the start position of the expression e.g. SELECT substring (‘weather’, 2,2) will return ‘ea’.
• Upper, converts lower case characters to upper case e.g. SELECT upper (‘Richard’)

The following SQL query uses the upper string function to display data in uppercase. The Name, DepartmentID, and GroupName columns are retrieved from the Department table and the data of the Name column is displayed in uppercase with a user-defined heading, Department Name:
 

SELECT 'Department Name' = upper (Name), DepartmentID, GroupName
FROM HumanResources.Department
 

Outputs: