如何从字符串中删除所有非字母的字符?
非字母数字呢?
这必须是一个自定义函数还是也有更通用的解决方案?
当前回答
SQL Server 2017+的另一个可能的选项，没有循环和/或递归，是使用TRANSLATE()和REPLACE()的基于字符串的方法。
t - sql声明:
DECLARE @pattern varchar(52) = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
SELECT
v.[Text],
REPLACE(
TRANSLATE(
v.[Text],
REPLACE(TRANSLATE(v.[Text], @pattern, REPLICATE('a', LEN(@pattern))), 'a', ''),
REPLICATE('0', LEN(REPLACE(TRANSLATE(v.[Text], @pattern, REPLICATE('a', LEN(@pattern))), 'a', '')))
),
'0',
''
) AS AlphabeticCharacters
FROM (VALUES
('abc1234def5678ghi90jkl#@$&'),
('1234567890'),
('JAHDBESBN%*#*@*($E*sd55bn')
) v ([Text])
或作为一个函数:
CREATE FUNCTION dbo.RemoveNonAlphabeticCharacters (@Text varchar(1000))
RETURNS varchar(1000)
AS BEGIN
DECLARE @pattern varchar(52) = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
SET @text = REPLACE(
TRANSLATE(
@Text,
REPLACE(TRANSLATE(@Text, @pattern, REPLICATE('a', LEN(@pattern))), 'a', ''),
REPLICATE('0', LEN(REPLACE(TRANSLATE(@Text, @pattern, REPLICATE('a', LEN(@pattern))), 'a', '')))
),
'0',
''
)
RETURN @Text
END
其他回答
Here's a solution that doesn't require creating a function or listing all instances of characters to replace. It uses a recursive WITH statement in combination with a PATINDEX to find unwanted chars. It will replace all unwanted chars in a column - up to 100 unique bad characters contained in any given string. (E.G. "ABC123DEF234" would contain 4 bad characters 1, 2, 3 and 4) The 100 limit is the maximum number of recursions allowed in a WITH statement, but this doesn't impose a limit on the number of rows to process, which is only limited by the memory available. If you don't want DISTINCT results, you can remove the two options from the code.
-- Create some test data:
SELECT * INTO #testData
FROM (VALUES ('ABC DEF,K.l(p)'),('123H,J,234'),('ABCD EFG')) as t(TXT)
-- Actual query:
-- Remove non-alpha chars: '%[^A-Z]%'
-- Remove non-alphanumeric chars: '%[^A-Z0-9]%'
DECLARE @BadCharacterPattern VARCHAR(250) = '%[^A-Z]%';
WITH recurMain as (
SELECT DISTINCT CAST(TXT AS VARCHAR(250)) AS TXT, PATINDEX(@BadCharacterPattern, TXT) AS BadCharIndex
FROM #testData
UNION ALL
SELECT CAST(TXT AS VARCHAR(250)) AS TXT, PATINDEX(@BadCharacterPattern, TXT) AS BadCharIndex
FROM (
SELECT
CASE WHEN BadCharIndex > 0
THEN REPLACE(TXT, SUBSTRING(TXT, BadCharIndex, 1), '')
ELSE TXT
END AS TXT
FROM recurMain
WHERE BadCharIndex > 0
) badCharFinder
)
SELECT DISTINCT TXT
FROM recurMain
WHERE BadCharIndex = 0;
下面是使用iTVF删除非字母字符的另一种方法。首先，需要一个基于模式的字符串分配器。以下是Dwain Camp文章中的一段:
-- PatternSplitCM will split a string based on a pattern of the form
-- supported by LIKE and PATINDEX
--
-- Created by: Chris Morris 12-Oct-2012
CREATE FUNCTION [dbo].[PatternSplitCM]
(
@List VARCHAR(8000) = NULL
,@Pattern VARCHAR(50)
) RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
WITH numbers AS (
SELECT TOP(ISNULL(DATALENGTH(@List), 0))
n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n)
)
SELECT
ItemNumber = ROW_NUMBER() OVER(ORDER BY MIN(n)),
Item = SUBSTRING(@List,MIN(n),1+MAX(n)-MIN(n)),
[Matched]
FROM (
SELECT n, y.[Matched], Grouper = n - ROW_NUMBER() OVER(ORDER BY y.[Matched],n)
FROM numbers
CROSS APPLY (
SELECT [Matched] = CASE WHEN SUBSTRING(@List,n,1) LIKE @Pattern THEN 1 ELSE 0 END
) y
) d
GROUP BY [Matched], Grouper
现在你有了一个基于模式的拆分器，你需要拆分匹配模式的字符串:
[a-z]
然后将它们连接起来以得到想要的结果:
SELECT *
FROM tbl t
CROSS APPLY(
SELECT Item + ''
FROM dbo.PatternSplitCM(t.str, '[a-z]')
WHERE Matched = 1
ORDER BY ItemNumber
FOR XML PATH('')
) x (a)
样本
结果:
| Id | str | a |
|----|------------------|----------------|
| 1 | test“te d'abc | testtedabc |
| 2 | anr¤a | anra |
| 3 | gs-re-C“te d'ab | gsreCtedab |
| 4 | M‚fe, DF | MfeDF |
| 5 | R™temd | Rtemd |
| 6 | ™jad”ji | jadji |
| 7 | Cje y ret¢n | Cjeyretn |
| 8 | J™kl™balu | Jklbalu |
| 9 | le“ne-iokd | leneiokd |
| 10 | liode-Pyr‚n‚ie | liodePyrnie |
| 11 | V„s G”ta | VsGta |
| 12 | Sƒo Paulo | SoPaulo |
| 13 | vAstra gAtaland | vAstragAtaland |
| 14 | ¥uble / Bio-Bio | ubleBioBio |
| 15 | U“pl™n/ds VAsb-y | UplndsVAsby |
使用CTE生成的数字表来检查每个字符，然后FOR XML连接到一个保留值的字符串，您可以…
CREATE FUNCTION [dbo].[PatRemove](
@pattern varchar(50),
@expression varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
WITH
d(d) AS (SELECT d FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) digits(d)),
nums(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM d d1, d d2, d d3, d d4),
chars(c) AS (SELECT SUBSTRING(@expression, n, 1) FROM nums WHERE n <= LEN(@expression))
SELECT
@expression = (SELECT c AS [text()] FROM chars WHERE c NOT LIKE @pattern FOR XML PATH(''));
RETURN @expression;
END
CREATE FUNCTION remove_spc_char(@str VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @resp VARCHAR(MAX) = '';
DECLARE @str_val VARCHAR(MAX) = UPPER(@str);
DECLARE @i INTEGER= 1;
DECLARE @v_asc INTEGER;
WHILE @i <= (LEN(@str_val))
BEGIN
SET @v_asc = (ASCII(SUBSTRING(@str_val, @i, 1)))
BEGIN
IF @v_asc in (192,193,194,195,196,65)
begin
SET @v_asc = 65;
SET @resp = concat(@resp, CHAR(@v_asc));
end;
IF @v_asc in (200,201,202,203,233,69)
begin
SET @v_asc = 69;
SET @resp = concat(@resp, CHAR(@v_asc));
end;
IF @v_asc in (204,205,206,207,296,73)
begin
SET @v_asc = 73;
SET @resp = concat(@resp, CHAR(@v_asc));
end;
IF @v_asc in (210,211,212,213,214,79)
begin
SET @v_asc = 79;
SET @resp = concat(@resp, CHAR(@v_asc));
end;
IF @v_asc in (217,218,219,220,85)
begin
SET @v_asc = 85;
SET @resp = concat(@resp, CHAR(@v_asc));
end;
IF @v_asc in (199,231,67)
begin
SET @v_asc = 67;
SET @resp = concat(@resp, CHAR(@v_asc));
end;
IF @v_asc in (209,78)
begin
SET @v_asc = 78;
SET @resp = concat(@resp, CHAR(@v_asc));
end;
IF @v_asc in (924,181,358,216,222,330,272,208,198,42,37,38,34,36,35,
64,33,39,41,40,43,61,95,45,62,60,63,47,176,183,124,166,174,359,248,254,
180,170,186,126,312,331,273,172,178,179,163,162,123,91,93,125,92,167,240,
223,230,171,187,169,185,168)
begin
SET @resp = concat(@resp, '');
end;
ELSE
begin
if @v_asc not in (65,67,69,73,78,79,85)
begin
SET @resp = concat(@resp, CHAR(@v_asc));
end;
end;
END;
SET @i = @i + 1
END;
RETURN @resp;
END;
我知道SQL不擅长字符串操作，但我没想到它会这么难。下面是一个简单的函数，用于从字符串中剥离所有数字。当然还有更好的办法，但这只是个开始。
CREATE FUNCTION dbo.AlphaOnly (
@String varchar(100)
)
RETURNS varchar(100)
AS BEGIN
RETURN (
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
@String,
'9', ''),
'8', ''),
'7', ''),
'6', ''),
'5', ''),
'4', ''),
'3', ''),
'2', ''),
'1', ''),
'0', '')
)
END
GO
-- ==================
DECLARE @t TABLE (
ColID int,
ColString varchar(50)
)
INSERT INTO @t VALUES (1, 'abc1234567890')
SELECT ColID, ColString, dbo.AlphaOnly(ColString)
FROM @t
输出
ColID ColString
----- ------------- ---
1 abc1234567890 abc
第2轮-数据驱动黑名单
-- ============================================
-- Create a table of blacklist characters
-- ============================================
IF EXISTS (SELECT * FROM sys.tables WHERE [object_id] = OBJECT_ID('dbo.CharacterBlacklist'))
DROP TABLE dbo.CharacterBlacklist
GO
CREATE TABLE dbo.CharacterBlacklist (
CharID int IDENTITY,
DisallowedCharacter nchar(1) NOT NULL
)
GO
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'0')
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'1')
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'2')
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'3')
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'4')
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'5')
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'6')
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'7')
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'8')
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'9')
GO
-- ====================================
IF EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID('dbo.StripBlacklistCharacters'))
DROP FUNCTION dbo.StripBlacklistCharacters
GO
CREATE FUNCTION dbo.StripBlacklistCharacters (
@String nvarchar(100)
)
RETURNS varchar(100)
AS BEGIN
DECLARE @blacklistCt int
DECLARE @ct int
DECLARE @c nchar(1)
SELECT @blacklistCt = COUNT(*) FROM dbo.CharacterBlacklist
SET @ct = 0
WHILE @ct < @blacklistCt BEGIN
SET @ct = @ct + 1
SELECT @String = REPLACE(@String, DisallowedCharacter, N'')
FROM dbo.CharacterBlacklist
WHERE CharID = @ct
END
RETURN (@String)
END
GO
-- ====================================
DECLARE @s nvarchar(24)
SET @s = N'abc1234def5678ghi90jkl'
SELECT
@s AS OriginalString,
dbo.StripBlacklistCharacters(@s) AS ResultString
输出
OriginalString ResultString
------------------------ ------------
abc1234def5678ghi90jkl abcdefghijkl
我对读者的挑战是:你能让这个过程更有效率吗?那么使用递归呢?
