如何从SQL Server中的字符串中去除所有非字母字符？
答
试试这个功能：
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')
一旦你理解的代码，你应该看到，这是比较简单的改变它删除其他字符，太。你甚至可以使这种动态足以传递你的搜索模式。
希望它有帮助。
+2
油滑。比我的简单得多。 – 2009-06-17 18:17:02
+0
这是一个很棒的实现 – SQLMenace 2009-06-17 18:39:37
+9
这段代码删除非字母字符（因此数字也被删除）。如果您想要离开数字（删除非字母数字字符），则... 用^ a-z ^代替^ a-z 0-9 该搜索字符串出现在两个不同位置的代码中。一定要替换它们两个。 – 2009-06-17 18:51:53
答
我知道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
我向读者提出的挑战：您能否提高效率？使用递归怎么样？
+0
你可以写一个更好的dbo.StripBlacklistCharacters（）而不用循环使用http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum表中的数字加入你的黑名单表，但我今天懒得自己试试.... – 2009-06-17 19:59:06
答
的G Mastros'awesome answer参数版本：
CREATE FUNCTION [dbo].[fn_StripCharacters]
(
@String NVARCHAR(MAX),
@MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
SET @MatchExpression = '%['[email protected]+']%'
WHILE PatIndex(@MatchExpression, @String) > 0
SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')
RETURN @String
END
字母：只在
SELECT dbo.fn_StripCharacters('[email protected]#f4$', '^a-z')
数字：
SELECT dbo.fn_StripCharacters('[email protected]#f4$', '^0-9')
只有字母数字：
SELECT dbo.fn_StripCharacters('[email protected]#f4$', '^a-z0-9')
非字母数字：
SELECT dbo.fn_StripCharacters('[email protected]#f4$', 'a-z0-9')
答
我把这个两个地方PATINDEX被调用。
PatIndex('%[^A-Za-z0-9]%', @Temp)
以上RemoveNonAlphaCharacters自定义函数，并更名为RemoveNonAlphaNumericCharacters
答
虽然职位是有点老了，我想讲以下几点。 问题我与上述方案是，它不会过滤掉字符，如C，E，I，等我适应一个函数，如下所示（I只用一个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
答
如果您像我一样，无法只为生产数据添加功能，但仍希望执行此类过滤，那么使用PIVOT表格将纯粹的SQL解决方案重新放回到一起。
N.B.我将表格硬编码为最多40个字符，如果您有更长的字符串进行过滤，则必须添加更多字符。
SET CONCAT_NULL_YIELDS_NULL OFF;
with
ToBeScrubbed
as (
select 1 as id, '*SOME [email protected] [email protected]* #* BOGUS [email protected]*&! DATA' as ColumnToScrub
),
Scrubbed as (
select
P.Number as ValueOrder,
isnull (substring (t.ColumnToScrub , number , 1) , '') as ScrubbedValue,
t.id
from
ToBeScrubbed t
left join master..spt_values P
on P.number between 1 and len(t.ColumnToScrub)
and type ='P'
where
PatIndex('%[^a-z]%', substring(t.ColumnToScrub,P.number,1)) = 0
)
SELECT
id,
[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]+ [36]+ [37]+ [38] +[39] +[40] as ScrubbedData
FROM (
select
*
from
Scrubbed
)
src
PIVOT (
MAX(ScrubbedValue) FOR ValueOrder IN (
[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], [36], [37], [38], [39], [40]
)
) pvt
答
这是一种非常笨重的方式，将所有不想出来的角色都带走。问题是你必须指定你不想要的字符。如果一个新角色进入你，它会通过，除非你将它添加到列表中。
好处是您不必创建特殊功能。我没有写权限，所以这使我能够从简单的查询中运行。
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
p.Name
,'®','')
,'©','')
,'ö','o')
,'ë','e')
,'ä','a')
,'ü','u')
,'ú','u')
,'í','i')
,'ï','i')
,'™','')
,'é','e')
,'²','2')
,'è','e')
,'—','-')
,'–','-')
,'ó','o')
,'•',' ')
,'…','.')
,'ô','o')
,'â','a')
,'á','a')
,'ê','e')
,'è','e')
,'’',' ')
,'·',' ')
,'à','a')
,'å','a')
,'ã','a')
,'’',' ')
,'a€s','as')
,'ø','o')
,'ñ','n')
,'î','i')
,'ç','c')
,'Ç','C')
,'Ã','A')
,'”','"')
,'“','"')
,'Á','A')
,'¢','c')
,'Ã','A')
,'Å','A')
,'¶','S')
,'×','x')
,'†','')
,'š','')
,'¤','')
,'µ','')
,'õ','')
,'€','')
,'‘','')
,'Õ','')
,'ð','')
,'Ò','')
,'¨','')
,'º','')
,'°','')
,'ì','')
,'ƒ','')
,'ÿ','')
,'ß','')
,'«','')
,'»','')
,'Æ','')
,'¬','')
,'Ù','')
,'ý','')
,'û','')
,'|','')
as Name
答
我刚刚发现这内置于Oracle 10g，如果这是你正在使用。我不得不将所有特殊字符去掉一个电话号码比较。
regexp_replace(c.phone, '[^0-9]', '')
答
使用CTE生成的数字表，检查每个字符，然后FOR XML到Concat的到，你可以保持的字符串值...
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
答
该解决方案，艾伦先生的解决方案的启发，需要一个Numbers整数表（如果您想要以良好的性能进行严肃的查询操作，您应该手头有这个表）。它不需要CTE。您可以更改NOT IN (...)表达式以排除特定字符，或者将其更改为IN (...)或LIKE表达式以仅保留某些字符。
SELECT (
SELECT SUBSTRING([YourString], N, 1)
FROM dbo.Numbers
WHERE N > 0 AND N <= CONVERT(INT, LEN([YourString]))
AND SUBSTRING([YourString], N, 1) NOT IN ('(',')',',','.')
FOR XML PATH('')
) AS [YourStringTransformed]
FROM ...
答
说完看着所有给定的解决方案，我认为必须有不需要的功能或CTE/XML查询纯SQL方法，并且不涉及难以维持嵌套REPLACE语句。这里是我的解决方案：
SELECT
x
,CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 1, 1) + '%' THEN '' ELSE SUBSTRING(x, 1, 1) END
+ CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 2, 1) + '%' THEN '' ELSE SUBSTRING(x, 2, 1) END
+ CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 3, 1) + '%' THEN '' ELSE SUBSTRING(x, 3, 1) END
+ CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 4, 1) + '%' THEN '' ELSE SUBSTRING(x, 4, 1) END
+ CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 5, 1) + '%' THEN '' ELSE SUBSTRING(x, 5, 1) END
+ CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 6, 1) + '%' THEN '' ELSE SUBSTRING(x, 6, 1) END
-- Keep adding rows until you reach the column size
AS stripped_column
FROM (SELECT
column_to_strip AS x
,'ABCDEFGHIJKLMNOPQRSTUVWXYZ' AS a
FROM my_table) a
做这种方式的优点是有效的字符都包含在子查询中的一个字符串进行简单的重新配置为不同的字符集。
缺点是您必须为每个字符添加一行SQL，直到列的大小。为了使这项工作更容易我只是用下面的PowerShell脚本，这个例子中，如果一个VARCHAR（64）：
1..64 | % {
" + CASE WHEN a NOT LIKE '%' + SUBSTRING(x, {0}, 1) + '%' THEN '' ELSE SUBSTRING(x, {0}, 1) END" -f $_
} | clip.exe
答
下面是不需要创建一个功能或上市字符的所有实例来替代的解决方案。它将递归WITH语句与PATINDEX结合使用来查找不需要的字符。它将替换列中的所有不需要的字符 - 任何给定字符串中包含的最多100个唯一的坏字符。 （EG“ABC123DEF234”将包含4个错误字符1,2,3和4）100限制是WITH语句允许的最大递归次数，但这不会限制要处理的行数，仅受限于可用内存。
如果你不想要DISTINCT结果，你可以从代码中删除这两个选项。
-- 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删除非字母字符的方法。首先，你需要一个基于模式的字符串分割器。下面是德韦恩营的article采取一个：
-- 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)
结果：
答
- 第一个创建一个功能
CREATE FUNCTION [dbo].[GetNumericonly]
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '')
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
现在调用这个函数像
select [dbo].[GetNumericonly]('Abhi12shek23jaiswal')
其结果就像
1223
答
DECLARE @vchVAlue NVARCHAR(255) = 'SWP, Lettering Position 1: 4 Ω, 2: 8 Ω, 3: 16 Ω, 4: , 5: , 6: , Voltage Selector, Solder, 6, Step switch, : w/o fuseholder '
WHILE PATINDEX('%?%' , CAST(@vchVAlue AS VARCHAR(255))) > 0
BEGIN
SELECT @vchVAlue = STUFF(@vchVAlue,PATINDEX('%?%' , CAST(@vchVAlue AS VARCHAR(255))),1,' ')
END
SELECT @vchVAlue
答
，我试图保持阿拉伯字母我试图取代常规的表达，但也并没有以这种方式工作并没有为我工作。我写了另一种方法在ASCII级别上工作，因为这是我唯一的选择，并且工作。
Create function [dbo].[RemoveNonAlphaCharacters] (@s varchar(4000)) returns varchar(4000)
with schemabinding
begin
if @s is null
return null
declare @s2 varchar(4000)
set @s2 = ''
declare @l int
set @l = len(@s)
declare @p int
set @p = 1
while @p <= @l begin
declare @c int
set @c = ascii(substring(@s, @p, 1))
if @c between 48 and 57 or @c between 65 and 90 or @c between 97 and 122 or @c between 165 and 253 or @c between 32 and 33
set @s2 = @s2 + char(@c)
set @p = @p + 1
end
if len(@s2) = 0
return null
return @s2
end
GO
答
信不信由你，在我的系统中，这丑陋的功能执行除G Mastros优雅的更好。
CREATE FUNCTION dbo.RemoveSpecialChar (@s VARCHAR(256))
RETURNS VARCHAR(256)
WITH SCHEMABINDING
BEGIN
IF @s IS NULL
RETURN NULL
DECLARE @s2 VARCHAR(256) = '',
@l INT = LEN(@s),
@p INT = 1
WHILE @p <= @l
BEGIN
DECLARE @c INT
SET @c = ASCII(SUBSTRING(@s, @p, 1))
IF @c BETWEEN 48 AND 57
OR @c BETWEEN 65 AND 90
OR @c BETWEEN 97 AND 122
SET @s2 = @s2 + CHAR(@c)
SET @p = @p + 1
END
IF LEN(@s2) = 0
RETURN NULL
RETURN @s2
答
从性能的角度看我会用内联函数：
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
*/
此链接可以帮助你.... HTTP：//www.codefari.com/2014/10/remove-special- characters-from-string.html – Singh 2016-01-13 05:38:40