반응형
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')
코드를 이해한 후에는 다른 문자를 제거하기 위해 코드를 변경하는 것이 비교적 간단하다는 것을 알아야 합니다.검색 패턴을 전달할 수 있을 정도로 동적으로 만들 수도 있습니다.
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')
믿거나 말거나, 내 시스템에서 이 추악한 기능은 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
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
독자들에 대한 저의 도전: 이것을 좀 더 효율적으로 만들 수 있을까요?재귀를 사용하는 것은 어떻습니까?
다음은 함수를 만들거나 바꿀 모든 문자 인스턴스를 나열할 필요가 없는 솔루션입니다.PATINDEX와 함께 재귀 WITH 문을 사용하여 원하지 않는 문자를 찾습니다.지정된 문자열에 포함된 100개의 고유한 잘못된 문자(예: "ABC123DEF234")를 대체합니다. (예: "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;
주어진 솔루션을 모두 살펴본 결과 함수나 CTE/XML 쿼리가 필요 없고 중첩된 REPLACE 문을 유지하는 데 어려움이 없는 순수한 SQL 방법이 있어야 한다고 생각했습니다.제 솔루션은 다음과 같습니다.
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
저와 마찬가지로 프로덕션 데이터에 기능을 추가할 수 있는 액세스 권한이 없지만 이러한 필터링을 수행하려면 피벗 테이블을 사용하여 필터링된 조각을 다시 결합하는 순수 SQL 솔루션이 있습니다.
N.B. 표를 최대 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
다음은 다음을 사용하여 알파벳이 아닌 문자를 제거하는 다른 방법입니다.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 |
2017+에 및 없이 할 수 또 옵션은 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
알렌 씨의 솔루션에서 영감을 얻은 이 솔루션은 다음과 같은 것을 요구합니다.Numbers정수 테이블(좋은 성능으로 심각한 쿼리 작업을 수행하려면 이 테이블을 준비해야 합니다).CTE가 필요하지 않습니다. 변경할 수 .NOT IN (...), 정특문자제나다음문식변경는하자로거로 변경하는 IN (...)ORLIKE특정 문자만 유지하는 식입니다.
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 ...
성능 측면에서 인라인 기능을 사용합니다.
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
*/
여기 @Gerhard Weiss의 답변을 기반으로 한 또 다른 재귀적 CTE 솔루션이 있습니다.당신은 전체 코드 블록을 복사해서 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
저는 이것을 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
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'
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
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;
포스트가 조금 오래되었지만, 저는 다음과 같이 말하고 싶습니다.위 솔루션의 문제점은 ï, ,, , 등과 같은 문자를 걸러내지 않는다는 것입니다.다음과 같은 기능을 적용했습니다(메모리를 저장하기 위해 80바차 문자열만 사용했습니다).
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]', '')
언급URL : https://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server
반응형
'programing' 카테고리의 다른 글
| $프로젝트가 있는 Mongo $그룹 (0) | 2023.05.02 |
|---|---|
| 게시물 가져오기GIS 버전 (0) | 2023.05.02 |
| 전달된 인수가 Bash의 파일 또는 디렉토리인지 확인합니다. (0) | 2023.05.02 |
| 셸 변수에서 웹 페이지 내용을 가져오려면 어떻게 해야 합니까? (0) | 2023.04.22 |
| PowerShell 2.0에서 디렉토리 전체를 반복적으로 삭제하는 방법 (0) | 2023.04.22 |