C#: Can we define an abstract class with only parameterized constructor. If no, then what will happen and if yes then what steps you need to do this in order to use abstract class functionality?

Hi Friends,

Hope, you are doing fine.

Wish you all a very Happy New Year, 2018!

During an interview, an interviewer asked me the below question and due to my curiosity and to verify my given answer, I had implemented this and check its behaviour.

The question was as below:

"Can we define an abstract class with only parameterized constructor. 
If no, then what will happen and if yes, then what steps you need 
to do this in order to use abstract class functionality?"

The answer is “Yes”, we can create an abstract class with only parameterized constructor. But before answering to its second part of the questions, let’s actually implemented the small POC:

Open Visual Studio and Create New Project -> Console Application.

Add the below abstract class as below:

 public abstract class CanAbstractClassHaveParameterizedCTOR
 {
 public CanAbstractClassHaveParameterizedCTOR(string input)
 {
 Console.WriteLine("Abstract Base Constructor ====> " + input);
 }
 }

Now, add another class inheriting the above abstract class, as below:

 public class DerivedClassToAbstractClassWithParameterizedCTOR 
: CanAbstractClassHaveParameterizedCTOR
 {
 public void DoTask()
 {
 Console.WriteLine("DoTask...");
 }
 }

With this code, you will get compile error as:

Error: '<Your_Project_Namespace>.CanAbstractClassHaveParameterizedCTOR' 
does not contain a constructor that takes 0 arguments

Because, you are inheriting the abstract class as base class and that abstract class is defined with only parameterized constructor, you need to change your derived class constructor as parameterized constructor inheriting the base class constructor as below:

Modified Code:

using System;
namespace Practice.CSharp
{
 public abstract class CanAbstractClassHaveParameterizedCTOR
 {
 public CanAbstractClassHaveParameterizedCTOR(string input)
 {
 Console.WriteLine("Abstract Base Constructor ====> " + input);
 }
 }

public class DerivedClassToAbstractClassWithParameterizedCTOR 
: CanAbstractClassHaveParameterizedCTOR
 {
 public DerivedClassToAbstractClassWithParameterizedCTOR
      (string abc) : base(abc)
 {
 Console.WriteLine("Derived Constructor ===> " + abc);
 }

public void DoTask()
 {
 Console.WriteLine("DoTask...");
 }
 }
}

You, your code can be compiled and run.

Now write the below code to check if it is working fine…

Program.cs

namespace Practice.CSharp
{
 class Program
 {
 static void Main(string[] args)
 {
DerivedClassToAbstractClassWithParameterizedCTOR obj = new 
DerivedClassToAbstractClassWithParameterizedCTOR("Test String");
 obj.DoTask();

Console.WriteLine("Press any key to quit...!");
 Console.Read();
 }
 }
}

Output Snapshot:

 

 

 

 

 

 

 

 

Conclusion:

Yes, we can define the abstract class with parameterized constructor, but when we inherit this abstract class, we need to define the parameterized constructor in the derived class also inhering the base class constructor.

I, hope that you will like this post and request you to comment or let me know, if you found something not correct to make this post with most suitable details.

Happy Coding 🙂

PowerShell Script: Use of While..Loop{} in powershell by showing how to open Nth instances of a chrome browser.

Hi Friends,

Hope, you are doing fine!

Today, we will see that how can we use while loop in PowerShell script. We will be open Nth number of instances of CHROME browser via PowerShell script.

Please see the below code:

<# PowerShell Script to demo "While Loop"
 in opening the nth number of Chrome Browser Instances ... #>
$i=0;
$limit=10;
while($i -ne $limit){
 Start-Process "chrome.exe" "http://www.microsoft.com";
 $i++;
}

Code-Snippet:

 

How to execute your code in PowerShell script?

There are two ways to execute your PowerShell script:

1. Windows PowerShell ISE window, by pressing green Arrow Key in the top menu list.
2. Windows PowerShell Console Window

Please refer the below snapshot for your further reference:

Snapshot:

Result:

 

That’s it…!

Happy Coding 🙂

Send Email POC using PowerShell script

Hi Friends,

Hope, you are doing fine, while reading this post!

