Use RAND() in User Defined Function

Here is the issue,  you cannot call a non-deterministic function from inside a user-defined function.

So there is a workaround, By creating a view to return RAND(), call RAND() function inside a view and use that view inside your function, something like below.

CREATE VIEW ReturnRANDValue
AS
SELECT RAND() AS Value
  

Here is the function where we call above view inside this function

CREATE FUNCTION [dbo].[ReturnMobileNotificationCount] (
	@MobilityOrderID INT,
	@MobilityOrderItemID INT,
	@LineStatusMasterID INT,
	@LineSubStatusMasterID INT
	)
RETURNS BIT
AS
BEGIN
	DECLARE @LineSubStatusMatch BIT = 0

	SELECT MobilityOrderID,
		MobilityOrderItemID,
		LineStatusMasterID,
		LineSubStatusMasterID,
		HistoryID,
		ROW_NUMBER() OVER (
			ORDER BY (
					SELECT Value
					FROM ReturnRANDValue
					) DESC
			) SerialNo
	FROM MobileNotificationCriteriaHistory MNC(NOLOCK)
	WHERE MobilityOrderID = @MobilityOrderID
		AND MobilityOrderItemID = @MobilityOrderItemID
	ORDER BY HistoryID DESC

	RETURN @LineSubStatusMatch
END
  

How to remove special characters from a string using a function with RegEx

Here is how you can get remove special characters from a string in SQL

-- SELECT dbo.[RemoveCharSpecialSymbolValue] ('naga3fg@#sai') 
CREATE FUNCTION [dbo].[RemoveCharSpecialSymbolValue](@str VARCHAR(500))  
RETURNS VARCHAR(500)  
BEGIN  
DECLARE @startingIndex int  
SET @startingIndex=0  
WHILE 1=1  
	BEGIN  
	SET @startingIndex= patindex('%[^0-9a-zA-Z]%',@str)  
	IF @startingIndex <> 0  
		BEGIN  
			SET @str = replace(@str,substring(@str,@startingIndex,1),'')  
		END  
	ELSE BREAK;  
END  
RETURN @str  
END   

Hope this helps 😀