T-SQL 修剪 （和其他非字母数字字符）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/52315/
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
T-SQL trim   (and other non-alphanumeric characters)
提问by jwolly2
We have some input data that sometimes appears with   characters on the end.
我们有一些输入数据有时会以 字符结尾。
The data comes in from the source system as varchar() and our attempts to cast as decimal fail b/c of these characters.
数据作为 varchar() 来自源系统，我们尝试将这些字符转换为十进制失败 b/c。
Ltrim and Rtrim don't remove the characters, so we're forced to do something like:
Ltrim 和 Rtrim 不会删除字符，因此我们被迫执行以下操作：
UPDATE myTable
SET myColumn = replace(myColumn,char(160),'')
WHERE charindex(char(160),myColumn) > 0
This works for the  , but is there a good way to do this for any non-alphanumeric (or in this case numeric) characters?
这适用于 ，但是对于任何非字母数字（或在这种情况下是数字）字符，有没有一种好方法可以做到这一点？
回答by
This will remove all non alphanumeric chracters
这将删除所有非字母数字字符
CREATE FUNCTION [dbo].[fnRemoveBadCharacter]
(
@BadString nvarchar(20)
)
RETURNS nvarchar(20)
AS
BEGIN
DECLARE @nPos INTEGER
SELECT @nPos = PATINDEX('%[^a-zA-Z0-9_]%', @BadString)
WHILE @nPos > 0
BEGIN
SELECT @BadString = STUFF(@BadString, @nPos, 1, '')
SELECT @nPos = PATINDEX('%[^a-zA-Z0-9_]%', @BadString)
END
RETURN @BadString
END
Use the function like:
使用如下函数：
UPDATE TableToUpdate
SET ColumnToUpdate = dbo.fnRemoveBadCharacter(ColumnToUpdate)
WHERE whatever
回答by Espo
This pagehas a sample of how you can remove non-alphanumeric chars:
此页面提供了如何删除非字母数字字符的示例：
-- Put something like this into a user function:
DECLARE @cString VARCHAR(32)
DECLARE @nPos INTEGER
SELECT @cString = '90$%45623 *6%}~:@'
SELECT @nPos = PATINDEX('%[^0-9]%', @cString)
WHILE @nPos > 0
BEGIN
SELECT @cString = STUFF(@cString, @nPos, 1, '')
SELECT @nPos = PATINDEX('%[^0-9]%', @cString)
END
SELECT @cString
回答by jason saldo
How is the table being populated? While it is possible to scrub this in sql a better approach would be to change the column type to int and scrub the data before it's loaded into the database (SSIS). Is this an option?
表是如何填充的？虽然可以在 sql 中清理它，但更好的方法是将列类型更改为 int 并在将数据加载到数据库 (SSIS) 之前清理数据。这是一个选项吗？
回答by BrandonNeiger
For large datasets I have had better luck with this function that checks the ASCII value. I have added options to keep only alpha, numeric or alphanumeric based on the parameters.
对于大型数据集，我使用这个检查 ASCII 值的函数运气更好。我添加了基于参数仅保留字母、数字或字母数字的选项。
--CleanType 1 - Remove all non alpanumeric
-- 2 - Remove only alpha
-- 3 - Remove only numeric
CREATE FUNCTION [dbo].[fnCleanString] (
@InputString varchar(8000)
, @CleanType int
, @LeaveSpaces bit
) RETURNS varchar(8000)
AS
BEGIN
-- // Declare variables
-- ===========================================================
DECLARE @Length int
, @CurLength int = 1
, @ReturnString varchar(8000)=''
SELECT @Length = len(@InputString)
-- // Begin looping through each char checking ASCII value
-- ===========================================================
WHILE (@CurLength <= (@Length+1))
BEGIN
IF (ASCII(SUBSTRING(@InputString,@CurLength,1)) between 48 and 57 AND @CleanType in (1,3) )
or (ASCII(SUBSTRING(@InputString,@CurLength,1)) between 65 and 90 AND @CleanType in (1,2) )
or (ASCII(SUBSTRING(@InputString,@CurLength,1)) between 97 and 122 AND @CleanType in (1,2) )
or (ASCII(SUBSTRING(@InputString,@CurLength,1)) = 32 AND @LeaveSpaces = 1 )
BEGIN
SET @ReturnString = @ReturnString + SUBSTRING(@InputString,@CurLength,1)
END
SET @CurLength = @CurLength + 1
END
RETURN @ReturnString
END
回答by Tobi Adeyemi
If the mobile could start with a Plus(+) I will use the function like this
如果手机可以以 Plus(+) 开头，我将使用这样的功能
CREATE FUNCTION [dbo].[Mobile_NoAlpha](@Mobile VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @StartsWithPlus BIT = 0
--check if the mobile starts with a plus(+)
IF LEFT(@Mobile, 1) = '+'
BEGIN
SET @StartsWithPlus = 1
--Take out the plus before using the regex to eliminate invalid characters
SET @Mobile = RIGHT(@Mobile, LEN(@Mobile)-1)
END
WHILE PatIndex('%[^0-9]%', @Mobile) > 0
SET @Mobile = Stuff(@Mobile, PatIndex('%[^0-9]%', @Mobile), 1, '')
IF @StartsWithPlus = 1
SET @Mobile = '+' + @Mobile
RETURN @Mobile
END