Today, I will share some useful code snippet which helps in sending an email using PowerShell script.

Steps:

1. Open an instance of Windows PowerShell ISE
2. Type the below code:
3. Change the values as per your records within the below code before running the same.

Code-snippet:

<# PowerShell Script: POC to send an email ... #>

<# VARIABLES DEFINITION SECTION ... #>
$Username = "gaurav.lal@xxxxxxxxxxxx.com" 
$Password= "xxxxxxxxxx"
$path = "C:\Users\HP\Downloads\apply_now.jpg"
$emailFrom = "noreply@gmail.com"
$emailFromDisplayName = "ABC Company"
$smtpServer = "smtp.gmail.com"
$smtpPortNumber = "587"
$recipientEmail = "gaurav.lal@xxxxxxxxxxxx.com"

<# BODY OF THE FUNCTION... #>
function SendEmailPOC-Via-PowerShell-Script
         ([string]$emailTo, [string]$attachmentFilepath)
{
 $mailMessage = new-object Net.Mail.MailMessage
 $mailMessage.From = new-object Net.Mail.MailAddress 
                 ($emailFrom, $emailFromDisplayName)
 $mailMessage.To.Add($emailTo)
 $mailMessage.Subject = "Test Email POC from Powershell Script"
 $mailMessage.Body = "Please ignore this email as 
                      this is only the result of 'Test Email POC 
                         from Powershell Script'..."
 $attachment = New-Object Net.Mail.Attachment($attachmentFilepath)
 $mailMessage.Attachments.Add($attachment)
 $smtpClient = new-object Net.Mail.SmtpClient
                      ($smtpServer, $smtpPortNumber)
 $smtpClient.EnableSSL = $true
 $smtpClient.Credentials = New-Object System.Net.NetworkCredential
                              ($Username, $Password)
 $smtpClient.send($mailMessage)
 write-host "Mail Sent"
 $attachment.Dispose()
}

<# NOW WE WILL RUN THE FUNCTION... #>
SendEmailPOC-Via-PowerShell-Script 
          -email $recipientEmail -attachmentFilepath $path;

Code Snapshot:

Now, run the PowerShell script and ensure that there is no error, then check the output file location.

Result Snapshot:

That’s it!

Happy Coding 🙂

T-SQL: Find if “xp_cmdshell” is configured on your SQL Server instance

Hi Friends,

How are you doing?

Today, we will see how to check if the “xp_cmdshell” is already configured on your SQL Server instance or not. We can check the other configurations as well using the same T-SQL.

What is “xp_cmdshell”?

“xp_cmdshell” allow us to run windows commands by using SQL Server, but we need to enable this!

We can check if this configuration is already configured on this instance of SQL Server or not by referring to the system view “sys.configurations“. You can explore this by following this link.

T-SQL Script:

-- CHECK FOR "xp_cmdshell"
SELECT name, CONVERT(INT, ISNULL(value, value_in_use)) AS IsConfigured 
FROM sys.configurations 
WHERE name = 'xp_cmdshell';

-- CHECK FOR "show advanced options"
SELECT name, CONVERT(INT, ISNULL(value, value_in_use)) AS IsConfigured 
FROM sys.configurations 
WHERE name = 'show advanced options';

-- CHECK FOR "Agent XPs"
SELECT name, CONVERT(INT, ISNULL(value, value_in_use)) AS IsConfigured
FROM sys.configurations 
WHERE name = 'Agent XPs';

Snapshot:

Please note that if you found value in “sys.configurations” is not set to 1 then you may need to manually enable the “xp_cmdshell” function.

If you check the defined structure of the sys.configurations system view, you will get the following:

That’s it! Hope, you may like it…

Happy Coding 🙂

 

T-SQL: Find the location of data and log files of the databases within a SQL Server instance.

Hi Friends,

Hope, you are doing fine!

Today, please find below the T-SQL script tip to find the location of the data & log files available within a SQL Server instance. The script is as below:

T-SQL:

SELECT 
        name AS NameOfFile, 
        physical_name AS FileLocation
FROM 
        sys.master_files

Snapshot:

Happy Coding 🙂

 

T-SQL to find I/O usage stats by databases within the SQL Server instance

Hi Friends,

