This is a list of all the articles posted on this blog about SQL Server String functions.
Concatenate rows (group concatenation) in MySQL, Hive, SQL Server and Oracle
The CONCAT() function joins the input strings or different column values and returns as a single string. However, if we need to concatenate rows of strings (rows from the same column) into a single string with a desired separator, this will not work. There comes a function that fulfills this requirement and this function known…
Word Count in SQL Server – String_Split Usage
STRING_SPLIT is a table-valued function that splits the words from the input text and returns into rows. This function has been introduced in SQL Server 2016. String_split will help in many ways. 1) You can get the word count from the given text. 2) You can pass the string values to a query. Word Count…
SQL Server – Replicate Function
SQL Server “Replicate” Function repeats a character expression to a specified number of times. It is equivalent to or alternative of Oracle’s RPAD function. Let’s’ check how this will be useful to the developers. SELECT LEN(REPLICATE(2, ’12’)) The above query will return 12 as a result due to 2 is called 12 times (222222222222) hence…
SQL Server – CHARINDEX – Oracle INSTR
There is a ‘CharIndex’ function in SQL Server which is similar to the Oracle ‘Instr’ function. In Oracle, the syntax of the INSTR function is :instr( string1, string2 [, start_position [, nth_appearance ] ] ) ‘String1’ in the above syntax represents the string that you need to search in, whereas String2 is the substring that…
Handling “Json” and “Unstructured” Data in SQL
The below is to understand how we can handle JSON data in prior versions of SQL Server 2016. Sample JSON data is: {“accountNumber”: 2020112, “pin”: 2525}, {“accountNumber”: 2567899, “pin”: 1462} {“accountNumber”: 6789925, “pin”: 2614} {“accountNumber”: 9925678, “pin”: 6142} This can be extracted into columns easily in SQL Server 2016 with the help of following…
Convert Delimited Data Into Columns In SQL Server
This article addresses the conversion of the delimited data into columns in SQL Server. There are two functions called “STRING_SPLIT” and “PARSENAME” that helps us convert the delimited data into a single and multiple columns. Consider the below data set for practice.Data1456, ConvertedData1, SomeOtherData11466, ConvertedData2, SomeOtherData21476, ConvertedData3, SomeOtherData4Let’s create the table and load data in it.–…
SQL Server – CASE Function- Examples
This article talks about an equivalent of Oracle SQL’s DECODE function in SQL Server. There is no such function in SQL Server but through CASE function we can construct the same expression. (Update: IIF can be used as an alternative since SQL Server 2012 onwards) CASE function evaluates a list of conditions and returns one…
Big Data and SQL 