Find nth Occurrence of Character SQL Server

CHARINDEX function provided by Microsoft in SQL Server is used to find the index of a character or substring in the main string, but it only returns the first occurrence.

Oftentimes one needs to find the Nth Occurrence of a character or sub-string. In this post I am sharing a function to achieve the same.

The function accept 3 parameters and returns the position of Nth Occurrence of character or sub-string in the main string.

1. string: input value i.e main string
2. occurrence_val: character or sub-string to be find in the main string
3. occurrence_no: nth occurrence to be find in the string

CREATE FUNCTION dbo.udf_GetNthOccurrence(@string VARCHAR(MAX), @occurrence_val VARCHAR(MAX), @occurrence_no INT)
RETURNS INT AS
BEGIN
    DECLARE @ctr INT, @pos INT, @len INT
    SET @ctr = 0
    SET @pos = 0
    SET @len = DATALENGTH(@occurrence_val)
    WHILE @ctr<@occurrence_no
    BEGIN       
        SET @pos = CHARINDEX(@occurrence_val, @string, @pos) + @len
    IF @pos = @len
        BEGIN
            RETURN -1
        END
        SET @ctr = @ctr+1           
    END
    RETURN @pos - @len
END

Let's use above created function and try to find the nth occurrence in main string

DECLARE @String VARCHAR(MAX), @Occ_No INT, @Occ_String VARCHAR(5)
SET @String = 'Ajay\Vijay\Amit\Sanjay'

SET @Occ_No = 2
SET @Occ_String = '\'
SELECT    dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No) As Position
        , LEFT(@String, dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No)-1) AS Left_Part
        , SUBSTRING(@String, dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No), LEN(@String)) AS Right_Part
SET @String = 'Ajay\\Vijay\\Amit\\Sanjay'

SET @Occ_No = 3
SET @Occ_String = '\\'
SELECT    dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No) As Position
        , LEFT(@String, dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No)-1) AS Left_Part
        , SUBSTRING(@String, dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No), LEN(@String)) AS Right_Part

SET @Occ_No = 4
SELECT    dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No) As Position
        , CASE  WHEN dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No) = -1
                THEN 'Occurent Not found'
                ELSE LEFT(@String, dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No)-1)
          END AS Left_Part
        , CASE  WHEN dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No) = -1
                THEN 'Occurent Not found'
                ELSE SUBSTRING(@String, dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No)+1, LEN(@String))
          END AS Right_Part

OUTPUT

Required Field Validator for Checkboxlist in ASP.Net

RequiredField Validator does not directly work with CheckBoxList Control.
In this post, I am providing an alternate way to achieve the same with CustomValidator and some JQuery code

<script src="Scripts/jquery-1.4.1.min.js" type="text/javascript"></script>
<script language="javascript" type="text/javascript">
        function ValidateCheckBoxList(sender, args) {
            args.IsValid = false;
            jQuery(".CheckBoxList").find(":checkbox").each(function () {
                if (jQuery(this).attr("checked")) {
                    args.IsValid = true;
                    return;
                }
            });
        }
</script>

<asp:CheckBoxList ID="cblItems" runat="server" RepeatDirection="Horizontal" CssClass="CheckBoxList">
    <asp:ListItem Text="Item 1" Value="1"></asp:ListItem>
    <asp:ListItem Text="Item 2" Value="2"></asp:ListItem>
    <asp:ListItem Text="Item 3" Value="3"></asp:ListItem>
</asp:CheckBoxList>
<asp:CustomValidator ID="cvEventsValidator" Display="Dynamic" ValidationGroup="Submit"
    runat="server" ClientValidationFunction="ValidateCheckBoxList">*</asp:CustomValidator>
<br />
<asp:Button ID="btn" Text="Submit" runat="server" ValidationGroup="Submit" />

Delete data from all Tables in SQL Server

Whenever, the data is deleted or truncated from the tables in SQL Server, the only issue encountered is due to relationships between the tables. To overcome this issue, I have used below approach to delete the data from all the tables form a database in one go in SQL Server.

Steps

1. First identify all the tables having primary keys and store these tables name in a temp table.
2. Truncate all the tables excluding the tables name stored in temp table.
3. Now all the data from the referencing table has been deleted, so now we can delete all the data from the tables having Primary Key without any issue. So, delete or truncate all the tables that have Primary Key stored in temp table

DECLARE @QUERY NVARCHAR(MAX)
--===========================================
-- Getting all tables with primary key
--===========================================
SELECT OBJECT_NAME(ic.OBJECT_ID) as TBLNAME INTO #TEMP
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1