Hope, you are doing fine!

Today, we will see how to extract the I/O statistics for data and log files within a SQL Server instance, with the help of dynamic management view (DMV) named as (“sys.dm_io_virtual_file_stats“).

T-SQL Script:

-- T-SQL to find I/O usage stats by databases within 
-- the SQL Server instance (Database level IO usage stats)
SELECT 
 DB_NAME(database_id) AS [Database Name],
 -- IO READS
 [IO_Reads_In_Bytes] = CAST(SUM(num_of_bytes_read) AS NUMERIC(18, 2)),
 [IO_Reads_In_KB] = CAST(SUM(num_of_bytes_read) / 1024 AS NUMERIC(18, 2)),
 [IO_Reads_In_MB] = CAST(SUM(num_of_bytes_read) / (1024 * 1024) 
AS NUMERIC(18, 2)),
 [IO_Reads_In_GB] = CAST(SUM(num_of_bytes_read) / (1024 * 1024 * 1024) 
AS NUMERIC(18, 2)),
 -- IO WRITES
 [IO_Writes_In_Bytes] = CAST(SUM(num_of_bytes_written) AS NUMERIC(18, 2)),
 [IO_Writes_In_KB] = CAST(SUM(num_of_bytes_written) / 1024 
AS NUMERIC(18, 2)),
 [IO_Writes_In_MB] = CAST(SUM(num_of_bytes_written) / (1024 * 1024)
 AS NUMERIC(18, 2)),
 [IO_Writes_In_GB] = CAST(SUM(num_of_bytes_written) / (1024 * 1024 * 1024)
 AS NUMERIC(18, 2)),
 -- IO READS + IO WRITES
 [IO_In_Bytes] = CAST(SUM(num_of_bytes_read + num_of_bytes_written) 
AS NUMERIC(18, 2)),
 [IO_In_KB] = CAST(SUM(num_of_bytes_read + num_of_bytes_written) / 1024
 AS NUMERIC(18, 2)),
 [IO_In_MB] = CAST(SUM(num_of_bytes_read + num_of_bytes_written) 
/ (1024 * 1024) AS NUMERIC(18, 2)),
 [IO_In_GB] = CAST(SUM(num_of_bytes_read + num_of_bytes_written) 
/ (1024 * 1024 * 1024) AS NUMERIC(18, 2))
FROM 
 sys.dm_io_virtual_file_stats(NULL, NULL) AS IO_Stats_DMV
GROUP BY database_id
ORDER BY database_id

Snapshot:

Hope, this T-SQL script will be useful when you need to see which database is the busiest database within a SQL Server instance.

Happy Coding 🙂

T-SQL: Find the currently running queries in sql server

Hi Friends,

Hope, you are doing fine!

Please find below the T-SQL script to find the T-SQL batches/queries/statements, which is/are still running as on current time on a SQL Server instance!

T-SQL Script:

SELECT
 queryRequest.session_id, 
 queryRequest.start_time, 
 queryStatement.text
FROM
 sys.dm_exec_requests queryRequest
 CROSS APPLY
 sys.dm_exec_sql_text(queryRequest.sql_handle) queryStatement
GO;

-- DMV (EXTRACT Complete COLUMNS FROM THIS DM VIEW)
SELECT * FROM sys.dm_exec_requests WHERE sql_handle IS NOT NULL
GO;

Please find below the snapshot of a result of this query:

Snapshot:

That’s it…!

Hope, this post may be useful to someone!

Happy Coding  🙂

T-SQL inbuilt function to retrieve all the supported collations within SQL Server

Hi Friends,

Hope, you are doing fine!

Today, we will see the demo of T-SQL in-built TVF (Table Valued Function) function “fn_helpcollations()” which help us to retrieve all the supported collations within SQL Server.

Please refer the below link for your reference:

https://msdn.microsoft.com/en-us/library/ms187963.aspx

Syntax:

fn_helpcollations()

T-SQL:

SELECT * FROM fn_helpcollations()

Snapshot:

That’s it! Also you can apply WHERE clause to filter your result set as well! Please share and press like, if you enjoyed this post! 🙂

Happy Coding 🙂

Tooltip Demo using jQuery and Asp.Net

