如何从字符串中删除所有非字母的字符?
非字母数字呢?
这必须是一个自定义函数还是也有更通用的解决方案?
当前回答
下面是使用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 |
其他回答
SQL Server >= 2017…
declare @text varchar(max)
-- create some sample text
select
@text=
'
Lorem @ipsum *&dolor-= sit?! amet, {consectetur } adipiscing\ elit. Vivamus commodo justo metus, sed facilisis ante
congue eget. Proin ac bibendum sem/.
'
-- the characters to be removed
declare @unwanted varchar(max)='''.,!?/<>"[]{}|`~@#$%^&*()-+=/\:;'+char(13)+char(10)
-- interim replaced with
declare @replace_with char(1)=' '
-- call the translate function that will change unwanted characters to spaces
-- in this sample
declare @translated varchar(max)
select @translated=TRANSLATE(@text,@unwanted,REPLICATE(@replace_with,len(@unwanted)))
-- In this case, I want to preserve one space
select string_agg(trim(value),' ')
from STRING_SPLIT(@translated,' ')
where trim(value)<>''
-- Result
'Lorem ipsum dolor sit amet consectetur adipiscing elit Vivamus commodo justo metus sed facilisis ante congue eget Proin ac bibendum sem'
虽然这篇文章有点老了，但我想说以下几点。 我有上述解决方案的问题是，它没有过滤出字符，如ç， ë， ï等。我调整了一个函数如下(我只使用80 varchar字符串来节省内存):
create FUNCTION dbo.udf_Cleanchars (@InputString varchar(80))
RETURNS varchar(80)
AS
BEGIN
declare @return varchar(80) , @length int , @counter int , @cur_char char(1)
SET @return = ''
SET @length = 0
SET @counter = 1
SET @length = LEN(@InputString)
IF @length > 0
BEGIN WHILE @counter <= @length
BEGIN SET @cur_char = SUBSTRING(@InputString, @counter, 1) IF ((ascii(@cur_char) in (32,44,46)) or (ascii(@cur_char) between 48 and 57) or (ascii(@cur_char) between 65 and 90) or (ascii(@cur_char) between 97 and 122))
BEGIN SET @return = @return + @cur_char END
SET @counter = @counter + 1
END END
RETURN @return END
我刚在Oracle 10g中找到了这个，如果你用的就是它的话。为了进行电话号码比较，我必须去掉所有的特殊字符。
regexp_replace(c.phone, '[^0-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')
一旦您理解了代码，您就会发现更改它以删除其他字符也相对简单。您甚至可以使此动态到足以传入您的搜索模式。
下面是使用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 |
