Get last index position of character

The function is used to find the last index position of the character in the given string

Create FUNCTION [dbo].[CharLastIndex](@SeekChars nvarchar(max),@String nvarchar(max))
RETURNS int
AS 
BEGIN   
    Declare @Index int,@startPosition int
    SET  @startPosition=0
    Select @Index= charindex(@SeekChars,@String,@startPosition)
    SET @startPosition = @Index
    While(@startPosition!=0)
    BEGIN
        SET @Index =@startPosition
        Select @startPosition = charIndex(@SeekChars,@String,@startPosition+1)
    END

RETURN @Index
END

Check given string is integer or not

Create FUNCTION [dbo].[CheckIsInt](@String nvarchar(255)) 
RETURNS bit
AS 
BEGIN   
Declare @Count int,@char nchar(1),@result bit
SET @Count=1
SET @result =0
While (@Count<=len(@String))
BEGIN
    Set @char =substring(@String,@Count,1)
    if(charindex(@char,'0123456789')=0)
        return 0
    else
        SET @result=1   
    SET    @Count =@Count + 1
END
return @result

END

Get Distinct String using SQL Server

Get distinct string between given two string

ALTER function [dbo].[GetDistinctString](@string1 varchar(max),@string2 varchar(max))
returns varchar(max)
as
begin
Set @string2 = ',' + @string2 + ','
Declare @count int,@index int,@s1Len int,@nextIndex int
Set @index=0
Set @nextIndex =1
Set @s1Len=len(@string1)
Declare @part varchar(4)

While(len(@string2)>0 and @index <= @s1Len and @nextIndex <> 0)
begin
    Set @nextIndex = charindex(',',@string1,@index)
    if @nextIndex !=0
    begin
        Set @part = substring(@string1,@index,@nextIndex - @index )
       
    end
    else
    begin
        Set @part = substring(@string1,@index,@s1Len - @index + 1)
       
    end
    --Set @string2 = replace(@string2,@part,'')
    Set @string2 = replace(@string2,','+@part+',',',')
   
    Set @index =@nextIndex+1

end
Set @string1 = @string1 + @string2
Set @s1Len =len(@string1)
return substring(@string1,0,@s1Len)

end

Get Empty Guid using SQL Function

Get Empty Guid using SQL Server

Create FUNCTION [dbo].[GetEmptyGUID]()
RETURNS UNIQUEIDENTIFIER
AS
BEGIN
RETURN SELECT CAST(CAST(0 as BINARY) AS UNIQUEIDENTIFIER)
END

Truncate Time

Function to truncate the time from the given date using SQL Server

ALTER FUNCTION [dbo].[TruncateTime]
(
@dateToConvert datetime
)
RETURNS datetime
AS
BEGIN
RETURN convert(datetime, convert(varchar(10), @dateToConvert, 101))
END

Decode URL using SQL Server

Create FUNCTION [dbo].[UrlDecode](@url varchar(max))
RETURNS varchar(MAX)
AS
BEGIN
    DECLARE @count int, @c char(1), @cenc char(2), @i int, @urlReturn varchar(MAX)
    SET @count = Len(@url)
    SET @i = 1
    SET @urlReturn = ''
    WHILE (@i <= @count)
     BEGIN
        SET @c = substring(@url, @i, 1)
        IF @c LIKE '[!%]' ESCAPE '!'
         BEGIN
            SET @cenc = substring(@url, @i + 1, 2)
            SET @c = CHAR(CASE WHEN SUBSTRING(@cenc, 1, 1) LIKE '[0-9]'
                                THEN CAST(SUBSTRING(@cenc, 1, 1) as int)
                                ELSE CAST(ASCII(UPPER(SUBSTRING(@cenc, 1, 1)))-55 as int)
                            END * 16 +
                            CASE WHEN SUBSTRING(@cenc, 2, 1) LIKE '[0-9]'
                                THEN CAST(SUBSTRING(@cenc, 2, 1) as int)
                                ELSE CAST(ASCII(UPPER(SUBSTRING(@cenc, 2, 1)))-55 as int)
                            END)
            SET @urlReturn = @urlReturn + @c
            SET @i = @i + 2
         END
        ELSE
         BEGIN
            SET @urlReturn = @urlReturn + @c
         END
        SET @i = @i +1
     END
    RETURN @urlReturn
