Shortest way to split strings

CREATE FUNCTION FN_SplitSTRING (@Text nVarchar(max), @Delimeter nVarchar(50))
RETURNS @Table Table (Item Varchar(200))
/*
Written By: Nitish Kumar.
Objective: To split string with delimeter.
Written On: 28-July-2015.
      SELECT * FROM FN_SplitSTRING('1,2,3,5,6,4,8',',')
      SELECT * FROM FN_SplitSTRING('NITISH, KUMAR, JHA, FROM, SAMASTIPUR',',')
      SELECT * FROM FN_SplitSTRING('17*858*858*8569*89*58*nitish','*')
*/
AS
BEGIN
   
      DECLARE @i INT= LEN(@Text)

      WHILE @i <> 0
      BEGIN

            INSERT INTO @Table SELECT LTRIM(RTRIM(SUBSTRING(@Text,0,CHARINDEX(@Delimeter,@Text))))

            SET @Text= RIGHT(@Text,LEN(@Text) - LEN(SUBSTRING(@Text,0,CHARINDEX(@Delimeter,@Text)+1)) )

            IF(LEN(SUBSTRING(@Text,0,CHARINDEX(@Delimeter,@Text)+1)) = 0)
            BEGIN    
                  INSERT INTO @Table SELECT LTRIM(@Text)
                  SET @i= 1;
            END

            SET @i= @i-1
      END
   
      RETURN
END
GO

jQuery click events firing multiple times

Cause: If your table is binding multiple times then obviously page.bootpag will bind that many times.

So, to resolve this must unbind this on each table bind call.
ex:   $("#page").unbind();

Move and remove Listbox item to another listbox using jQuery/ JavaScript

// Option 1: jQuery


<script type="text/javascript" language="javascript">
$(document).keydown(function (e) {
    var element = e.target.nodeName.toLowerCase();
    if (element != 'input' && element != 'textarea') {
        if (e.keyCode === 8) {
            return false;
        }
    }
});

