SQL Server String Functions

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

Continue reading

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

Continue reading

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

Continue reading

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

Continue reading

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

Continue reading

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, SomeOtherData4 Let’s create the table and load

Continue reading

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

Continue reading

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s