19 ответов:
попробуйте эту функцию:
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')Как только вы поймете код, вы должны увидеть, что это относительно просто изменить его, чтобы удалить другие символы, тоже. Вы даже можете сделать это достаточно динамичным, чтобы передать свой шаблон поиска.
надеюсь, что это помогает.
Параметризованная версия Г Mastros До'удивительный ответ!--7-->:
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')
Я знал, что 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мой вызов читателям: можете ли вы сделать это больше эффективно? А как насчет использования рекурсии?
Если вы похожи на меня и не имеете доступа просто добавлять функции к вашим производственным данным, но все еще хотите выполнять такую фильтрацию, вот чистое решение SQL с использованием сводной таблицы, чтобы снова собрать отфильтрованные части.
Б. Н. Я жестко закодировал таблицу до 40 символов, вам придется добавить больше, если у вас есть более длинные строки для фильтрации.
SET CONCAT_NULL_YIELDS_NULL OFF; with ToBeScrubbed as ( select 1 as id, '*SOME 222@ !@* #* BOGUS !@*&! 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
рассмотрев все приведенные решения, я подумал, что должен быть чистый метод SQL, который не требует функции или запроса CTE / XML и не требует сложного обслуживания вложенных операторов замены. Вот мое решение:
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
Верьте или нет, в моей системе эта уродливая функция работает лучше, чем G Mastros elegant one.
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
Это очень неуклюжий способ, чтобы взять все символы, которые вы не хотите. Проблема в том, что вы должны указать, какие символы вы не хотите. Если новый персонаж входит в вас, он пройдет, если вы не добавите его в список.
плюс в том, что вам не нужно создавать специальную функцию. У меня нет прав на запись, поэтому это позволяет мне запускать из простого запроса.
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
это решение, вдохновленное решением г-на Аллена, требует
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 ...
вот решение, которое не требует создания функции или перечисления всех экземпляров символов для замены. Он использует рекурсивный оператор WITH в сочетании с PATINDEX для поиска нежелательных символов. Он заменит все неугодные символы в столбце - до 100 уникальных плохих символов, содержащихся в любой заданной строке. (Например, "ABC123DEF234" будет содержать 4 плохих символа 1, 2, 3 и 4) предел 100-это максимальное количество рекурсий, разрешенных в операторе WITH, но это не накладывает ограничение на количество обрабатываемых строк, которое ограничивается только доступной памятью.
Если вам не нужны отдельные результаты, вы можете удалить эти два параметра из кода.-- 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. Во-первых, вам нужен разделитель строк на основе шаблонов. Вот один из них, взятый из лагеря Двейна статьи:-- 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 |
Я поставил это в обоих местах, где PatIndex называется.
PatIndex('%[^A-Za-z0-9]%', @Temp)для пользовательской функции выше RemoveNonAlphaCharacters и переименовал его RemoveNonAlphaNumericCharacters
--сначала создайте одну функцию
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
С точки зрения производительности я бы использовал встроенную функцию:
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 */
используя таблицу чисел, сгенерированную CTE, чтобы изучить каждый символ, а затем для 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
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 endGO
вот еще одно рекурсивное решение CTE, основанное на ответе @Gerhard Weiss здесь. Вы должны быть в состоянии скопировать и вставить весь блок кода в SSMS и играть с ним там. Результаты включают в себя несколько дополнительных столбцов, чтобы помочь нам понять, что происходит. Мне потребовалось некоторое время, пока я не понял все, что происходит с PATINDEX (RegEx) и рекурсивным CTE.
DECLARE @DefineBadCharPattern varchar(30) SET @DefineBadCharPattern = '%[^A-z]%' --Means anything NOT between A and z characters (according to ascii char value) is "bad" SET @DefineBadCharPattern = '%[^a-z0-9]%' --Means anything NOT between a and z characters or numbers 0 through 9 (according to ascii char value) are "bad" SET @DefineBadCharPattern = '%[^ -~]%' --Means anything NOT between space and ~ characters (all non-printable characters) is "bad" --Change @ReplaceBadCharWith to '' to strip "bad" characters from string --Change to some character if you want to 'see' what's being replaced. NOTE: It must be allowed accoring to @DefineBadCharPattern above DECLARE @ReplaceBadCharWith varchar(1) = '#' --Change this to whatever you want to replace non-printable chars with IF patindex(@DefineBadCharPattern COLLATE Latin1_General_BIN, @ReplaceBadCharWith) > 0 BEGIN RAISERROR('@ReplaceBadCharWith value (%s) must be a character allowed by PATINDEX pattern of %s',16,1,@ReplaceBadCharWith, @DefineBadCharPattern) RETURN END --A table of values to play with: DECLARE @temp TABLE (OriginalString varchar(100)) INSERT @temp SELECT ' 1hello' + char(13) + char(10) + 'there' + char(30) + char(9) + char(13) + char(10) INSERT @temp SELECT '2hello' + char(30) + 'there' + char(30) INSERT @temp SELECT ' 3hello there' INSERT @temp SELECT ' tab' + char(9) + ' character' INSERT @temp SELECT 'good bye' --Let the magic begin: ;WITH recurse AS ( select OriginalString, OriginalString as CleanString, patindex(@DefineBadCharPattern COLLATE Latin1_General_BIN, OriginalString) as [Position], substring(OriginalString,patindex(@DefineBadCharPattern COLLATE Latin1_General_BIN, OriginalString),1) as [InvalidCharacter], ascii(substring(OriginalString,patindex(@DefineBadCharPattern COLLATE Latin1_General_BIN, OriginalString),1)) as [ASCIICode] from @temp UNION ALL select OriginalString, CONVERT(varchar(100),REPLACE(CleanString,InvalidCharacter,@ReplaceBadCharWith)), patindex(@DefineBadCharPattern COLLATE Latin1_General_BIN,CleanString) as [Position], substring(CleanString,patindex(@DefineBadCharPattern COLLATE Latin1_General_BIN,CleanString),1), ascii(substring(CleanString,patindex(@DefineBadCharPattern COLLATE Latin1_General_BIN,CleanString),1)) from recurse where patindex(@DefineBadCharPattern COLLATE Latin1_General_BIN,CleanString) > 0 ) SELECT * FROM recurse --optionally comment out this last WHERE clause to see more of what the recursion is doing: WHERE patindex(@DefineBadCharPattern COLLATE Latin1_General_BIN,CleanString) = 0
хотя пост немного старый, я хотел бы сказать следующее. Проблема, с которой я столкнулся выше, заключается в том, что он не отфильтровывает символы, такие как ç, ë, ï и т. д. Я адаптировал функцию следующим образом (я использовал только строку 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]', '')
Comments