$(document).ready(function () {
                
    $("#<%=hdnAssignedModules.ClientID%>").val('');
    var sAllValue = '';
    $('#<%=lstAssigenedModules.ClientID %> option').each(function () {

    var vImgValue = $(this).val();
    sAllValue = sAllValue + vImgValue + ";";

    });
    $("#<%=hdnAssignedModules.ClientID%>").val(sAllValue);

   
    $('#<%=lstAllModules.ClientID %>').click(function () {
    if ($('#<%=lstAllModules.ClientID %> > option:selected').val() != '')
    $('#btnAddModule').removeAttr('disabled');
    else
    $('#btnAddModule').attr('disabled', 'disabled');
    });
    $('#<%=lstAssigenedModules.ClientID %>').click(function () {
    if ($('#<%=lstAssigenedModules.ClientID %> > option:selected').val() != '')
    $('#btnRemModule').removeAttr('disabled');
    else
    $('#btnRemModule').attr('disabled', 'disabled');
    });
    
    $('#btnAddModule').click(
    function (e) {
    if ($('#<%=lstAllModules.ClientID %> > option:selected').appendTo($('#<%=lstAssigenedModules.ClientID %>')));
    $('#btnAddModule').attr('disabled', 'disabled');

    var sAllValue = '';
    $("#<%=hdnAssignedModules.ClientID%>").val('');
    $('#<%=lstAssigenedModules.ClientID %> > option').each(function () {
    var vImgValue = $(this).val();
    sAllValue = sAllValue + vImgValue + ";";
    });
    $("#<%=hdnAssignedModules.ClientID%>").val(sAllValue);
    });
   
    $('#btnRemModule').click(function (e) {
    if ($('#<%=lstAssigenedModules.ClientID %> > option:selected').appendTo($('#<%=lstAllModules.ClientID %>')));
    $('#btnRemModule').attr('disabled', 'disabled');
        
    $("#<%=hdnAssignedModules.ClientID%>").val('');
    var sAllValue = '';
    $('#<%=lstAssigenedModules.ClientID %> > option').each(function () {
    var vImgValue = $(this).val();
    sAllValue = sAllValue + vImgValue + ";";
    });
    $("#<%=hdnAssignedModules.ClientID%>").val(sAllValue);
    });
 });    



 </script>



 // Option 2: Javascript
  <script type="text/javascript" language="javascript">
        function move(tbFrom, tbTo) {
            var arrFrom = new Array(); var arrTo = new Array();
            var arrLU = new Array();
            var i;
            for (i = 0; i < tbTo.options.length; i++) {
                arrLU[tbTo.options[i].text] = tbTo.options[i].value;
                arrTo[i] = tbTo.options[i].text;
            }
            var fLength = 0;
            var tLength = arrTo.length;
            for (i = 0; i < tbFrom.options.length; i++) {
                arrLU[tbFrom.options[i].text] = tbFrom.options[i].value;
                if (tbFrom.options[i].selected && tbFrom.options[i].value != "") {
                    arrTo[tLength] = tbFrom.options[i].text;
                    tLength++;
                }
                else {
                    arrFrom[fLength] = tbFrom.options[i].text;
                    fLength++;
                }
            }

            tbFrom.length = 0;
            tbTo.length = 0;
            var ii;

            for (ii = 0; ii < arrFrom.length; ii++) {
                var no = new Option();
                no.value = arrLU[arrFrom[ii]];
                no.text = arrFrom[ii];
                tbFrom[ii] = no;
            }

            for (ii = 0; ii < arrTo.length; ii++) {
                var no = new Option();
                no.value = arrLU[arrTo[ii]];
                no.text = arrTo[ii];
                tbTo[ii] = no;
            }

            document.getElementById("SiteBody_hdnAssignedModules").value = "";
            var sAllValue = '';
            var list = document.getElementById('SiteBody_lstAssigenedModules');
            for (i = 0; i < list.options.length; i++) {
                sAllValue = sAllValue + list.options[i].value + ";";
            }
            document.getElementById("SiteBody_hdnAssignedModules").value = sAllValue;

            document.getElementById("btnAddModule").disabled = true;
            document.getElementById("btnRemModule").disabled = true;
        }

        function enableAddButton() {
            var list = document.getElementById('SiteBody_lstAllModules');
            if (list.options.length != 0) {

                var indx = list.selectedIndex;

                if (list[indx].value != '')
                    document.getElementById("btnAddModule").disabled = false;
                else
                    document.getElementById("btnAddModule").disabled = true;
            }
        }

        function enableRemoveButton() {
            var list = document.getElementById('SiteBody_lstAssigenedModules');
            if (list.options.length != 0) {
                var indx = list.selectedIndex;

                if (list[indx].value != '')
                    document.getElementById("btnRemModule").disabled = false;
                else
                    document.getElementById("btnRemModule").disabled = true;
            }
        }

        function SetAssignedModules() {
            document.getElementById("SiteBody_hdnAssignedModules").value = "";
            var sAllValue = '';
            var list = document.getElementById('SiteBody_lstAssigenedModules');
            for (i = 0; i < list.options.length; i++) {
                sAllValue = sAllValue + list.options[i].value + ";";
            }
            document.getElementById("SiteBody_hdnAssignedModules").value = sAllValue;
        }
    </script>
    
    
    
    PAGE:
    
<tr>
<td>
<asp:ListBox ID="lstAllModules" onClick="enableAddButton()" runat="server"></asp:ListBox>
</td>
<td>
<input type="button" id="btnAddModule" value="Add &gt;&gt;" style="width: 90px;"
disabled="disabled" onclick="move(this.form.SiteBody_lstAllModules,this.form.SiteBody_lstAssigenedModules)" />
<input type="button" id="btnRemModule" value="&lt;&lt; Remove" style="width: 90px;"
disabled="disabled" onclick="move(this.form.SiteBody_lstAssigenedModules,this.form.SiteBody_lstAllModules)" />
</td>
<td>
<asp:ListBox ID="lstAssigenedModules" onClick="enableRemoveButton()" runat="server">
</asp:ListBox>
<asp:HiddenField ID="hdnAssignedModules" runat="server" />
</td>
</tr>


