복붙노트

[SQL] 어떻게 SQL에서 여러 문자를 교체하는 방법?

SQL

어떻게 SQL에서 여러 문자를 교체하는 방법?

이것은 액세스 SQL에서 여러 문자를 대체하는 방법 비슷한 질문을 기반으로?

SQL 서버 2005 where 절 안에 19 개 교체에 대한 교체에 제한 () 함수를 갖고있는 것 같아요 때문에 나는 이것을 썼다.

필요 (열 된 경기를 수행 할 수 있으며 장착하여 여러 취소 필요한 문자를 제거 일치의 가능성을 개선하기 위해) 기능 : 나는 다음과 같은 작업을

DECLARE @es NVarChar(1) SET @es = ''
DECLARE @p0 NVarChar(1) SET @p0 = '!'
DECLARE @p1 NVarChar(1) SET @p1 = '@'
---etc...

SELECT *
FROM t1,t2 
WHERE  REPLACE(REPLACE(t1.stringkey,@p0, @es), @p1, @es) 
     = REPLACE(REPLACE(t2.stringkey,@p0, @es), @p1, @es)    
---etc

이있는 경우> (19)는 절, 그렇지 않은 일에 () 교체. 솔루션 그래서 나는 (이 예에서는 trimChars라는 SQL 함수를 작성에서 시작을 용서하는 것입니다 내놓았다 @ 22

CREATE FUNCTION [trimChars] (
   @string varchar(max)
) 

RETURNS varchar(max) 
AS
BEGIN

DECLARE @es NVarChar(1) SET @es = ''
DECLARE @p22 NVarChar(1) SET @p22 = '^'
DECLARE @p23 NVarChar(1) SET @p23 = '&'
DECLARE @p24 NVarChar(1) SET @p24 = '*'
DECLARE @p25 NVarChar(1) SET @p25 = '('
DECLARE @p26 NVarChar(1) SET @p26 = '_'
DECLARE @p27 NVarChar(1) SET @p27 = ')'
DECLARE @p28 NVarChar(1) SET @p28 = '`'
DECLARE @p29 NVarChar(1) SET @p29 = '~'
DECLARE @p30 NVarChar(1) SET @p30 = '{'

DECLARE @p31 NVarChar(1) SET @p31 = '}'
DECLARE @p32 NVarChar(1) SET @p32 = ' '
DECLARE @p33 NVarChar(1) SET @p33 = '['
DECLARE @p34 NVarChar(1) SET @p34 = '?'
DECLARE @p35 NVarChar(1) SET @p35 = ']'
DECLARE @p36 NVarChar(1) SET @p36 = '\'
DECLARE @p37 NVarChar(1) SET @p37 = '|'
DECLARE @p38 NVarChar(1) SET @p38 = '<'
DECLARE @p39 NVarChar(1) SET @p39 = '>'
DECLARE @p40 NVarChar(1) SET @p40 = '@'
DECLARE @p41 NVarChar(1) SET @p41 = '-'

return   REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
       @string, @p22, @es), @p23, @es), @p24, @es), @p25, @es), @p26, @es), @p27, @es), @p28, @es), @p29, @es), @p30, @es), @p31, @es), @p32, @es), @p33, @es), @p34, @es), @p35, @es), @p36, @es), @p37, @es), @p38, @es), @p39, @es), @p40, @es), @p41, @es)
END

이것은 다음 문자열을 대체 할 다른 추가로 사용할 수 있습니다

SELECT *
FROM t1,t2 
WHERE  trimChars(REPLACE(REPLACE(t1.stringkey,@p0, @es), @p1, @es) 
         = REPLACE(REPLACE(t2.stringkey,@p0, @es), @p1, @es))

