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 😀

How to remove alpha numeric characters from a string using function with RegEx

Here is how you can get alpha numeric characters from a string in SQL

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

Hope this helps 😀

How to remove ASCII Characters from a string using function

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

CREATE FUNCTION [dbo].RemoveASCIICharactersInRange(@InputString VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    IF @InputString IS NOT NULL
    BEGIN
      DECLARE @Counter INT, @TestString NVARCHAR(40)

      SET @TestString = '%[' + NCHAR(0) + NCHAR(1) + NCHAR(2) + NCHAR(3) 
      + NCHAR(4) + NCHAR(5) + NCHAR(6) + NCHAR(7) + NCHAR(8) + NCHAR(11) 
      + NCHAR(12) + NCHAR(14) + NCHAR(15) + NCHAR(16) + NCHAR(17) 
      + NCHAR(18) + NCHAR(19) + NCHAR(20) + NCHAR(21) + NCHAR(22) 
      + NCHAR(23) + NCHAR(24) + NCHAR(25) + NCHAR(26) + NCHAR(27) 
      + NCHAR(28) + NCHAR(29) + NCHAR(30) + NCHAR(31) 
      + NCHAR(127)+ NCHAR(160)+ ']%'

      SELECT @Counter = PATINDEX (@TestString, @InputString COLLATE Latin1_General_BIN)

      WHILE @Counter <> 0
      BEGIN
        SELECT @InputString = STUFF(@InputString, @Counter, 1, '')
        SELECT @Counter = PATINDEX (@TestString, @InputString COLLATE Latin1_General_BIN)
      END
    END
    RETURN(@InputString)
END

Hope this helps 😀

How to omit methods from Swagger documentation on using Swashbuckle

You can add the following attribute to Controllers and Actions to exclude them from the generated documentation

[ApiExplorerSettings(IgnoreApi = true)]

Here is how you can add this on to a controller.

[ApiExplorerSettings(IgnoreApi = true)]
[Produces("application/json")]
[Consumes("application/json")]
[NSAuthorize("Permission", "CanReadResource")]
public class CommonController : BaseController

"There is already an open DataReader associated with this Command which must be closed first."

This can happen if you execute a query while iterating over the results from another query. I ran into this issue when I called another async call while I was executing one result.

public async Task<IActionResult> GetTicketsInventory(int customerID, int accountID)
        {
            var service_response = await this.ticketsService.GetTicketsInventory(customerID, accountID);
            List<TicketsInventoryCO> items = service_response.Response.ToConvert();

               // this is my second call where i am trying to get results based on some condition
               // to get customer names
                var customer_response = await this.customerService.GetAllMobilityCustomers();

            return ProcessServiceResponse(
                apiContext,
                service_response,
                items,
                null);

        }

This can be easily solved by allowing MARS in your connection string. Add MultipleActiveResultSets=true to the provider part of your connection string (where Data Source, Initial Catalog, etc. are specified). Sample connection string

 "CoreCS": "Data Source=xxx.xxx.xxx.xxx;Initial Catalog=Mobility;Integrated Security=False;User Id=sa; Password=xxxxxx;Max Pool Size=20; Connection Timeout=10;MultipleActiveResultSets=true;",