Skip to main content

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:

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

Comments

Popular posts from this blog

difference between structure and union in C Language

In c language article we will see the difference between union and structure. Both are the user define datatype in c language. See the table which is mentioned below: ASP.NET Video Tutorial Series Structure Union1.The keywordstruct is used to define a structure 1. The keyword union is used to define a union. 2. When a variable is associated with a structure, the compiler allocates the memory for each member. The size of structure is greater than or equal to the sum ofsizes of its members. The smaller members may end with unused slack bytes. 2. When a variable is associated with a union, thecompiler allocates thememory by considering the size of the largest memory. So, size of union is equal to the size of largest member. 3. Each member within a structure is assigned unique storage area of location. 3. Memory allocated is shared by individual members of union. 4. The address of each member will be in ascending order This indicates that memory for each member will start at different offset v…

Difference between Linear search and Binary Search in c language

SQL Video Channel : Download all SQL Video



Binary Search Linear Search Works only on sorted items. such as  1,2,3,4,5,6  etc
Works on sorted as well as unsorted items. 12,4,5,3,2,1 etc Very efficient if the items are sorted Very efficient if the items are less and present in the beginning of the list. such as Suppose your list items are : 12,3,4,5,1 and you want to search 12 number then you get beginning in the list. Works well with arrays and not on linked lists. Works with arrays and linked lists.
Number of comparisons are less More number of comparisons are required if the items are present in the later part of the array or its elements are more.

Memory representation of Linked List Data Structures in C Language

Memory representation of Linked List

             In memory the linked list is stored in scattered cells (locations).The memory for each node is allocated dynamically means as and when required. So the Linked List can increase as per the user wish and the size is not fixed, it can vary.

               Suppose first node of linked list is allocated with an address 1008. Its graphical representation looks like the figure shown below:


      Suppose next node is allocated at an address 506, so the list becomes,



  Suppose next node is allocated with an address with an address 10,s the list become,


The other way to represent the linked list is as shown below:




 In the above representation the data stored in the linked list is “INDIA”, the information part of each node contains one character. The external pointer root points to first node’s address 1005. The link part of the node containing information I contains 1007, the address of next node. The last node …