如何从字符串中删除所有非字母的字符?
非字母数字呢?
这必须是一个自定义函数还是也有更通用的解决方案?
当前回答
从性能角度来看，我会使用内联函数:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udf_RemoveNumericCharsFromString]
(
@List NVARCHAR(4000)
)
RETURNS TABLE
AS RETURN
WITH GetNums AS (
SELECT TOP(ISNULL(DATALENGTH(@List), 0))
n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM
(VALUES (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 StrOut = ''+
(SELECT Chr
FROM GetNums
CROSS APPLY (SELECT SUBSTRING(@List , n,1)) X(Chr)
WHERE Chr LIKE '%[^0-9]%'
ORDER BY N
FOR XML PATH (''),TYPE).value('.','NVARCHAR(MAX)')
/*How to Use
SELECT StrOut FROM dbo.udf_RemoveNumericCharsFromString ('vv45--9gut')
Result: vv--gut
*/
其他回答
下面是使用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 |
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;
使用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 [dbo].[fn_StripCharacters]
(
@String NVARCHAR(MAX),
@MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
SET @MatchExpression = '%['+@MatchExpression+']%'
WHILE PatIndex(@MatchExpression, @String) > 0
SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')
RETURN @String
END
字母只有:
SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z')
数字只有:
SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^0-9')
字母数字只有:
SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z0-9')
非字母数字：
SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', 'a-z0-9')
试试这个函数:
Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin
Declare @KeepValues as varchar(50)
Set @KeepValues = '%[^a-z]%'
While PatIndex(@KeepValues, @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')
Return @Temp
End
这样叫它:
Select dbo.RemoveNonAlphaCharacters('abc1234def5678ghi90jkl')
一旦您理解了代码，您就会发现更改它以删除其他字符也相对简单。您甚至可以使此动态到足以传入您的搜索模式。
