How to

Split delimited string using XML. Hello readers.... Below is a simple example to split string by using XML. Create a function that does the task. This function accepts two parameters. First is the string with delimiters e.g.(a,b,c,d – here , are the delimiters)  and second parameter is separator or delimiter (in example , is delimiter). You can use any character for delimiter instead of a comma. I am using coma as a delimiter in the current example. First we convert the string in to XML by using CAST and then by querying over the generated XML we generate the columns that hold the split output of the string. Below is the code. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION fnSplit (       @string VARCHAR ( MAX ),       @seprator VARCHAR ( 1 )     ) RETURNS @chunks TABLE ( Chunks VARCHAR ( 200 )) AS BEGIN       SET @st...

Hello readers..! Some time you need to calculate running total of the item. Many people do extra calculation and long methods to accomplish that. Below is the simple logic to calculate running total.  First create a table that holds item with there price. I m using temporary table.  CREATE TABLE #amount (       RecordId INT IDENTITY ( 1 , 1 ),       ItemName VARCHAR ( 20 ),       Cost INT ) Insert some data into the table.  INSERT INTO #amount VALUES ( 'Pen' , 10 ),( 'Pencil' , 5 ), ( 'Rubber' , 2 ), ( 'Scale' , 2 ), ( 'Paper' , 10 ) Now creating another temporary table (#TotalAmount) from the above table and with extra field named RunningTotal that holds the Running Total of the items. SELECT *, 0 AS RuningTotal INTO #TotalAmount FROM #amount Declaring a variable that calculates the total of item. DECLARE @amountSum INT = 0...

A simple and very short code to replace extra commas from the beginning or ending of a string. No need to explain the code. DECLARE @str VARCHAR ( 50 ) = ',a,n,i,s,h,' SELECT @str SET @str = REPLACE ( @str , ',' , ' ' ) SET @str = LTRIM ( RTRIM ( @str )) SELECT   REPLACE ( @str , ' ' , ',' )