NOTE:

* SetAssignedModules()-> Setting dropdown values to hidden feilds.
* Change Controls' Id.

Finding Unmatched Records in DatatTables Using Linq

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;



namespace MyApp
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            DataTable dt1 = new DataTable();
            dt1.Columns.Add("Bay", typeof(string));
            dt1.Rows.Add("S30");
            dt1.Rows.Add("S31");
            //dt1.Rows.Add("S32");

            DataTable dt2 = new DataTable();
            dt2.Columns.Add("Bay", typeof(string));
            dt2.Rows.Add("S31");
            dt2.Rows.Add("S41");
            dt2.Rows.Add("S51");

            var vardt1 = dt1.AsEnumerable().Select(a => a.Field<string>("Bay"));
            var vardt2 = dt2.AsEnumerable().Select(a => a.Field<string>("Bay"));
            var varUnMatched = vardt1.Except(vardt2);
            
            Response.Write(varUnMatched.Count().ToString()+"<br/>");
            foreach (var element in varUnMatched)
            {
                Response.Write(element + "<br/>");
            }
        }
    }
}



Cheers!!!

Is SQL Server Case-Sencitive? If Yes, then How to compare to values.

-- Example of case-sencivity.
Declare @v1 varchar(20), @v2 varchar(20)

SET @v1='nitisH' ; SET @v2='niTisH'

IF(@v1=@v2 COLLATE Latin1_General_CS_AS )
    PRINT 'Matched'
Else
    PRINT 'Unmatched'   
   
   
   

Select Name from MemberMaster where Name = 'niTisH' COLLATE Latin1_General_CS_AS


Keyword ->

COLLATE: Is a clause that can be applied to a database definition or a column definition to define the collation, or to a character string expression
         to apply a collation cast.

        The COLLATE clause can be applied only for the char, varchar, text, nchar, nvarchar, and ntext data types.
       
        You can use the COLLATE clause of the CREATE DATABASE or ALTER DATABASE statement to specify the default collation of the database. You can also
        specify a collation when you create a database using SQL Server Management Studio. If you do not specify a collation, the database is assigned the
        default collation of the instance of SQL Server.   
       
        You can specify collations for each character string column using the COLLATE clause of the CREATE TABLE or ALTER TABLE statement. You can also specify
        a collation when you create a table using SQL Server Management Studio. If you do not specify a collation, the column is assigned the default collation
        of the database. You can also use the database_default option in the COLLATE clause to specify that a column in a temporary table use the collation
        default of the current user database for the connection instead of tempdb.   
       
        You can use the COLLATE clause to apply a character expression to a certain collation. Character literals and variables are assigned the default collation
        of the current database. Column references are assigned the definition collation of the column.
       
       
database_default: Causes the COLLATE clause to inherit the collation of the current database.   

collation_name: Is the name of the collation to be applied to the expression, column definition, or database definition. collation_name can be only a specified
        Windows_collation_name or a SQL_collation_name. collation_name must be a literal value. collation_name cannot be represented by a variable or expression.

CaseSensitivity: CI specifies case-insensitive, CS specifies case-sensitive.
AccentSensitivity: AI specifies accent-insensitive, AS specifies accent-sensitive.



/* Find Collation of SQL Server Database */
SELECT DATABASEPROPERTYEX('test_base', 'Collation')

/* Find Collation of SQL Server Database Table Column */
USE test_base

SELECT name, collation_name FROM sys.columns WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM sys.objects WHERE type = 'U' AND name = 'EmployeeDetails')
--AND name = 'City' -- > can apply over particular column too.


Cheers!!!