--=========================================================
-- Truncate data from all tables without having Primary Key
--=========================================================
SET @QUERY = ''
SELECT    @QUERY = @QUERY + ';TRUNCATE TABLE ' + name
FROM    sysobjects
WHERE    xtype = 'U' AND name not in (SELECT TBLNAME FROM #TEMP)
EXEC sp_executesql @QUERY

--=========================================================
-- Delete data from tables with Primary Keys
--=========================================================
SET @QUERY = ''
SELECT    @QUERY = @QUERY + ';DELETE FROM ' + name
FROM    sysobjects
WHERE xtype = 'U' AND name in (SELECT TBLNAME FROM #TEMP)
EXEC sp_executesql @QUERY
DROP TABLE #TEMP

Check all Checkbox in Gridview

JavaScript function that check or uncheck all the check-boxes of the Grid-view Data-rows depending on the state of check-box of the Grid view Header-row

<script type="text/jscript" language="javascript">
    function SelectAllCheckboxes(chkAll, chkCtrlId) {
        checked = chkAll.checked;
        elm = chkAll.form.elements;
        for (i = 0; i < elm.length; i++)
            if (elm[i].type == "checkbox" && elm[i].id.indexOf(chkCtrlId) != -1) {
                if (elm[i].checked != checked)
                    elm[i].click();
            }
    }
</script>

Add this Template-field to the Grid-view that creates check-boxes in Header-row and Data-rows and also call the above JavaScript function on the click of check-box of the Grid-view Header-row

<asp:TemplateField HeaderText="Select">
    <ItemTemplate>
        <asp:CheckBox ID="chkSelect" runat="server" />
    </ItemTemplate>
    <HeaderTemplate>
        <input id="chkAll" onclick="javascript:SelectAllCheckboxes(this, 'chkSelect');" runat="server"
            type="checkbox" />
    </HeaderTemplate>
</asp:TemplateField>

Calculate Working Hours SQL Server

Calculating work hours is a very frequent need in scheduling, billing, payroll, and time and attendance applications. When the data is fetched from some punching device, you just know the timings of the punches of the employees but don't know whether it is In Time or Out Time.

In this post, I am providing the solution to calculate working hours of the employees assuming that odd entries of an employee on a particular day on Time basis are for In Time and even entries are for Out Time .

In the below example, we have used Row_Number to generate sequential number for each employee on each day to find out odd and even entries for the day.

Refer related post,  Row_Number in SQL to learn about it

DECLARE @TAB TABLE( empid VARCHAR(10), recdate DATE, rectime TIME )

INSERT INTO @TAB
SELECT 'EMP0001', '01/01/2012' ,'9:00 AM'
UNION ALL SELECT 'EMP0001', '01/01/2012' ,'01:05 PM'
UNION ALL SELECT 'EMP0001', '01/01/2012' ,'01:33 PM'
UNION ALL SELECT 'EMP0001', '01/01/2012' ,'06:10 PM'
UNION ALL SELECT 'EMP0002', '01/01/2012' ,'08:55 AM'
UNION ALL SELECT 'EMP0002', '01/01/2012' ,'01:02 PM'
UNION ALL SELECT 'EMP0002', '01/01/2012' ,'01:35 PM'
UNION ALL SELECT 'EMP0002', '01/01/2012' ,'06:05 PM'
UNION ALL SELECT 'EMP0001', '01/02/2012' ,'9:03 AM'
UNION ALL SELECT 'EMP0001', '01/02/2012' ,'01:08 PM'
UNION ALL SELECT 'EMP0001', '01/02/2012' ,'01:42 PM'
UNION ALL SELECT 'EMP0001', '01/02/2012' ,'06:16 PM'

SELECT * FROM @TAB

;WITH CTE AS(
    SELECT    empid, recdate, datepart(hh, rectime) * 60 + datepart(mi, rectime) as mins
            , ROW_NUMBER() OVER ( PARTITION BY empid, recdate ORDER BY rectime) as id
    FROM    @TAB
)

SELECT empid, recdate, mins/60 As Hrs, mins%60 as Mins
FROM (
    SELECT A.empid, A.recdate,  SUM(B.mins - A.mins) as mins
    FROM (
        SELECT * FROM CTE WHERE id%2 = 1
    ) A INNER JOIN (   
        SELECT    * FROM CTE WHERE    id%2 = 0
    ) B ON A.empid = B.empid and A.recdate = B.recdate and A.id + 1 = B.id   
    GROUP BY A.empid, A.recdate
) TAB

RESULT

Validate Alphanumeric Javascript

Validating the input for alphanumeric value only is a very frequent requirement in the applications to ensure special characters are not

In this post, I am sharing a small Javascript Function that ensures that the input can contain alpha or number characters only.

Note: Space or punctuation also are not allowed since those characters are not alpha numeric

On key press the function will validate the character (based on ASCII value) and will not allow you to type if the character is not alphanumeric. If you want to allow any other character then tweak the function accordingly.

<script type="text/javascript">
  function validate(key) {
    var keycode = (key.which) ? key.which : key.keyCode;
    if ((keycode >= 65 && keycode <= 90) || (keycode >= 97 && keycode <= 122) || (keycode >= 48 && keycode <= 57))
      return true;
    else
      return false;
  }
</script>
<asp:TextBox ID="txt1" onkeypress="return validate(event)" runat="server"></asp:TextBox>