How to split comma separate string in Sql server

This is common requirement for almost every developer to extract data from comma separated string. In Sql Server 2016, we have STRING_SPLIT  inbuilt function to extract data from string. But for people those are using prior versions of Sql Server like me 😥 , still have to strugle with this issue. If you Google about this function, you’ll find out more than 10 ways of extrating data from comma separted string. Here, I present my own version 🙂

--SELECT * FROM DBO.StringSplitter('Sunil,Sanjay,Neha,Aradhaya,Nikhil',',')
--SELECT * FROM DBO.StringSplitter('Sunil',',')
CREATE FUNCTION StringSplitter
(
	@STR VARCHAR(100), -- Input string 
	@SEPARATOR VARCHAR(1) -- Separator character 
)
RETURNS @TEMP TABLE (NAME VARCHAR(100)) -- Return data in tabular format 
AS
BEGIN
DECLARE @START INT= 1, @STRLENGTH INT = LEN(@STR);
-- First check whether input string is comma separater string or not.
-- If NOT then insert the whole string into our table variable and 
-- return. 
IF(CHARINDEX(',', @STR, @START) = 0)
 BEGIN
  INSERT INTO @TEMP SELECT @STR
 END
-- If input string is a comma separated string then go in and extract data 
ELSE
 BEGIN
  -- Before loop over the input string first add a ',' (comma)
  -- at the end of the input string because if we don't add
  -- then last comma separated word will be missed.
  SET @STR += ',';
  -- loop till @START variable is smaller than length of the input string
  WHILE(@START < @STRLENGTH)
   BEGIN
        -- get the substring from begining of the input string till it finds a
	-- comma. Once you find a comma set this value to your @START variable
	-- by increasing it's value by 1 because next search should start from
	-- next character after found comma 
	INSERT INTO @TEMP 
	SELECT SUBSTRING(@STR, @START, CHARINDEX(',', @STR, @START) - @START)
	SET @START = CHARINDEX(',', @STR, @START) + 1
   END
END
RETURN;
END
Advertisements

What is the difference between ExecuteNonQuery(), ExecuteReader() and ExecuteScalar()?

ExecuteNonQuery(): It retuns onbly number of affected rows. Suppose you added anew row and deleted one row and updated 4 rows, so its output will be 6.

using (var con = new SqlConnection(ConnectionString))
using (var cmd = con.CreateCommand())
{
    con.Open();
    cmd.CommandText = "Your Stored Procedure name";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@id", index);
    var result = cmd.ExecuteReader();
}

ExecuteScalar(): It retuns first row’s first column’s value. So suppose friom Database you return list of employees and number of rows are 10 and fields are 5. And first field is EmployeeName, so ExecuteScalar wil retuns first row’s first column’s value.

using (var con = new SqlConnection(ConnectionString))
using (var cmd = con.CreateCommand())
{
    con.Open();
    cmd.CommandText = "Your Stored Procedure name";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@id", index);
    var result = cmd.ExecuteScalar();
}

ExecuteReader():  It works only in connected mode, that means your connection to Database is still open. Also it can have multiple result sets but you can read one at a time and only in forward direction. Once you pass a set you can’t go back and read it again. This is readonly.

It returns IDataReader by which you can read data. For current set you can user READ() method and for moving to next set you can use NextResult() methods.

using (var con = new SqlConnection(ConnectionString))
using (var cmd = con.CreateCommand())
{
    con.Open();
    cmd.CommandText = "Your Stored Procedure name";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@id", index);
    using (var reader = cmd.ExecuteReader())
    {
        while(reader.Read()) // Current set, reading set 1
        {
            learerLabel.Text = reader.GetString(reader.GetOrdinal("yourcolumn"))
        }
        reader.NextResult(); // Next set, load set 2
        while(reader.Read()) // reading set 2
        {
          // code to read set 2
        }
    }
}

Top with OrderBy in Sql Server

Suppose there are 50 employees in our Employee table. And our job is to fetch employees from 11th to 20th position. There are so many ways of doing this, you can use OFFSET that is introduced in Sql Server 2012, you can use EXCEPT etc. Below are the queries for EXCEPT and OFFSET:

Data script:
declare @counter int = 1
while(@counter <= 50)
begin
insert into Employee (name,salary) select CONCAT('name - ',@counter),100*@counter;
set @counter += 1;
end
GO
Select * from Employee

Query with OFFSET:
SELECT *
FROM Employee e
ORDER BY e.id
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY

Query with EXCEPT:
Select Top(20) * From Employee
Except
Select Top(10) * From Employee

We are supposed to get the same result (as per above queries) using TOP, see below query:

Query with TOP:
select top(10) * from (
Select Top(20)* from Employee
) as t order by t.id desc

But above query producess unexpected result. It supposed to produce employees from 11th to 20th position. But instead of that it returns employees from 50th to 41th position, why?

Answer: When we use TOP, it should never be used without ORDER BY. If we use it without ORDERBY then it returns some unexpected result, that’s why we are getting list of employees from 50th to 41th position. So, to fix our query we’ll have to add ORDER BY in our inner query too. See below working script:

select top(10) e.*
from (Select Top(20) e.*
from Employee e
order by t.id asc
) e
order by e.id desc;

So, Thumb rule is, NEVER USE TOP WITHOUT ORDER BY.

Convert a string into Title case in Sqlserver

Input: This is test input data!

output: This Is Test Input Data!

CREATE FUNCTION funTitleCase
(
@str VARCHAR(1000)
)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @counter INT = 0, @length INT = 0;
SET @length = LEN(@str);
SET @str = RTRIM(STUFF(@str,1,1,UPPER(SUBSTRING(@str,1,1))));
WHILE(@counter < @length)
BEGIN
IF(SUBSTRING(@str,@counter,1) =’ ‘ and SUBSTRING(@str,@counter+1,1) like ‘[a-zA-Z]’)
BEGIN
SET @str = STUFF(@str,@counter+1,1,UPPER(SUBSTRING(@str,@counter+1,1)));
END
SET @counter += 1;
END
RETURN ISNULL(@str,”);
END

Search Records with Single Quotes in Sql Server

Sometime we need to find some records those are having single quotes(” ‘ “), below is the query to find out such records:

DECLARE @t TABLE(ItemID int identity(1,1), ItemDescription varchar(100));
INSERT INTO @t(ItemDescription)
VALUES(‘This is men”s shirt’),(‘This is girl”s skirt’),(‘This is short shirt’),(‘This is test item’)

SELECT *
FROM @t t
WHERE t.ItemDescription LIKE ‘%”%’

 

How to fin Max value among all the columns in a row in Sql Server

Sometimes we need max or min value among all the columns of a table for a each row. We all know how to do it for a column but needs little twist for a row. See below script to achieve it in Sql Server:

DECLARE @t TABLE(a INT,b INT,c INT,d INT,e INT,f INT );
INSERT @t VALUES(123,200,300,904,65,6),(9,8,7,11,56,0),(14,26,15,45,76,1)

SELECT *,(
SELECT max(val)
FROM (
VALUES(a),(b),(c),(d),(e),(f)
) AS value(val)
) as maxvalue
FROM @t t