Sum Comma Separated values in SQL Server

Scenario: Comma Separated values are stored in a single column.
Need to show the output summation of values in single column.

Here is the expected Before/After:

Table structure:

create table SumCommaSeparated
(
  id int identity(1,1),
  value varchar(200)
)

Insert Values into table:

insert into SumCommaSeparated values('20,12,14,34');
insert into SumCommaSeparated values('20,15,14,34,82,90');
insert into SumCommaSeparated values('200');

Create SQL function to sum up and show the output:

Create FUNCTION [dbo].[fnSplit]
(
@list  NVARCHAR(2000)
) 
RETURNS TABLE AS
RETURN

   WITH SplitRecords(LoopStart, LoopEnd) AS (
--Split Column values till End
     SELECT LoopStart = 1,
        LoopEnd = CHARINDEX(',' COLLATE Latin1_General_CI_AS, @list + ',')
     UNION ALL
     SELECT LoopStart = LoopEnd + 1,
            LoopEnd = CHARINDEX(',' COLLATE Latin1_General_CI_AS,@list + ',', LoopEnd + 1)

     FROM   SplitRecords
WHERE  LoopEnd > 0
  )
  SELECT sum(cast(LTRIM(RTRIM(SUBSTRING(@list, SplitRecords.LoopStart,
CASE WHEN SplitRecords.LoopEnd > 0 THEN SplitRecords.LoopEnd - SplitRecords.LoopStart ELSE 0 END))) as bigint)) 
--Cast your output according to requirement
AS VALUE
  FROM   SplitRecords
  WHERE  LoopEnd > 0

SQL Script:

select SCSep.id,SCSep.value BeforeAddition,fn.VALUE as AfterAddition
from SumCommaSeparated SCSep
cross apply [dbo].[fnSplit](SCSep.value) as fn

How to find underscore(_) using like operator in SQL Server

Like operator is used to match the specified pattern.

I came across the below scenario where i need to search the underscore using like operator.

Here is the example for title table

select Name from title where Name like 'santosh%'

Name

Santosh

Santosh_Kumar

Santoshkumar

select Name from title where Name like 'santosh_%'

Name

Santosh_Kumar

Santoshkumar

But I need the output only Santosh_Kumar.

So modify the SQL script like below. and output will be as expected.

select Name from title where Name like 'santosh[_]%'

Or use ESCAPE Operator

select Name from title where Name like 'santosh\_%' escape '\'

Name

Santosh_Kumar

Difference between COALESCE and ISNULL function in SQL Server

ISNULL()

Syntax: ISNULL(expression, Replace_value )

--It takes two parameter

--SQL Server Specific

--Return 1^st parameter if it is not null.

Example:

SELECT ISNULL(NULL, 'SQLServer');

Result: SQLServer

SELECT ISNULL('Inside', 'Forum');

Result: Inside

SELECT ISNULL(NULL, 10);

Result: 10

SELECT ISNULL(18, 88);

Result: 18

SELECT ISNULL(NULL, '2015-10-20');

Result: '2015-10-20'

COALESCE()

Syntax: COALESCE( expression1, expression2, ... expression_n )

-It takes multiple parameters

-ANSI Standard

-Return 1st parameter if it is Not NULL.

Example:

SELECT COALESCE(NULL, 'SQL Server', 'Inside');

Result: SQL Server

SELECT COALESCE(NULL, NULL, 1, 2, 3, NULL, 4);

Result: 1

Difference:

COALESCE and ISNULL function is used to return the first non-null expression.

Here is the few differences:

1. COALESCE is a ANSI standard where ISNULL is SQL Server Specific

2. COALESCE can accept multiple parameters whereas, ISNULL can accept only two parameters

declare @first as int=NULL

declare @sec as int=1234

declare @third as int=12

select COALESCE(@first,@sec,@third) as ColParam, ISNULL(@first,@sec) as IsNUllParam

3. COALESCE will not support two NULL Parameters while ISNULL will support

select COALESCE(NULL,NULL) as ColParam, ISNULL(NULL,NULL) as IsNUllParam

4. COALESCE will not depend on the length of the datatype, it will return the whole string

ISNULL depends on the length of the first datatype.

declare @first as varchar(3)=NULL

declare @sec as varchar(10)='123456'

declare @third as varchar(4)='5689'

select COALESCE(@first,@sec,@third) as ColParam, ISNULL(@first,@sec) as IsNUllParam, ISNULL(@first,@third) as IsNUllParam1

Unable to open Step output file. The step failed

When you try to execute the job, The job will fail with error "Unable to open Step output file. The step failed"

In this case there is no error with the package. So we have to change the path in jobs under sql agent job.

For example if the job name is: Batch_price.

Here is the steps:

1. Double click on Batch_price in sql agent job. [i.e. job properties]

2. Go to steps.

3. Edit steps.

4.Click advanced option.

5.Change the path under output file as in image below or delete the path.

6. ok and run the job again.