SQL 如何从sql中的字符串中修剪某些字符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2871109/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I trim certain characters from a string in sql?
提问by Matt
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.
我想从 SQL 中的字符串中修剪所有特殊字符。我见过很多人使用子字符串方法删除一定数量的字符，但在这种情况下，字符串每一侧的长度是未知的。
Anyone know how to do this?
有人知道怎么做吗？
采纳答案by Pranay Rana
use replacefunction will help you
使用replace功能会帮助你
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
更多关于替换：http: //technet.microsoft.com/en-us/library/ms186862.aspx
回答by egrunin
In MS SQL, this will remove all plus signs:
在 MS SQL 中，这将删除所有加号：
SELECT REPLACE(theField, '+', '') FROM theTable
Is that the sort of thing you need?
那是你需要的东西吗？
回答by Dotnetsqlcoder
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
回答by RameezAli
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')
![SQL ON [PRIMARY] 是什么意思？](/res/img/loading.gif)