내가 trimChars 그래서 (trimMoreChars 같은 대체 유사 할 몇 가지 더 많은 기능을 생성 (

SELECT *
FROM t1,t2 
WHERE  trimChars(trimMoreChars(REPLACE(REPLACE(t1.stringkey,@p0, @es), @p1, @es) 
         = REPLACE(REPLACE(t2.stringkey,@p0, @es), @p1, @es)))

누군가가 나에게 성능면에서이 문제에 더 나은 솔루션과 어쩌면 청소기 구현을 줄 수 있습니까?

해결법

  1. ==============================

    1.난 심각 대신 CLR UDF을하고 완전한 검색을 수행하고 문자의 범위를 대체하는 정규 표현식 (문자열과 패턴이 매개 변수로 전달 될 수 모두) 사용을 고려한다. 그것은 쉽게 SQL UDF를 능가한다.

    난 심각 대신 CLR UDF을하고 완전한 검색을 수행하고 문자의 범위를 대체하는 정규 표현식 (문자열과 패턴이 매개 변수로 전달 될 수 모두) 사용을 고려한다. 그것은 쉽게 SQL UDF를 능가한다.

  2. ==============================

    2.SQL에서 하나의 유용한 트릭은 값을 할당 할 수있는 기능 사용 @var = 기능 (...)입니다. 당신이 당신의 기록 세트의 여러 레코드가있는 경우, 리셀러 (VAR)는 부작용으로 여러 번 할당됩니다

    SQL에서 하나의 유용한 트릭은 값을 할당 할 수있는 기능 사용 @var = 기능 (...)입니다. 당신이 당신의 기록 세트의 여러 레코드가있는 경우, 리셀러 (VAR)는 부작용으로 여러 번 할당됩니다

    declare @badStrings table (item varchar(50))

INSERT INTO @badStrings(item)
SELECT '>' UNION ALL
SELECT '<' UNION ALL
SELECT '(' UNION ALL
SELECT ')' UNION ALL
SELECT '!' UNION ALL
SELECT '?' UNION ALL
SELECT '@'

declare @testString varchar(100), @newString varchar(100)

set @teststring = 'Juliet ro><0zs my s0x()rz!!?!one!@!@!@!'
set @newString = @testString

SELECT @newString = Replace(@newString, item, '') FROM @badStrings

select @newString -- returns 'Juliet ro0zs my s0xrzone'
  3. ==============================

    3.정말 Juliett의 솔루션 @ 좋아! 난 그냥 모든 잘못된 문자를 얻기 위해 CTE를 사용합니다 :

    정말 Juliett의 솔루션 @ 좋아! 난 그냥 모든 잘못된 문자를 얻기 위해 CTE를 사용합니다 :

    DECLARE @badStrings VARCHAR(100)
DECLARE @teststring VARCHAR(100)

SET @badStrings = '><()!?@'
SET @teststring = 'Juliet ro><0zs my s0x()rz!!?!one!@!@!@!'

;WITH CTE AS
(
  SELECT SUBSTRING(@badStrings, 1, 1) AS [String], 1 AS [Start], 1 AS [Counter]
  UNION ALL
  SELECT SUBSTRING(@badStrings, [Start] + 1, 1) AS [String], [Start] + 1, [Counter] + 1 
  FROM CTE 
  WHERE [Counter] < LEN(@badStrings)
)

SELECT @teststring = REPLACE(@teststring, CTE.[String], '') FROM CTE

SELECT @teststring
  4. ==============================

    4.난 당신이 스칼라 사용자 정의 함수를 작성하는 것이 좋습니다. 이 예이다 (미안 미리 변수 이름은 스페인어에 있기 때문에)

    난 당신이 스칼라 사용자 정의 함수를 작성하는 것이 좋습니다. 이 예이다 (미안 미리 변수 이름은 스페인어에 있기 때문에)

    CREATE FUNCTION [dbo].[Udf_ReplaceChars] (
  @cadena VARCHAR(500),  -- String to manipulate
  @caracteresElim VARCHAR(100),  -- String of characters to be replaced
  @caracteresReem VARCHAR(100)   -- String of characters for replacement
) 
RETURNS VARCHAR(500)
AS
BEGIN
  DECLARE @cadenaFinal VARCHAR(500), @longCad INT, @pos INT, @caracter CHAR(1), @posCarER INT;
  SELECT
    @cadenaFinal = '',
    @longCad = LEN(@cadena),
    @pos = 1;

  IF LEN(@caracteresElim)<>LEN(@caracteresReem)
    BEGIN
      RETURN NULL;
    END

  WHILE @pos <= @longCad
    BEGIN
      SELECT
        @caracter = SUBSTRING(@cadena,@pos,1),
        @pos = @pos + 1,
        @posCarER = CHARINDEX(@caracter,@caracteresElim);

      IF @posCarER <= 0
        BEGIN
          SET @cadenaFinal = @cadenaFinal + @caracter;
        END
      ELSE
        BEGIN
          SET @cadenaFinal = @cadenaFinal + SUBSTRING(@caracteresReem,@posCarER,1)
        END
    END

  RETURN @cadenaFinal;
END

    다음은이 기능을 사용하는 예이다 :

    SELECT dbo.Udf_ReplaceChars('This is a test.','sat','Z47');

    그리고 그 결과는 다음과 같습니다 7hiZ IZ 4 7eZ7.

    당신이 볼 수 있듯이, @caracteresElim 매개 변수의 각 문자는 @caracteresReem 매개 변수에서 같은 위치의 문자로 대체됩니다.

  5. ==============================

    5.이 질문은 SQL 서버 2005에 대한 질문 동안, 그것의 가치의 SQL 서버 2017의 같은 것을 지적, 요청은 새로운 TRANSLATE 기능을 수행 할 수 있습니다.

    이 질문은 SQL 서버 2005에 대한 질문 동안, 그것의 가치의 SQL 서버 2017의 같은 것을 지적, 요청은 새로운 TRANSLATE 기능을 수행 할 수 있습니다.

    https://docs.microsoft.com/en-us/sql/t-sql/functions/translate-transact-sql

    이 정보는 나중에이 페이지로 사람을 도움이되기를 바랍니다.

  6. ==============================

    6.나는 데이터 일회성 마이그레이션 문제를 한 곳 소스 데이터 수를 출력하지 정확하게 어떤 특별한 기술적 / 문자와 CSV를의 유비쿼터스 여분의 쉼표.

    나는 데이터 일회성 마이그레이션 문제를 한 곳 소스 데이터 수를 출력하지 정확하게 어떤 특별한 기술적 / 문자와 CSV를의 유비쿼터스 여분의 쉼표.

    우리는 이러한 각 문자에 대한 소스 추출물이 소스 시스템을로드했지만 이는 다른 데이터에 없을 것이다 SQL 서버 모두에 인식 할 무언가로 대체해야한다고 결정했다.

    그것은 다양한 테이블에서 여러 열이 대체 문자가 나타나는 것을 그러나 의미 않았고 내가 그들을 대체 할 것입니다. 중첩 여러 REPLACE 함수는 나는 다음과 같은 기능을 쓴 있도록 브래킷의 위치와 수를 잘못 판단의 오류로 가져 오기 코드 모습 무서운 및 경향했다. 나는 확실히 그것을 수백만 행 표를 확장하는 방법을 빨리 모르겠지만 덜 초보다 3,000 행의 테이블의 열을 처리 할 수 ​​있습니다 알고 있습니다.

    create function [dbo].[udf_ReplaceMultipleChars]
(
    @OriginalString nvarchar(4000)
  , @ReplaceTheseChars nvarchar(100)
  , @LengthOfReplacement int = 1
)
returns nvarchar(4000)
begin

    declare @RevisedString nvarchar(4000) = N'';
    declare @lengthofinput int =
            (
            select len(@OriginalString)
            );

with AllNumbers
as (select 1 as  Number
    union all
    select Number + 1
    from AllNumbers
    where Number < @lengthofinput)
select @RevisedString += case
                             when (charindex(substring(@OriginalString, Number, 1), @ReplaceTheseChars, 1) - 1) % 2
    = 0 then
                                 substring(
                                              @ReplaceTheseChars
                                            , charindex(
                                                           substring(@OriginalString, Number, 1)
                                                         , @ReplaceTheseChars
                                                         , 1
                                                       ) + 1
                                            , @LengthOfReplacement
                                          )
                             else
                                 substring(@OriginalString, Number, 1)
                         end
    from AllNumbers
    option (maxrecursion 4000);
    return (@RevisedString);
end;

    그것은 제 여섯째 등으로 평가되는 문자열을 모두 작성하여 작동 첫 문자가 두번째로 대체 될 쌍이 문자열 네 번째로 제와 함께 (@OriginalString) 문자를 교체해야 할 (@ReplaceTheseChars)에.

    여기에 내가 대체 할 필요가 있다고 문자의 문자열입니다 그들의 대체 ... [ '] "~ {Ø} ° $ ± | ¼|¼ª½¬½ ^ ¾ # ✓

    즉 개구 대괄호 아포스트로피, 폐쇄 한 따옴표를 나타낸다. 당신은 거기에 저속한 분수뿐만 아니라도, 직경 기호가 있다고 볼 수 있습니다.

    사람이 긴 문자열을 대체하기 위해 필요한 경우 시작 지점으로 포함 된 기본 @LengthOfReplacement이있다. 나는 내 프로젝트에서 그와 함께 주위를 연주하지만, 하나의 문자 교환이 주요 기능이다.

    case 문의 조건은 중요하다. 그것은이 당신의 @ReplaceTheseChars 변수와 문자가 홀수 위치 (아무것도 반환에게 부정적인 모듈 값을 찾을 수없는 것을 charIndex의 결과 보장하지만에서 마이너스 1)에서 찾을 수있다 발견 된 경우에만 문자를 대체하는 것을 보장한다. 즉 당신이 위치 5 물결표 (~)를 찾을 경우 쉼표로 교체하지만 후속 실행에 위치 (6)에서 쉼표를 발견하는 경우 그것은 중괄호로 교체하지 않을 ({).

    이것은, 예를 들어으로 입증 할 수 ...

    declare @ProductDescription nvarchar(20) = N'abc~def[¦][123';
select @ProductDescription
= dbo.udf_ReplaceMultipleChars(
                                  @ProductDescription
/* NB the doubling up of the apostrophe is necessary in the string but resolves to a single apostrophe when passed to the function */
                                ,'['']"~,{Ø}°$±|¼¦¼ª½¬½^¾#✓' 
                                , default
                              );
select @ProductDescription
 , dbo.udf_ReplaceMultipleChars(
                                   @ProductDescription
                                 ,'['']"~,{Ø}°$±|¼¦¼ª½¬½^¾#✓'
/* if you didn't know how to type those peculiar chars in then you can build a string like  this... '[' + nchar(0x0027) + ']"~,{' + nchar(0x00D8) + '}' + nchar(0x00B0) etc */
                                ,
                                 default
                               );

    이 기능을 통해 전달 제 등은 다음 두 번째 이후의 값을 모두 리턴 ...      abc 방송, def'¼ " '123 abc 방송, def'¼"'123

    테이블 업데이트는 것

    update a
set a.Col1 = udf.ReplaceMultipleChars(a.Col1,'~,]"',1)
from TestTable a

    마지막으로, 나는이 기능을 아주 쉽게 문서의 예를 처리 할 수 ​​있다고 생각 번역 기능에 액세스 할 수 없었습니다 있지만 (나는 당신이 말하는! 소리). 번역사 기능 데모입니다

    SELECT TRANSLATE('2*[3+4]/{7-2}', '[]{}', '()()');

    이는 내가 2 * [+ 4 3]에서 작동하지 않을 수 있습니다 이해하지만 2 * (3 + 4) / (7-2)를 반환 / [7-2]!

    > ({- -> (등의 여분 [뒤에 교체 각 문자 목록은 다음과 같이 내 기능이 접근 할

    select dbo.udf_ReplaceMultipleChars('2*[3+4]/{7-2}', '[({(])})', 1);

    이는 또한 작동합니다

    select dbo.udf_ReplaceMultipleChars('2*[3+4]/[7-2]', '[({(])})', 1);

    나는 누군가가이 유용한 발견 희망 당신은 할 수 않는 큰 테이블에 대한 성능을 테스트 할 수 있다면 우리는 어떤 식 으로든 알아!

  7. ==============================

    7.

    declare @testVal varchar(20)

set @testVal = '?t/es?ti/n*g 1*2?3*'

select @testVal = REPLACE(@testVal, item, '') from (select '?' item union select '*' union select '/') list

select @testVal;
  8. ==============================

    8.하나의 옵션은 의사 설정 기반 쿼리를 통해 반복적 인 프로세스를 구동하기 위해 숫자 / 집계 테이블을 사용하는 것입니다.

    하나의 옵션은 의사 설정 기반 쿼리를 통해 반복적 인 프로세스를 구동하기 위해 숫자 / 집계 테이블을 사용하는 것입니다.

    문자 교체의 일반적인 생각은 간단한 문자 맵 테이블 접근 방식으로 설명 될 수 있습니다 :

    create table charMap (srcChar char(1), replaceChar char(1))
insert charMap values ('a', 'z')
insert charMap values ('b', 'y')


create table testChar(srcChar char(1))
insert testChar values ('1')
insert testChar values ('a')
insert testChar values ('2')
insert testChar values ('b')

select 
coalesce(charMap.replaceChar, testChar.srcChar) as charData
from testChar left join charMap on testChar.srcChar = charMap.srcChar

    그럼 당신은 문자열의 각 문자 위치에서 검색을 수행 할 집계 테이블 방식으로 가져올 수 있습니다.

    create table tally (i int)
declare @i int
set @i = 1
while @i <= 256 begin
    insert tally values (@i)
    set @i = @i + 1
end

create table testData (testString char(10))
insert testData values ('123a456')
insert testData values ('123ab456')
insert testData values ('123b456')

select
    i,
    SUBSTRING(testString, i, 1) as srcChar,
    coalesce(charMap.replaceChar, SUBSTRING(testString, i, 1)) as charData
from testData cross join tally
    left join charMap on SUBSTRING(testString, i, 1) = charMap.srcChar
where i <= LEN(testString)
  9. ==============================

    9.찰스 Bretana는 그의 대답을 삭제 한 이유는 CW 응답으로 다시 그것을 추가 해요, 그래서 나는 모른다, 그러나 계산 열은 시간 거의 모든 데이터를 정화하거나 변화 필요 이러한 경우를 처리 할 수있는 정말 좋은 방법입니다 지속 하지만, 필요 원래 쓰레기를 유지합니다. 그의 제안은 관련과 관계없이 데이터를 정화하기로 결정 방법에 적합합니다.

    찰스 Bretana는 그의 대답을 삭제 한 이유는 CW 응답으로 다시 그것을 추가 해요, 그래서 나는 모른다, 그러나 계산 열은 시간 거의 모든 데이터를 정화하거나 변화 필요 이러한 경우를 처리 할 수있는 정말 좋은 방법입니다 지속 하지만, 필요 원래 쓰레기를 유지합니다. 그의 제안은 관련과 관계없이 데이터를 정화하기로 결정 방법에 적합합니다.

    특히, 내 현재 프로젝트에서, 나는이 선두에 제로 일관성 저장된 일부 특정 숫자 식별자에서 (운이 좋게이 realtively 쉽게 바로 T-SQL에서 처리됩니다) 모든 앞에 0을 트림 계산 열을 지속해야합니다. 이 그것을 필요로하는 테이블에 지속 계산 된 열 저장하고 그 식별자가 자주 조인에 사용되는 일치 된 때문에 인덱싱됩니다.

  10. ==============================

    10.다음 단계는

    다음 단계는

    다음 코드를 참조하십시오 :

    public partial class UserDefinedFunctions 
{

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString Replace2(SqlString inputtext, SqlString filter,SqlString      replacewith)
{

    string str = inputtext.ToString();
    try
    {
        string pattern = (string)filter;
        string replacement = (string)replacewith;
        Regex rgx = new Regex(pattern);
        string result = rgx.Replace(str, replacement);
        return (SqlString)result;

    }
    catch (Exception s)
    {
        return (SqlString)s.Message;
    }
}
}

    다음 코드를 참조하십시오 :

    create table dbo.test(dummydata varchar(255))
Go
INSERT INTO dbo.test values('P@ssw1rd'),('This 12is @test')
Go
Update dbo.test
set dummydata=dbo.Replace2(dummydata,'[0-9@]','')

select * from dbo.test
dummydata, Psswrd, This is test booom!!!!!!!!!!!!!

    11. from https://stackoverflow.com/questions/1580017/how-to-replace-multiple-characters-in-sql by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 어떻게 SQL과 중복 행을 삭제하려면?  (0) 2020.04.15
[SQL] 포스트 그레스 데이터 형식 캐스트  (0) 2020.04.15
[SQL] MySQL의에서 INTERSECT  (0) 2020.04.15
[SQL] DISTINCT 여러 열을 선택 MYSQL  (0) 2020.04.15
[SQL] SQL 서버는 가장 최근의 값을 사용하여 별개의 행을 선택  (0) 2020.04.15
,

티스토리툴바