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