How to Remove Special Characters Using SQL

Remove Special Characters Using TSQL


CREATE FUNCTION [dbo].[RemoveSpecialCharacters](@StrValue NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN

    Declare @KeepValues as varchar(50)

SET @StrValue = LTRIM(RTRIM(@StrValue))

    SET @KeepValues = '%[^a-z0-9]%'

    WHILE PATINDEX(@KeepValues, @StrValue) > 0
        SET @StrValue = STUFF(@StrValue, PATINDEX(@KeepValues, @StrValue), 1, '')

    Return @StrValue
END

Result:

SELECT DBO.RemoveSpecialCharacters('Welcome %To $Axil - 007')

WelcomeToAxil007

Remove / Trim Leading Zeros - SQL Server

TSQL - Removing leading zeros from varchar value:

How to remove leading zeros from a number using TSQL

ALTER FUNCTION [dbo].[RemoveLeadingZero] (@StrValue NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN
SET @StrValue = ISNULL(@StrValue,'')

SET @StrValue =  SUBSTRING(@StrValue, PATINDEX('%[^0]%', @StrValue+'.'), LEN(@StrValue))
   
Return @StrValue
END

Result:

SELECT DBO.RemoveLeadingZero('00050407')

50407

SELECT DBO.RemoveLeadingZero('00050470')

50470

SELECT DBO.RemoveLeadingZero('000.5430')

.5430