Hi Friends,

Hope, you are doing fine!

Today, we will see how to implement a tooltip on some controls using jQuery without using any jQuery plugin.

I know this is very simple, but can be helpful to someone who is looking the similar code snippet. Hence, today I am going to post the same so that we all can take the reference of the same and if someone has the better solutions, then I will really appreciate if they can comment out in this post so that we all can learn together!

Aspx code:

<%@ Page Language=”C#” AutoEventWireup=”true” CodeFile=”tooltipdemo.aspx.cs”
Inherits=”tooltipdemo” %>
<!DOCTYPE html>
<html xmlns=”http://www.w3.org/1999/xhtml”&gt;
<head runat=”server”>
<title>Demo-Show Tooltip using jQuery and ASP.NET</title>
http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js
http://js/demoTooltip.js
<style>
.hover-textInput { color:red; float: right; }
</style>
</head>
<body>
<form id=”form1″ runat=”server”>

Text-1:
Text-2:
Text-3:
Text-4:

</form>
</body>
</html>

Snapshot:

ASPX code

 

Now, add the JS file (demoTooltip.js) to your project and add the below code:

jQuery Code:

$(document).ready(function () {
 fillTexts();
 showToolTip();
});
// FUNCTION TO "SHOW TOOLTIP..."
function showToolTip() {
 $('#divMain .inputText').each(function (idx, itm) {
 var currElemt = $(this);
 $(currElemt).hover(function () {
 $(currElemt).closest('td').find('.tooltip').
html(currElemt.attr('value')).fadeIn();
 }, function () {
 $(currElemt).closest('td').find('.tooltip').hide();
 });
 });
}
//FUNCTION TO FILL TEXT 
function fillTexts() {
 $('#divMain .inputText').each(function (idx, itm) {
 var currElemt = $(this);
 $(currElemt).val('Text-' + (idx + 1));
 $(currElemt).closest('td').append('<span class="tooltip hover-textInput">
</span>');
 $(currElemt).keypress(function () {
 updateToolTipText($(this));
 }).keyup(function () {
 updateToolTipText($(this));
 });
 });
}
// FUNCTION TO UPDATE TOOLTIP SPAN WITH UPDATED INPUT TEXT...
function updateToolTipText(obj){
 if (obj !== null && obj !== undefined) {
 $(obj).closest('td').find('.tooltip').html(obj.attr('value'));
 }
}

Snapshot:

jQuery Code

That’s it!

Result:

Result

 

You can also download the code snippet in text format from here.

If you like this post, then please share and press like!

Happy Coding 🙂

T-SQL Fun: Guess your age based on two parameters

Hi Friends,

Hope, you are doing fine! Today is the last day of 2016 and I hope that this year brought a lot of happiness and good lessons to all of our life. I wish you all a very happy and prosperous new year 2017 in advance!

Today, I will share a T-SQL for fun to demo a trick to find your age, based on two parameters. I know that this could be also done directly in T-SQL using many date’s inbuilt functions but let’s solve this by some other alternate/trick.

Let’s start now:

Parameters to be given by the end user, are as below:

1. Number (between 1 to 10)
2. DOB (in mm/dd/yyyy format)

Pseudocode‘s steps:

1. Think a number between 1 to 10.
2. Multiply the number 2 with the number in Step-1
3. Add 5 to the result, we get in Step-2.
4. Multiply 50 to the result, we get in Step-3.
5. If you already had your Birthday as compare to current date, Add 1766 to the result, we get in Step-4, else add 1765.
6. Subtract your Birth Year from the result, we get in Step-5
7. The first two digit will be your Number (1st Parameter) and the rest will be your Age.

Doesn’t it sound interesting?

Let’s try this with below the T-SQL script:

T-SQL Script:

--PUZZLE: T-SQL FUN TO GUESS YOUR AGE BASED ON TWO INPUTS....
DECLARE 
 @Num INT = 10, -- NUMBER SHOULD BE BETWEEN 1 TO 10
 @DOB DATETIME = '10/18/1912' -- YOUR DATE OF BIRTH (mm/dd/yyyy FORMAT)
BEGIN
 DECLARE @Result INT = 0, @NumToAddIfBDPast INT = 1766, 
@NumToAddIfBDDue INT = 1765,
 @NumLength INT = (CASE WHEN @Num BETWEEN 1 AND 9 THEN 1 ELSE 2 END), 
 @CurrentDate DATETIME = DATEADD(ss, -1, CAST((CONVERT(VARCHAR, 
DATEADD(d, 1, GETDATE()), 101) + ' 00:00:00') AS DATETIME))
 SET @DOB = CAST((@DOB + ' 00:00:00') AS DATETIME)
 SET @Result = @Num * 2 -- MULTIPLY THE GIVEN NUMBER BY 2 
-- AND ASSIGN IT TO THE RESULT
 SET @Result = @Result + 5 -- ADD 5 TO THE RESULT
 SET @Result = @Result * 50 -- MULTIPLY THE RESULT WITH 50 
-- AND ASSIGN IT BACK TO THE RESULT
 SET @Result = @Result + 
 (CASE WHEN (MONTH(@DOB) <= MONTH(@CurrentDate) 
AND DAY(@DOB) < DAY(@CurrentDate)) 
 THEN @NumToAddIfBDPast ELSE @NumToAddIfBDDue END)
 -- SUBTRACT THE YEAR OF BIRTH FROM THE RESULT
 SET @Result = @Result - YEAR(@DOB)
 SELECT @Result AS FinalResult, 
 CASE WHEN (CAST(SUBSTRING(CAST(@Result AS VARCHAR), 1, @NumLength) AS INT) 
 > @Num) 
 THEN (CAST(SUBSTRING(CAST(@Result AS VARCHAR), 1, @NumLength) AS INT) 
 - (CAST(SUBSTRING(CAST(@Result AS VARCHAR), 1, @NumLength) AS INT) - @Num))
 ELSE
 CAST(SUBSTRING(CAST(@Result AS VARCHAR), 1, @NumLength) AS INT)
 END AS YourNumber,
 CAST((CASE WHEN (CAST(SUBSTRING(CAST(@Result AS VARCHAR), 1, @NumLength) 
   AS INT) > @Num) 
 THEN CAST((CAST(SUBSTRING(CAST(@Result AS VARCHAR), 1, @NumLength) AS INT) 
  - @Num) AS VARCHAR)
 + SUBSTRING(CAST(@Result AS VARCHAR), (LEN(CAST(@Result AS VARCHAR)) - 1), 
     LEN(CAST(@Result AS VARCHAR)))
 ELSE
 SUBSTRING(CAST(@Result AS VARCHAR), (LEN(CAST(@Result AS VARCHAR)) - 1), 
    LEN(CAST(@Result AS VARCHAR)))
 END) AS INT) AS YourAge
END
GO

Snapshot:

Hope, you will enjoy this T-SQL trick and may give it a try, at least once!

Happy Coding  🙂

Find all available Dynamic Management Views (DMVs) and Dynamic Management Inline Functions (DMIFs) using T-SQL in SQL Server

Hi Friends,

Hope, you are doing fine!

Please find below the T-SQL script to find all available Dynamic Management Views (DMVs) and Dynamic Management Inline Functions (DMIFs) using T-SQL in SQL Server Instance.

Actually, this script is the answer of one of the random question asked by my friend that “How many Dynamic Management Views (DMVs) available in SQL Server ?”

T-SQL Script:

-- 1st Way (By Using sys.system_objects system table)
SELECT *
FROM sys.system_objects 
WHERE name LIKE 'dm_%' 
ORDER BY type, name

-- 2nd Way (By Using sys.sysobjects system table)
SELECT * 
FROM sys.sysobjects 
WHERE name LIKE 'dm_%' 
ORDER BY type, name

Snapshot:

That’s it, my friend! Hope you can explore this further as per your need!

Happy Coding 🙂

T-SQL to read JSON formatted data in MS SQL SERVER 2016

Hi Friends,

How are doing today?

Well, today I will demonstrate you “how to convert json data / document back to sql server tabular formatted data“. You may have already gone through my last post “JSON SUPPORT in MS SQL SERVER 2016“. If not, then you can check this link first to get some idea first!

To read a JSON formatted data / document, we will be using OPENJSON() function.  The syntax of the OPENJSON() function is as below:

Syntax:

OPENJSON( jsonExpression [ , path ] ) 
[ 
 WITH ( 
 colName type [ column_path ] [ AS JSON ] 
 [ , colName type [ column_path ] [ AS JSON ] ] 
 [ , . . . n ] 
 ) 
]

Let’s take the below T-SQL script to have demo on this function(), which will help us to understand how we can query the json data and convert it back to sql server tabular format.

T-SQL Script:

DECLARE @jsonData VARCHAR(MAX) = ''
-- ASSIGN JSON Data to variable @jsonData...
SET @jsonData = (SELECT TOP (2) id, joined, ip, 
 latitude, longitude FROM [dbo].[visitors] 
 WHERE latitude IS NOT NULL AND longitude IS NOT NULL 
 ORDER BY NEWID() 
 FOR JSON AUTO, INCLUDE_NULL_VALUES)
-- EXTRACT @jsonData...
SELECT @jsonData AS JsonData
-- READ @jsonData WITHIN SELECT USING 'OPENJSON()' function...
SELECT * FROM OPENJSON(@jsonData) 
 WITH (
 id int, joined datetime, ip varchar(50), 
 latitude DECIMAL(18,10), longitude DECIMAL(18,10) 
 )
GO

Snapshot:

Hope, you will like this post and will share your feedback (, if any).

Thanks!

Happy Coding  🙂

“JSON Support”: One of the new features introduced in MS SQL Server 2016

Hello friends,

Hope, you are doing fine while reading this post!

MS SQL Server 2016 has introduced one of the coolest feature in this version. Now, we can also process the sql data into json format and also read / process json data/files back to sql server tables.

Before moving ahead, it is assumed that you know what JSON is. If not, then let me make you understand on a high level.

WHAT IS JSON?
JSON stands for Java Script Object Notation. It is a minimal, readable format 
for structuring data. It is used primarily to transmit data between a server 
and web application, as an alternative to XML. For more details, please visit 
the official JSON website.

Before SQL Server 2016 version, SQL Server supports the XML manipulation but now we can also play with json data which is most popular format used for the data interchange between different platforms and application without any glitches in-between.

How we can format a SQL query result as JSON?

We can export data from SQL Server as JSON, or format query results as JSON, by adding the FOR JSON clause to a SELECT statement.

FOR JSON clause is having further two options which can be used to structure the data as per user’s requirement or we can have the data as it is mentioned in SQL Server.

• USE PATH mode: When you use PATH mode with the FOR JSON clause, you maintain full control over the format of the JSON output. You can create wrapper objects and nest complex properties.

SELECT TOP (1) 
 id AS VisitorID, 
 joined AS 'Detail.Joined', 
 ip AS 'Detail.IP_Address', 
 latitude AS 'Geography.Latitude', 
 longitude AS 'Geography.Longitude'
FROM [dbo].[visitors] 
FOR JSON PATH, INCLUDE_NULL_VALUES
GO

Snapshot:

 

• USE AUTO mode: When you use AUTO mode with the FOR JSON clause, the JSON output is formatted automatically based on the structure of the SELECT statement.

SELECT TOP (2) id, joined, ip, latitude, longitude 
FROM [dbo].[visitors] 
FOR JSON AUTO
GO

Snapshot:

In the above screenshot, you will observe that the latitude and longitude columns are not extracted in the json formatted result. The reason is these columns are having NULL values for the two rows.

Snapshot:

To get these columns also to be extracted in json format, we can use the INCLUDE_NULL_VALUES keyword after FOR JSON <mode> within SELECT statement, as below:

SELECT TOP (2) id, joined, ip, latitude, longitude 
FROM [dbo].[visitors] 
FOR JSON AUTO, INCLUDE_NULL_VALUES
GO

Snapshot:

 

Hope, you will like this post and will really appreciate your feedback to improve the content of this post!

 

Happy Coding 🙂

 

 

SQL Server 2016’s new in-built function (i.e. SPLIT_STRING()) to split the string into rows using delimiter

Hi Friends,

Hope, you are doing fine!

Today, I found the SQL Server 2016 new in-built function (SPLIT_STRING()) which will make the developer’s life more easier, when it comes to split the string into rows using some delimiter.

Earlier to MS SQL Server 2016 version, we were using either XML Path() clause to split the string or to define some custom user-defined function to achieve this task.

You can explore this new function more by visiting this link.

Syntax:

SPLIT_STRING(<input_string>, <delimiter_character>)

Please note that the delimiter_character should be of single character expression and should not be some non-empty string.

Sample T-SQL:

SELECT * FROM STRING_SPLIT('My name is Gaurav Lal 
and I am going to demo today the new built-in-function 
of SQL-Server-2016 i.e. STRING_SPLIT()', ' ');

Snapshot (Result):

You can also apply this function within your SQL select statement. Let’s try this with an example:

Consider a case, where you have a table for CarPooling which contains two columns “CarNo” and “CarMembers“.

Note: CarMembers column is containing the comma-separated values in it.

Table and Data creation T-SQL:

DECLARE @CarPooling TABLE 
(
 Sno INT IDENTITY(1,1),
 CarNo VARCHAR(100), 
 CarMembers VARCHAR(300)
)
INSERT INTO @CarPooling 
 (carNo, CarMembers)
VALUES 
 ('DL-05-SW-4512', 'Jai, Neeraj, Mohit, Sachin, Gaurav'),
 ('HR-26-TQ-9085', 'Satya, Harinder, Vikas'),
 ('UP-16-RT-7775', 'Amrish, Ashish, Harsh, Amit')
SELECT * FROM @CarPooling
GO

Snapshot:

Now, if you wish to seperate this csv column into separate row, then you can use this STRING_SPLIT() function within your select statement using CROSS APPLY. Let’s have a look into next T-SQL below:

Solution (T-SQL):

DECLARE @delimiter_character CHAR(1) = ','
DECLARE @CarPooling TABLE 
( 
 Sno INT IDENTITY(1,1), 
 CarNo VARCHAR(100), 
 CarMembers VARCHAR(300)
)
INSERT INTO @CarPooling 
 (carNo, CarMembers)
VALUES 
 ('DL-05-SW-4512', 'Jai, Neeraj, Mohit, Sachin, Gaurav'), 
 ('HR-26-TQ-9085', 'Satya, Harinder, Vikas'), 
 ('UP-16-RT-7775', 'Amrish, Ashish, Harsh, Amit')
-- EXTRACT ORIGINAL DATA...
SELECT * FROM @CarPooling
-- Query using STRING_SPLIT() function to seperate the above 
-- comma-seperated-value defined in the column and make it 
-- as a seperate row value...
SELECT CarNo, LTRIM(RTRIM(value)) AS Individual_Member_Name
 FROM @CarPooling 
 CROSS APPLY STRING_SPLIT(CarMembers, @delimiter_character)
GO

Snapshot:

Please note, if you will pass the non-empty string as a delimiter, then you will get an error as below:

Msg 214, Level 16, State 11, Line 3
Procedure expects parameter 'separator' of type 'nchar(1)/nvarchar(1)'.

Snapshot:

Hope, you will enjoy this new feature and will share your valuable feedback!

Happy Coding 🙂

Fun with T-SQL: Get FIBONACCI SERIES using T-SQL and CTE

Hi Friends,

Hope, you are doing good!

Today, let’s create CTE demo to get FIBONACCI SERIES using T-SQL and CTE (Common Table Expression)

T-SQL Script:

DECLARE @FS_Length INT = 15 -- Defined Maximum Series Length
;WITH CTE_FIBONACCI_SERIES
AS
(
 SELECT 
 0 AS level, 
 CAST(0 AS FLOAT) As FNum, 
 CAST(1 AS FLOAT) AS NextValue
 UNION ALL
 SELECT 
 a.level + 1 AS level, 
 a.NextValue AS FNum, 
 a.FNum + a.NextValue AS NextValue 
 FROM CTE_FIBONACCI_SERIES a 
 WHERE level <= @FS_Length
)
SELECT FNum FROM CTE_FIBONACCI_SERIES 
OPTION (MAXRECURSION 0)
GO

Snap-shot:

That’s it… 🙂

Hope, you have enjoyed this!

Happy Coding 🙂