首页 / 知识

关于sql server：T-SQL trim＆nbsp(和其他非字母数字字符)

2023-04-14 17:20:00

T-SQL trim (and other non-alphanumeric characters)

我们有一些输入数据，有时有时以＆nbsp字符结尾。

数据以varchar()的形式从源系统输入，而我们尝试将这些字符强制转换为十进制会失败。

Ltrim和Rtrim不会删除字符，因此我们被迫执行以下操作：

1
2
3

UPDATE myTable
SET myColumn = REPLACE(myColumn,CHAR(160),'')
WHERE charindex(CHAR(160),myColumn) > 0

这适用于＆nbsp，但是对于任何非字母数字(在这种情况下为数字)字符，有没有很好的方法呢？

这将删除所有非字母数字字符

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

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

使用类似的功能：

1
2
3

UPDATE TableToUpdate
SET ColumnToUpdate = dbo.fnRemoveBadCharacter(ColumnToUpdate)
WHERE whatever

此页面提供了有关如何删除非字母数字字符的示例：

1
2
3
4
5
6
7
8
9
10
11
12
13

-- 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

表格如何填充？虽然可以在sql中进行清理，但更好的方法是将列类型更改为int并在将数据加载到数据库(SSIS)中之前对其进行清理。这是一个选择吗？

如果手机可以以Plus(+)开头，我将使用如下功能

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

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

对于大型数据集，我最好使用此功能来检查ASCII值。我添加了一些选项，以根据参数仅保留字母，数字或字母数字。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

--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

数字字符字符输入数据