I would like to trim all special characters from a string in SQL. I've seen a bunch of people who use substring methods to remove a certain amount of characters, but in this case the length on each side of the string is unknown.
Anyone know how to do this?
use replace function will help you
i mean to say when you want to remove special char replace this space' ' by using replace function
more about replace : http://technet.microsoft.com/en-us/library/ms186862.aspx
In MS SQL, this will remove all plus signs:
SELECT REPLACE(theField, '+', '') FROM theTable
Is that the sort of thing you need?
I wrote this function for LEFT trimming any char
CREATE FUNCTION TRIMCHAR
(
-- Add the parameters for the function here
@str varchar(200) , @chartotrim varchar(1)
)
RETURNS varchar(200)
AS
BEGIN
DECLARE @temp varchar(2000)
SET @temp = @str
WHILE CHARINDEX ( @chartotrim , @temp ) =1
BEGIN
SET @temp = RIGHT( @temp , LEN(@temp)-1)
END
RETURN @temp
END
GO
USE [YourDataBase]
GO
/****** Object: UserDefinedFunction [Accounts].[fn_CurrentFeeorArrears] Script Date: 02/18/2014 12:54:15 ******/
/*****Developed By rameez****/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [FN_REMOVE_SPECIAL_CHARACTER]
(
@INPUT_STRING varchar(300))
RETURNS VARCHAR(300)
AS
BEGIN
--declare @testString varchar(100),
DECLARE @NEWSTRING VARCHAR(100)
-- set @teststring = '@ram?eez(ali)'
SET @NEWSTRING = @INPUT_STRING ;
With SPECIAL_CHARACTER as
(
--SELECT '>' as item
--UNION ALL
--SELECT '<' as item
--UNION ALL
--SELECT '(' as item
--UNION ALL
--SELECT ')' as item
--UNION ALL
--SELECT '!' as item
--UNION ALL
--SELECT '?' as item
--UNION ALL
--SELECT '@' as item
--UNION ALL
--SELECT '*' as item
--UNION ALL
--SELECT '%' as item
--UNION ALL
SELECT '$' as item
)
SELECT @NEWSTRING = Replace(@NEWSTRING, ITEM, '') FROM SPECIAL_CHARACTER
return @NEWSTRING
END
select dbo.[FN_REMOVE_SPECIAL_CHARACTER] ('r$@ameez')
this is ONLY for SQL Server 2014 and before. It performs also space trimming on the right, which you may skip it. The while condition is working because it takes advantage of LEN() "bug".
CREATE FUNCTION [dbo].[RTrimCharacter]
(
@Value NVARCHAR(4000),
@CharacterToTrim NVARCHAR(1)
)
RETURNS NVARCHAR(4000)
AS
BEGIN
-- define a temporary nvarchar
declare @temp nvarchar(4000)
-- copy the input
set @temp = @Value
-- rtrim it
set @temp = RTRIM(@temp)
while (@Value = @temp)
begin
-- set the original to temp
set @Value = @temp
-- trim it the last if the character
if RIGHT(@temp, 1) = @CharacterToTrim
set @temp = LEFT( @temp , LEN(@temp)-1)
-- additionally trim the spaces
set @temp = RTRIM(@temp)
end
RETURN @temp
END