END

Check given string is Guid or not in SQL Server

Below function is used to find whether given string is Valid Guid or not.


Create function [dbo].[IsGuid] ( @testString varchar(38))
returns int
as
begin
    declare @ret int
    select  @ret = 0,
            @testString = replace(replace(@testString, '{', ''), '}', '')
    if len(isnull(@testString, '')) = 36 and
       @testString NOT LIKE '%[^0-9A-Fa-f-]%' and
       -- check for proper positions of hyphens (-) 
       charindex('-', @testString) = 9 and
       charindex('-', @testString, 10) = 14 and
       charindex('-', @testString, 15) = 19 and
       charindex('-', @testString, 20) = 24 and
       charindex('-', @testString, 25) = 0
          set @ret = 1
   
    return @ret
end



Usage

select  dbo.isGuid('some string')

Get Number of Records count based on the search string

The following Store procedure can be used for following purpose. you can modified based on your requirement.

1.Get number of records based on the search string
2.Execute the SQL Query inside the store procedure.
3.Using the view inside the Store procedure.

Create PROCEDURE [dbo].[GetCount]
(
    @Id uniqueidentifier=null,
    @SearchCriteria nvarchar(max) = null,
    @ApplicationId uniqueidentifier
)
as
Begin
DECLARE @nparam NVARCHAR(40)
DECLARE @Query nvarchar(max)
SELECT @Query = '
      SELECT
      Count(Id)
    From [vwGetAllQuestion] WHERE ID = isnull(@Id,Id)' + isnull(@SearchCriteria,'')
    set @nparam = '@Id uniqueidentifier'

--select @Query
EXEC sp_executesql @Query, @nparam, @Id

end

Export Data Table to CSV file with comma values

public void WriteCSVToResponse(DataTable dt, string fileName, string delimiter)
    {
        //prepare the output stream
        Response.Clear();
        Response.ContentType = "text/csv";
        Response.AppendHeader("Content-Disposition",
            string.Format("attachment; filename={0}", fileName));
      
        //write the csv column headers
        if (dt != null && dt.Rows.Count > 0)
        {
           
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (dt.Columns[i].ColumnName != "FormsResponseXML")
                {
                    Response.Write(dt.Columns[i].ColumnName);
                    Response.Write((i < dt.Columns.Count - 1) ? delimiter : Environment.NewLine);
                }
            }

            //write the data
            foreach (DataRow row in dt.Rows)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (dt.Columns[i].ColumnName != "FormsResponseXML")
                    {                       
                        var append = row[i].ToString().Contains(",")? string.Format("\"{0}\"", row[i].ToString()): row[i].ToString();
                        Response.Write(append);
                        Response.Write((i < dt.Columns.Count - 1) ? delimiter : Environment.NewLine);
                    }
                }
            }
        }

        Response.End();
    }

Convert DataTable to CSV file

 public void WriteCSVToResponse(DataTable dt, string fileName, string delimiter)
    {
        //prepare the output stream
        Response.Clear();
        Response.ContentType = "text/csv";
        Response.AppendHeader("Content-Disposition",
            string.Format("attachment; filename={0}", fileName));

        //write the csv column headers
        if (dt != null && dt.Rows.Count > 0)
        {
           
            for (int i = 0; i < dt.Columns.Count; i++)
            {
               
                    Response.Write(dt.Columns[i].ColumnName);
                    Response.Write((i < dt.Columns.Count - 1) ? delimiter : Environment.NewLine);
               
            }

            //write the data
            foreach (DataRow row in dt.Rows)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                   
                        Response.Write(row[i].ToString());
                        Response.Write((i < dt.Columns.Count - 1) ? delimiter : Environment.NewLine);
                   
                }
            }
        }

        Response.End();
    }