IT박스

T-SQL을 사용하여 하위 문자열의 마지막 항목 색인 찾기

itboxs 2020. 7. 22. 08:22
반응형

T-SQL을 사용하여 하위 문자열의 마지막 항목 색인 찾기

SQL을 사용하여 마지막으로 나타나는 문자열의 색인을 찾는 간단한 방법이 있습니까? 지금 SQL Server 2000을 사용하고 있습니다. 기본적으로 .NET System.String.LastIndexOf메서드가 제공 하는 기능이 필요 합니다. 약간의 인터넷 검색에서 이것을 밝혀 냈습니다- 마지막 색인을 검색하는 기능 - "텍스트"열 표현식을 전달하면 작동하지 않습니다. 다른 곳에서 찾은 다른 솔루션은 검색하는 텍스트가 1 자 길이 인 경우에만 작동합니다.

아마도 기능을 요리해야 할 것입니다. 그렇게하면 여기에 게시하여 여러분이보고 사용하도록하겠습니다.

텍스트 데이터 유형에 대한 작은 기능 목록으로 제한됩니다 .

내가 제안 할 수있는 것은로 시작 PATINDEX하지만 DATALENGTH-1, DATALENGTH-2, DATALENGTH-3결과를 얻거나 0으로 끝날 때까지 등에서 뒤로 작동합니다 (DATALENGTH-DATALENGTH)

이것은 실제로 SQL Server 2000처리 할 수없는 것입니다 .

다른 답변 편집 : REVERSE는 SQL Server 2000의 텍스트 데이터와 함께 사용할 수있는 함수 목록에 없습니다.

간단한 방법? 아니요, 그러나 나는 그 반대를 사용했습니다. 말 그대로.

이전 루틴에서 주어진 문자열의 마지막 발생을 찾기 위해 REVERSE () 함수를 사용한 다음 CHARINDEX를 사용한 다음 다시 REVERSE를 사용하여 원래 순서를 복원했습니다. 예를 들어 :

