SQL Server String FunctionsA string function accepts a string value as an input and returns a string value regardless of the data type (string or numeric). There are several built-in string functions in SQL Server that developers can use to manipulate the character data effectively. String methods in SQL Server are extremely useful for processing the string data type. SQL Server stores string and character data using a variety of data types such as varchar, nvarchar, and char. Therefore, the string functions can be used to obtain the desired and precise results effectively. The following image will show all string functions used in SQL Server: This article will cover most of the common string functions used in SQL Server and explain each of them with examples. The following table listed each of the functions with a brief description:
Let us see some of the most common string function examples. Example1: This example will return the numerical ASCII value of the character 'A' and 'B': Executing the statement will return the below result: Example2: This example will search for the string 'Javatpoint' in the 'Hello Javatpoint' and, if it finds, return the index of a specific string expression within a given string Executing the statement will return the below result. Here the first searching for the string Javatpoint returned 7, but when we specified the start location as 8, it returned 0 since no occurrence is found after this index. Example3: This example will use the CONCAT() function that takes more than two input string and return those string into a single output after concatenation: Executing the query will display the below output: Example4: This example uses the SOUNDEX() function that accepts a string as input and produces a four-character string based on how it is spoken. It converts the first character of the code into the upper case and becomes the input string's first character. The code's remaining characters are numbers that represent the expression's letters. Here is the result: We can see from the results that the SOUNDEX() method produces the same result for 'J' and 'Ja' because the character 'a' is disregarded (as the A, O, U, E, I, Y, H, W is ignored until they are not the first character). Also, because the SOUNDEX() function only needs the first four characters, the results of Javatpoint and Javatpoint Noida are identical. Example5: This example uses the DIFFERENCE() function that measures the similarity of the two strings. This statement gives the below output: Here, we can see that the DIFFERENCE() function returns a value of 4 as the SOUNDEX() function returns the same value for Hello and Hello World, indicating that they are both based on the SOUNDEX() value. The DIFFERENCE() function, on the other hand, returns 1 for Hello and Bicycle, indicating that they are not similar. Example6: This example uses the LEFT() and RIGHT() functions to extract a specific number of characters from the left-side or right-side of a string. Executing the statement will return the below result: Example7: This example uses the LOWER() and UPPER() to change the character case of an input string. The functions LOWER() and UPPER() are used to modify the letter case to lower case and upper case, respectively. Executing the statement will return the below result: Example8: This example will use the LTRIM() and RTRIM() functions to remove additional spaces from an input string's left or right sides. Executing the statement will return the below where we see that spaces from the left and right of the string javatpoint are removed: Example9: This example uses the QUOTENAME() function that returns a Unicode string including the delimiters, converting the input string into a valid delimited identifier: Executing the statement will return the below output: Example10: This example uses the REPLICATE() function to repeat the string a specified number of times. Executing the statement will return the below result: ConclusionThis article explains a complete overview of several SQL Server's built-in string functions, as well as some examples and pictures.
Next TopicSQL Server RENAME Database
|