Ref:

http://msdn.microsoft.com/en-us/library/ms184391.aspx
http://sqlservernet.blogspot.in/2012/02/case-sensitive-sql-query-search-and.html
http://msdn.microsoft.com/en-us/library/ms180175(v=sql.105).aspx

SELECT * FROM sys.fn_helpcollations() WHERE name LIKE 'SQL%';

How to get QueryStirng on client side like ASPX or html etc

 function getQueryString(name) {
        name = name.replace(/[\[]/, "\\[").replace(/[\]]/, "\\]");
        var regex = new RegExp("[\\?&]" + name + "=([^&#]*)"),
        results = regex.exec(location.search);
        return results === null ? "" : decodeURIComponent(results[1].replace(/\+/g, " "));
    }


Call this method as :
    var ClassSectionID = getQueryString('Classsectionid');



Cheers!!!

Get total working days excluding sunday and 5th Saturday of any monthhs in SQL SERVER

/*
DESCRIPTION:: Used to get total working days excluding sunday and 5th Saturday of any monthhs.
Created By : NITISH KUMAR
DATE:: 21 Aug, 14
SELECT dbo.fn_getWorkingDays('1 Mar 2014','6 May 2014') as WD
*/
Create function fn_getWorkingDays (@DateFrom smalldatetime, @DateTo smalldatetime)
Returns varchar(30)
AS
BEGIN
DECLARE @FMonth varchar(20), @TMonth varchar(20), @FYear varchar(10), @TYear varchar(10), @WD bigint, @Count varchar(10), @tot varchar(10)
--SET @DateFrom='1 Mar 2014';
--SET @DateTo='31 May 2014';
--SET NOCOUNT ON
Declare  @dates Table(totdates datetime)

WITH T(date)
AS
(
SELECT @DateFrom
UNION ALL
SELECT DateAdd(day,1,T.date) date FROM T WHERE T.date < @DateTo
) Insert INTO @dates Select * FROM T OPTION (MAXRECURSION 32767);

--SELECT * FROM @dates OPTION (MAXRECURSION 32767);
SET @Count='0';SET @tot='0';
DECLARE CUR CURSOR FOR SELECT distinct DATEPART(YYYY,totdates) M,DATEPART(MM,totdates) Y FROM @dates  OPTION (MAXRECURSION 32767);
OPEN CUR
Fetch NEXT FROM CUR INTO @FYear, @FMonth
While @@FETCH_STATUS=0
BEGIN
    SELECT @Count=Count(*) FROM @dates Where DATENAME(DW,totdates)='Saturday' and DatePart(MM,totdates)=@FMonth and DATEPART(YYYY,totdates) =@FYear OPTION (MAXRECURSION 32767);
   
    IF(CAST(@Count as bigint) =5)
    BEGIN
        IF(ISNULL(@tot,0) ='0')
            SET @tot = 1
        ELSE
            SET @tot = Cast(@tot as bigint) + 1;
    END           
Fetch NEXT FROM CUR INTO @FYear, @FMonth
END
CLOSE CUR
DEALLOCATE CUR

SELECT @WD=Count(*) FROM @dates OPTION (MAXRECURSION 32767);
SET @Count='';
SELECT @Count=Count(*) FROM @dates Where DATENAME(DW,totdates)='Sunday' OPTION (MAXRECURSION 32767);
SET @tot = Cast(@tot as bigint) + Cast(@Count as bigint)
SET @tot= @WD- Cast(@tot as bigint)
--SET NOCOUNT OFF
--PRINT @tot
return @tot
END


NOTE::

The MAXRECURSION query hint specifies the maximum number of recursions allowed for a query. The number of recursions is a non-negative integer between 0 and 32,767. When 0 is specified, no limit is applied.

If the MAXRECURSION query hint is not specified, the default limit is 100. When the specified or default number for MAXRECURSION limit is reached during query execution, the query is ended and an error is returned.

CHEERS!!!