SELECT
   mf.name
  ,mf.physical_name
  ,reverse(left(reverse(physical_name), charindex('\', reverse(physical_name)) -1))
 from sys.master_files mf

하위 폴더에 얼마나 깊이 중첩되어 있는지에 관계없이 "실제 이름"에서 실제 데이터베이스 파일 이름을 추출하는 방법을 보여줍니다. 이것은 하나의 문자 (백 슬래시) 만 검색하지만 더 긴 검색 문자열을 위해이를 작성할 수 있습니다.

유일한 단점은 이것이 TEXT 데이터 유형에서 얼마나 잘 작동하는지 모르겠습니다. 나는 몇 년 동안 SQL 2005를 사용해 왔으며 더 이상 TEXT 작업에 익숙하지 않지만 왼쪽과 오른쪽을 사용할 수 있다고 생각합니다.

필립

가장 간단한 방법은 ...

REVERSE(SUBSTRING(REVERSE([field]),0,CHARINDEX('[expr]',REVERSE([field]))))

Sqlserver 2005 이상을 사용하는 경우 REVERSE함수를 여러 번 사용 하면 성능이 저하되므로 아래 코드가 더 효율적입니다.

DECLARE @FilePath VARCHAR(50) = 'My\Super\Long\String\With\Long\Words'
DECLARE @FindChar VARCHAR(1) = '\'

-- Shows text before last slash
SELECT LEFT(@FilePath, LEN(@FilePath) - CHARINDEX(@FindChar,REVERSE(@FilePath))) AS Before
-- Shows text after last slash
SELECT RIGHT(@FilePath, CHARINDEX(@FindChar,REVERSE(@FilePath))-1) AS After
-- Shows the position of the last slash
SELECT LEN(@FilePath) - CHARINDEX(@FindChar,REVERSE(@FilePath)) AS LastOccuredAt

DECLARE @FilePath VARCHAR(50) = 'My\Super\Long\String\With\Long\Words'
DECLARE @FindChar VARCHAR(1) = '\'

SELECT LEN(@FilePath) - CHARINDEX(@FindChar,REVERSE(@FilePath)) AS LastOccuredAt

오래되었지만 여전히 유효한 질문이므로 여기에 다른 사람들이 제공 한 정보를 기반으로 만든 내용이 있습니다.

create function fnLastIndexOf(@text varChar(max),@char varchar(1))
returns int
as
begin
return len(@text) - charindex(@char, reverse(@text)) -1
end

이것은 나를 위해 아주 잘 작동했습니다.

REVERSE(SUBSTRING(REVERSE([field]), CHARINDEX(REVERSE('[expr]'), REVERSE([field])) + DATALENGTH('[expr]'), DATALENGTH([field])))

REVERSE(SUBSTRING(REVERSE(ap_description),CHARINDEX('.',REVERSE(ap_description)),len(ap_description)))

나를 위해 더 잘했다

흠, 나는 이것이 오래된 스레드라는 것을 알고 있지만, 집계 테이블은 SQL2000 (또는 다른 데이터베이스)에서 이것을 할 수 있습니다.

DECLARE @str CHAR(21),
        @delim CHAR(1)
 SELECT @str = 'Your-delimited-string',
        @delim = '-'

SELECT
    MAX(n) As 'position'
FROM
    dbo._Tally
WHERE
    substring(@str, _Tally.n, 1) = @delim

탈리 테이블은 증가하는 숫자 테이블입니다.

은 substring(@str, _Tally.n, 1) = @delim다음 그냥 세트의 최대 위치를 얻을, 각 단락의 위치를 가져옵니다.

탈리 테이블은 훌륭합니다. 이전에 사용해 본 적이 없다면 SQL Server Central 에 대한 좋은 기사가 있습니다 (무료 등록 또는 Bug Me Not ( http://www.bugmenot.com/view/sqlservercentral.com )).

* 편집 : n <= LEN(TEXT_FIELD)TEXT 유형에 LEN ()을 사용할 수 없으므로 제거 되었습니다. 만큼으로 substring(...) = @delim남아 그 결과는 여전히 정확하지만.

문자열과 하위 문자열을 모두 바꾸고 첫 번째 항목을 검색하십시오.

나는 이것이 몇 년 된 질문이라는 것을 알고 있지만 ...

켜짐 Access 2010, 당신이 사용할 수있는 InStrRev()이 작업을 수행 할 수 있습니다. 도움이 되었기를 바랍니다.

나는 그것이 비효율적이라는 것을 알고 있지만 당신이 찾은 웹 사이트에서 제공하는 솔루션을 사용할 수 있도록 text필드 캐스팅을 고려 varchar했습니까? text필드 의 길이가 길이보다 길면 레코드가 잘릴 수 있으므로이 솔루션으로 인해 문제가 발생할 수 있음을 알고 varchar있습니다.

데이터가 text필드 안에 있고 SQL Server 2000을 사용하고 있기 때문에 옵션이 제한됩니다.

단어 문자열에서 마지막 공백의 색인을 얻으려면이 표현식 RIGHT (name, (CHARINDEX ( '', REVERSE (name), 0))을 사용하여 문자열의 마지막 단어를 리턴 할 수 있습니다. 이름 및 / 또는 중간 이름의 이니셜이 포함 된 전체 이름의 성을 구문 분석하려는 경우 유용합니다.

@indexOf = <whatever characters you are searching for in your string>

@LastIndexOf = LEN([MyField]) - CHARINDEX(@indexOf, REVERSE([MyField]))

테스트를 거치지 않았지만 인덱스가 0이기 때문에 1만큼 꺼져있을 수 있지만 문자에서 문자열 끝까지 SUBSTRING잘릴 때 작동 @indexOf합니다.

SUBSTRING([MyField], 0, @LastIndexOf)

다른 답변 중 일부는 실제 문자열을 반환하지만 실제 인덱스 int를 더 알아야합니다. 그리고 그렇게하는 답은 지나치게 복잡해 보입니다. 다른 답변 중 일부를 영감으로 사용하여 다음을 수행했습니다.

먼저 함수를 만들었습니다.

CREATE FUNCTION [dbo].[LastIndexOf] (@stringToFind varchar(max), @stringToSearch varchar(max))
RETURNS INT
AS
BEGIN
    RETURN (LEN(@stringToSearch) - CHARINDEX(@stringToFind,REVERSE(@stringToSearch))) + 1
END
GO

그런 다음 쿼리에서 간단히 다음을 수행 할 수 있습니다.

declare @stringToSearch varchar(max) = 'SomeText: SomeMoreText: SomeLastText'

select dbo.LastIndexOf(':', @stringToSearch)

위의 값은 23을 반환해야합니다 ( ':'의 마지막 색인)

이것이 누군가에게 조금 더 쉬워 졌기를 바랍니다!

이 답변은 MS SQL Server 2008 (MS SQL Server 2000에 액세스 할 수 없음)을 사용하지만 OP에 따라 보는 방식은 3 가지 상황을 고려해야합니다. 내가 시도하지 않은 답변에서 여기에 3 가지 모두가 포함됩니다.

  1. 주어진 문자열에서 검색 문자의 마지막 색인을 반환합니다.
  2. 주어진 문자열에서 검색 하위 문자열 (단일 문자 이상)의 마지막 인덱스를 반환합니다.
  3. 검색 문자 또는 하위 문자열이 주어진 문자열에 없으면 0

내가 생각해 낸 기능에는 두 가지 매개 변수가 있습니다.

@String NVARCHAR(MAX) : 검색 할 문자열

@FindString NVARCHAR(MAX) :의 마지막 색인을 가져올 단일 문자 또는 하위 문자열 @String

in INT의 양의 인덱스 이거나에없는 의미를 반환합니다 .@FindString@String0@FindString@String

함수의 기능에 대한 설명은 다음과 같습니다.

  1. 를 초기화 @ReturnVal하는 0것을 의미는 @FindString아닙니다@String
  2. 수표의 인덱스 @FindString에 @String사용하여CHARINDEX()
  3. 의 인덱스 경우 @FindString에이 @String되고 0, @ReturnVal로 남겨0
  4. 의 인덱스 경우 @FindString에가 @String있다 > 0, @FindString에 @String그것의 마지막 인덱스 계산 때문에 @FindString에서를 @String사용하여REVERSE()
  5. 반환 @ReturnVal의 마지막 인덱스 양수 중 하나입니다 @FindString에 @String또는 0그 지시가 @FindString아닌@String

함수 작성 스크립트 (복사 및 붙여 넣기 준비)는 다음과 같습니다.

CREATE FUNCTION [dbo].[fn_LastIndexOf] 
(@String NVARCHAR(MAX)
, @FindString NVARCHAR(MAX))
RETURNS INT
AS 
BEGIN
    DECLARE @ReturnVal INT = 0
    IF CHARINDEX(@FindString,@String) > 0
        SET @ReturnVal = (SELECT LEN(@String) - 
        (CHARINDEX(REVERSE(@FindString),REVERSE(@String)) + 
        LEN(@FindString)) + 2)  
    RETURN @ReturnVal
END

다음은 함수를 편리하게 테스트하는 방법입니다.

DECLARE @TestString NVARCHAR(MAX) = 'My_sub2_Super_sub_Long_sub1_String_sub_With_sub_Long_sub_Words_sub2_'
, @TestFindString NVARCHAR(MAX) = 'sub'

SELECT dbo.fn_LastIndexOf(@TestString,@TestFindString)

다른 버전에 액세스 할 수 없기 때문에 MS SQL Server 2008에서만이 기능을 실행했지만이 내용을 살펴보면 적어도 2008 이상에서 좋을 것입니다.

즐겨.

폴더 경로에서 백 슬래시의 n 번째 마지막 위치를 찾아야했습니다. 여기 내 해결책이 있습니다.

/*
http://stackoverflow.com/questions/1024978/find-index-of-last-occurrence-of-a-sub-string-using-t-sql/30904809#30904809
DROP FUNCTION dbo.GetLastIndexOf
*/
CREATE FUNCTION dbo.GetLastIndexOf
(
  @expressionToFind         VARCHAR(MAX)
  ,@expressionToSearch      VARCHAR(8000)
  ,@Occurrence              INT =  1        -- Find the nth last 
)
RETURNS INT
AS
BEGIN

    SELECT  @expressionToSearch = REVERSE(@expressionToSearch)

    DECLARE @LastIndexOf        INT = 0
            ,@IndexOfPartial    INT = -1
            ,@OriginalLength    INT = LEN(@expressionToSearch)
            ,@Iteration         INT = 0

    WHILE (1 = 1)   -- Poor man's do-while
    BEGIN
        SELECT @IndexOfPartial  = CHARINDEX(@expressionToFind, @expressionToSearch)

        IF (@IndexOfPartial = 0) 
        BEGIN
            IF (@Iteration = 0) -- Need to compensate for dropping out early
            BEGIN
                SELECT @LastIndexOf = @OriginalLength  + 1
            END
            BREAK;
        END

        IF (@Occurrence > 0)
        BEGIN
            SELECT @expressionToSearch = SUBSTRING(@expressionToSearch, @IndexOfPartial + 1, LEN(@expressionToSearch) - @IndexOfPartial - 1)
        END

        SELECT  @LastIndexOf = @LastIndexOf + @IndexOfPartial
                ,@Occurrence = @Occurrence - 1
                ,@Iteration = @Iteration + 1

        IF (@Occurrence = 0) BREAK;
    END

    SELECT @LastIndexOf = @OriginalLength - @LastIndexOf + 1 -- Invert due to reverse
    RETURN @LastIndexOf 
END
GO

GRANT EXECUTE ON GetLastIndexOf TO public
GO

다음은 통과 한 테스트 사례입니다.

SELECT dbo.GetLastIndexOf('f','123456789\123456789\', 1) as indexOf -- expect 0 (no instances)
SELECT dbo.GetLastIndexOf('\','123456789\123456789\', 1) as indexOf -- expect 20
SELECT dbo.GetLastIndexOf('\','123456789\123456789\', 2) as indexOf -- expect 10
SELECT dbo.GetLastIndexOf('\','1234\6789\123456789\', 3) as indexOf -- expect 5

구분 기호가 마지막으로 표시되기 전에 부품을 가져 오려면 ( 사용상 NVARCHAR이유로 만 작동 DATALENGTH) :

DECLARE @Fullstring NVARCHAR(30) = '12.345.67890.ABC';

DECLARE @Delimiter CHAR(1) = '.';

SELECT SUBSTRING(@Fullstring, 1, DATALENGTH(@Fullstring)/2 - CHARINDEX(@Delimiter, REVERSE(@Fullstring)));

This answer meets the requirements of the OP. specifically it allows the needle to be more than a single character and it does not generate an error when needle is not found in haystack. It seemed to me that most (all?) of the other answers did not handle those edge cases. Beyond that I added the "Starting Position" argument provided by the native MS SQL server CharIndex function. I tried to exactly mirror the specification for CharIndex except to process right to left instead of left to right. eg I return null if either needle or haystack is null and I return zero if needle is not found in haystack. One thing that I could not get around is that with the built in function the third parameter is optional. With SQL Server user defined functions, all parameters must be provided in the call unless the function is called using "EXEC" . While the third parameter must be included in the parameter list, you can provide the keyword "default" as a placeholder for it without having to give it a value (see examples below). Since it is easier to remove the third parameter from this function if not desired than it would be to add it if needed I have included it here as a starting point.

create function dbo.lastCharIndex(
 @needle as varchar(max),
 @haystack as varchar(max),
 @offset as bigint=1
) returns bigint as begin
 declare @position as bigint
 if @needle is null or @haystack is null return null
 set @position=charindex(reverse(@needle),reverse(@haystack),@offset)
 if @position=0 return 0
 return (len(@haystack)-(@position+len(@needle)-1))+1
end
go

select dbo.lastCharIndex('xyz','SQL SERVER 2000 USES ANSI SQL',default) -- returns 0
select dbo.lastCharIndex('SQL','SQL SERVER 2000 USES ANSI SQL',default) -- returns 27
select dbo.lastCharIndex('SQL','SQL SERVER 2000 USES ANSI SQL',1) -- returns 27
select dbo.lastCharIndex('SQL','SQL SERVER 2000 USES ANSI SQL',11) -- returns 1

I came across this thread while searching for a solution to my similar problem which had the exact same requirement but was for a different kind of database that was also lacking the REVERSE function.

In my case this was for a OpenEdge (Progress) database, which has a slightly different syntax. This made the INSTR function available to me that most Oracle typed databases offer.

So I came up with the following code:

SELECT 
  INSTR(foo.filepath, '/',1, LENGTH(foo.filepath) - LENGTH( REPLACE( foo.filepath, '/',  ''))) AS IndexOfLastSlash 
FROM foo

However, for my specific situation (being the OpenEdge (Progress) database) this did not result into the desired behaviour because replacing the character with an empty char gave the same length as the original string. This doesn't make much sense to me but I was able to bypass the problem with the code below:

SELECT 
  INSTR(foo.filepath, '/',1, LENGTH( REPLACE( foo.filepath, '/',  'XX')) - LENGTH(foo.filepath))  AS IndexOfLastSlash 
FROM foo

Now I understand that this code won't solve the problem for T-SQL because there is no alternative to the INSTR function that offers the Occurence property.

Just to be thorough I'll add the code needed to create this scalar function so it can be used the same way like I did in the above examples.

  -- Drop the function if it already exists
  IF OBJECT_ID('INSTR', 'FN') IS NOT NULL
    DROP FUNCTION INSTR
  GO

  -- User-defined function to implement Oracle INSTR in SQL Server
  CREATE FUNCTION INSTR (@str VARCHAR(8000), @substr VARCHAR(255), @start INT, @occurrence INT)
  RETURNS INT
  AS
  BEGIN
    DECLARE @found INT = @occurrence,
            @pos INT = @start;

    WHILE 1=1 
    BEGIN
        -- Find the next occurrence
        SET @pos = CHARINDEX(@substr, @str, @pos);

        -- Nothing found
        IF @pos IS NULL OR @pos = 0
            RETURN @pos;

        -- The required occurrence found
        IF @found = 1
            BREAK;

        -- Prepare to find another one occurrence
        SET @found = @found - 1;
        SET @pos = @pos + 1;
    END

    RETURN @pos;
  END
  GO

To avoid the obvious, when the REVERSE function is available you do not need to create this scalar function and you can just get the required result like this:

SELECT
  LEN(foo.filepath) - CHARINDEX('/', REVERSE(foo.filepath))+1 AS LastIndexOfSlash 
FROM foo

This code works even if the substring contains more than 1 character.

DECLARE @FilePath VARCHAR(100) = 'My_sub_Super_sub_Long_sub_String_sub_With_sub_Long_sub_Words'
DECLARE @FindSubstring VARCHAR(5) = '_sub_'

-- Shows text before last substing
SELECT LEFT(@FilePath, LEN(@FilePath) - CHARINDEX(REVERSE(@FindSubstring), REVERSE(@FilePath)) - LEN(@FindSubstring) + 1) AS Before
-- Shows text after last substing
SELECT RIGHT(@FilePath, CHARINDEX(REVERSE(@FindSubstring), REVERSE(@FilePath)) -1) AS After
-- Shows the position of the last substing
SELECT LEN(@FilePath) - CHARINDEX(REVERSE(@FindSubstring), REVERSE(@FilePath)) AS LastOccuredAt

참고URL : https://stackoverflow.com/questions/1024978/find-index-of-last-occurrence-of-a-sub-string-using-t-sql

반응형

'IT박스' 카테고리의 다른 글

Qt Creator-프로젝트 오류 : Xcode가 올바르게 설정되지 않았습니다.  (0) 2020.07.22
치명적인 오류 : AST 파일이 잘못되었거나 손상되었습니다.-Xcode  (0) 2020.07.22
왜 SQL을 좋아하지 않습니까?  (0) 2020.07.22
Xcode 8 Beta-현재 Swift 구문으로 변환 실패 : 테스트 호스트를 찾을 수 없습니다  (0) 2020.07.22
APK가 서명되었거나 "디버그 빌드"인지 확인하는 방법  (0) 2020.07.22

'IT박스'의 다른글

  • 현재글T-SQL을 사용하여 하위 문자열의 마지막 항목 색인 찾기

관련글

댓글

티스토리툴바