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

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

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