SQL Server équivalent de substring_index fonction dans MySQL

Je suis en train de le port d'une requête à partir de MySQL pour SQL SERVER 2012.

Comment puis-je écrire un équivalent pour MySQL substring_index()?

MySQL SUBSTRING_INDEX() retourne la sous-chaîne d'une chaîne donnée avant un certain nombre d'occurrences d'un séparateur.

SUBSTRING_INDEX(str, delim, comte)

SELECT SUBSTRING_INDEX('www.somewebsite.com','.',2);

De sortie: 'www.somewebsite'

il n'y a pas d'équivalent direct . vous aurez besoin d'écrire votre propre.
Trouvé un implelemtation ici.

InformationsquelleAutor Ankit | 2014-05-25

Essayer cette solution basée sur des T-SQL et XQuery((root/row)[position() <= sql:variable("@count")]):

T-SQL fonction Scalaire:

CREATE FUNCTION dbo.SUBSTRING_INDEX
(
    @str NVARCHAR(4000),
    @delim NVARCHAR(1),
    @count INT
)
RETURNS NVARCHAR(4000)
WITH SCHEMABINDING
BEGIN
    DECLARE @XmlSourceString XML;
    SET @XmlSourceString = (SELECT N'<root><row>' + REPLACE( (SELECT @str AS '*' FOR XML PATH('')) , @delim, N'</row><row>' ) + N'</row></root>');

    RETURN STUFF
    (
        ((
            SELECT  @delim + x.XmlCol.value(N'(text())[1]', N'NVARCHAR(4000)') AS '*'
            FROM    @XmlSourceString.nodes(N'(root/row)[position() <= sql:variable("@count")]') x(XmlCol)
            FOR XML PATH(N''), TYPE
        ).value(N'.', N'NVARCHAR(4000)')), 
        1, 1, N''
    );
END
GO

SELECT dbo.SUBSTRING_INDEX(N'www.somewebsite.com', N'.', 2) AS Result;

De sortie:

/*
Result
---------------
www.somewebsite
*/

TSQL Fonction Table en ligne:

CREATE FUNCTION dbo.SUBSTRING_INDEX
(
    @str NVARCHAR(4000),
    @delim NVARCHAR(1),
    @count INT
)
RETURNS TABLE
AS 
RETURN
    WITH Base
    AS 
    (
        SELECT XmlSourceString = CONVERT(XML, (SELECT N'<root><row>' + REPLACE( (SELECT @str AS '*' FOR XML PATH('')) , @delim, N'</row><row>' ) + N'</row></root>'))
    )   
    SELECT STUFF
    (
        ((
            SELECT  @delim + x.XmlCol.value(N'(text())[1]', N'NVARCHAR(4000)') AS '*'
            FROM    Base b 
            CROSS APPLY b.XmlSourceString.nodes(N'(root/row)[position() <= sql:variable("@count")]') x(XmlCol)
            FOR XML PATH(N''), TYPE
        ).value(N'.', N'NVARCHAR(4000)')), 
        1, 1, N''
    ) AS Result;
GO

SELECT  *
FROM    (
    SELECT N'www.somewebsite.com' UNION ALL 
    SELECT N'www.yahoo.com' UNION ALL 
    SELECT N'www.outlook.com'
) a(Value)
CROSS APPLY dbo.SUBSTRING_INDEX(a.Value, N'.', 2) b;

De sortie:

/*
Value               Result
------------------- ---------------
www.somewebsite.com www.somewebsite
www.yahoo.com       www.yahoo
www.outlook.com     www.outlook
*/

Cette solution est incomplète car elle ne prend pas en charge l' @count négatif (qui effectue une recherche à partir de la fin de la chaîne, au lieu de le début).
Utiliser le REVERSE deux fois.
msdn.microsoft.com/en-us/library/ms180040.aspx
En fait, vous devez inverser le str, mais vous devez également avoir à inverser la delim (si votre mise en œuvre, à la différence de la fonction MySql, ne permet que la delim à un seul caractère). Si vous exécutez l'ensemble de tests à partir de ma solution, vous allez voir que votre fonction passe seulement cinq des 15 tests, et un de ceux qui est accidentel (numéro de l'essai 9 arrive à avoir la première lettre de correspondance, trouver la bonne chaîne).
SÉLECTIONNEZ inverser(dbo.SUBSTRING_INDEX(reverse('www.mysql.com'), '.', 2));

InformationsquelleAutor Bogdan Sahlean

J'avais besoin de cela récemment, donc j'ai écrit cette fonction stockée. À la fin sont un tas de tests pour vérifier qu'il fonctionne exactement comme la fonction MySql n' (les résultats attendus ont été copiés à partir de MySql après ces tests là):

-- Function to reproduce the useful functionality of SUBSTRING_INDEX from MySql
CREATE FUNCTION dbo.SUBSTRING_INDEX(@InString  NVARCHAR(Max),
@Delimiter NVARCHAR(Max),
@Count     INT)
RETURNS NVARCHAR(200)
AS
BEGIN
DECLARE @Pos INT;
DECLARE @DelimiterOffsets TABLE
(
i      INT IDENTITY(1, 1) NOT NULL,
offset INT NOT NULL
);
-- If @Count is zero, we return '' as per spec
IF @Count = 0
BEGIN
RETURN '';
END;
DECLARE @OrigLength      INT = LEN(@InString);
DECLARE @DelimiterLength INT = LEN(@Delimiter);
-- Prime the pump.
SET @Pos = Charindex(@Delimiter, @InString, 1);
-- If the delimiter does not exist in @InString, return the whole string
IF @Pos = 0
BEGIN
RETURN @InString;
END;
-- Put all delimiter offsets into @DelimiterOffsets, they get numbered automatically.
DECLARE @CurrentOffset INT = 0;
WHILE @Pos > 0
BEGIN
SET @CurrentOffset = @Pos;
INSERT INTO @DelimiterOffsets
(offset)
VALUES (@CurrentOffset);
SET @Pos = Charindex(@Delimiter, @InString, @CurrentOffset + @DelimiterLength);
END;
-- This number is guaranteed to be > 0.
DECLARE @DelimitersFound INT = (SELECT Count(*) FROM @DelimiterOffsets);
-- If they requested more delimiters than were found, return the whole string, as per spec.
IF Abs(@Count) > @DelimitersFound
BEGIN
RETURN @InString;
END;
DECLARE @StartSubstring INT = 0;
DECLARE @EndSubstring   INT = @OrigLength;
-- OK, now return the part they requested
IF @Count > 0
BEGIN
SET @EndSubstring = (SELECT offset 
FROM @DelimiterOffsets 
WHERE i = @Count);
END
ELSE
BEGIN
SET @StartSubstring = (SELECT offset + @DelimiterLength 
FROM @DelimiterOffsets 
WHERE i = (@DelimitersFound + @Count + 1));
END;
RETURN Substring(@InString, @StartSubstring, @EndSubstring);
END; 
Go 
GRANT EXECUTE ON [dbo].SUBSTRING_INDEX TO PUBLIC;
-- Tests
DECLARE @TestResults TABLE (i int, answer nVarChar(MAX), expected nVarChar(MAX));
insert into @TestResults
select * from  
(
(SELECT  1 as i, [dbo].SUBSTRING_INDEX(N'www.somewebsite.com', N'.', 2)    as r, 'www.somewebsite'     as e) UNION
(SELECT  2 as i, [dbo].SUBSTRING_INDEX(N'www.yahoo.com', N'.', 2)          as r, 'www.yahoo'           as e) UNION
(SELECT  3 as i, [dbo].SUBSTRING_INDEX(N'www.outlook.com', N'.', 2)        as r, 'www.outlook'         as e) UNION
(SELECT  4 as i, [dbo].SUBSTRING_INDEX(N'www.somewebsite.com', N'.', -2)   as r, 'somewebsite.com'     as e) UNION
(SELECT  5 as i, [dbo].SUBSTRING_INDEX(N'www.yahoo.com', N'.', -2)         as r, 'yahoo.com'           as e) UNION
(SELECT  6 as i, [dbo].SUBSTRING_INDEX(N'www.outlook.com', N'.', -2)       as r, 'outlook.com'         as e) UNION
(select  7 as i, [dbo].SUBSTRING_INDEX('hi.you.com','.',2)                 as r, 'hi.you'              as e) UNION
(select  8 as i, [dbo].SUBSTRING_INDEX('hi.you.com','.',-1)                as r, 'com'                 as e) UNION
(select  9 as i, [dbo].SUBSTRING_INDEX(N'prueba','ue',1)                   as r, 'pr'                  as e) UNION
(select 10 as i, [dbo].SUBSTRING_INDEX(N'prueba','ue',-1)                  as r, 'ba'                  as e) UNION
(select 11 as i, [dbo].SUBSTRING_INDEX(N'prueba','ue',0)                   as r, ''                    as e) UNION
(SELECT 12 as i, [dbo].SUBSTRING_INDEX(N'wwwxxxoutlookxxxcom', N'xxx', 2)  as r, 'wwwxxxoutlook'       as e) UNION
(SELECT 13 as i, [dbo].SUBSTRING_INDEX(N'wwwxxxoutlookxxxcom', N'xxx', -2) as r, 'outlookxxxcom'       as e) UNION
(SELECT 14 as i, [dbo].SUBSTRING_INDEX(N'wwwxxxoutlookxxxcom', N'xxx', 5)  as r, 'wwwxxxoutlookxxxcom' as e) UNION
(SELECT 15 as i, [dbo].SUBSTRING_INDEX(N'wwwxxxoutlookxxxcom', N'xxx', -5) as r, 'wwwxxxoutlookxxxcom' as e)
) as results;
select tr.i,
tr.answer,
tr.expected,
CASE WHEN tr.answer = tr.expected THEN 'Test Succeeded' ELSE 'Test Failed' END testState
from @TestResults tr
order by i;

Voici une version inspirée par Bogdan Sahlean's réponse à l'aide de SQL Server XML de la fonctionnalité permettant d'effectuer l'analyse et la combinaison de:

CREATE FUNCTION dbo.SUBSTRING_INDEX(@InString  NVARCHAR(Max),
@Delimiter NVARCHAR(Max),
@Count     INT)
RETURNS NVARCHAR(200)
AS
BEGIN
-- If @Count is zero, we return '' as per spec
IF @Count = 0
BEGIN
RETURN '';
END;
-- First we let the XML parser break up the string by @Delimiter.
-- Each parsed value will be <piece>[text]</piece>.
DECLARE @XmlSourceString XML = (select N'<piece>' + REPLACE( (SELECT @InString AS '*' FOR XML PATH('')) , @Delimiter, N'</piece><piece>' ) + N'</piece>');
-- This will contain the final requested string.
DECLARE @Results nVarChar(MAX);
;WITH Pieces(RowNumber, Piece) as
(
-- Take each node in @XmlSourceString, and return it with row numbers
-- which will identify each piece and give us a handle to change the
-- order, depending on the direction of search.
SELECT  row_number() over(order by x.XmlCol) as RowNumber,
@Delimiter + x.XmlCol.value(N'(text())[1]', N'NVARCHAR(MAX)') AS '*'
FROM  @XmlSourceString.nodes(N'(piece)') x(XmlCol)
), orderedPieces(RowNumber, Piece) as
(
-- Order the pieces normally or reversed depending on whether they want
-- the first @Count pieces or the last @Count pieces.
select TOP (ABS(@Count)) 
RowNumber, 
Piece
from Pieces
ORDER BY CASE WHEN @Count < 0 THEN RowNumber END DESC ,
CASE WHEN @Count > 0 THEN RowNumber END ASC
), combinedPieces(result) as
(
-- Now combine the pieces back together, ordering them by
-- the original order.  There will always
-- be an extra @Delimiter on the front of the string.
select CAST(Piece AS VARCHAR(100))
from OrderedPieces
order by RowNumber
FOR XML PATH(N'')
)
-- Finally, strip off the extra delimiter using STUFF and store the string in @Results.
select @Results = STUFF(result, 1, LEN(@Delimiter), '') from combinedPieces;
return @Results;
END;

Exécutant les tests de ce produit:

i  answer              expected             testState
1  www.somewebsite     www.somewebsite      Test Succeeded
2  www.yahoo           www.yahoo            Test Succeeded
3  www.outlook         www.outlook          Test Succeeded
4  somewebsite.com     somewebsite.com      Test Succeeded
5  yahoo.com           yahoo.com            Test Succeeded
6  outlook.com         outlook.com          Test Succeeded
7  hi.you              hi.you               Test Succeeded
8  com                 com                  Test Succeeded
9  pr                  pr                   Test Succeeded
10 ba                  ba                   Test Succeeded
11                                          Test Succeeded
12 wwwxxxoutlook       wwwxxxoutlook        Test Succeeded
13 outlookxxxcom       outlookxxxcom        Test Succeeded
14 wwwxxxoutlookxxxcom wwwxxxoutlookxxxcom  Test Succeeded
15 wwwxxxoutlookxxxcom wwwxxxoutlookxxxcom  Test Succeeded

InformationsquelleAutor Scott Gartner

    CREATE FUNCTION FN_SUBSTRING_INDEX
(
@TEXTO NVARCHAR(200),
@SUBSTRING_INDEX NVARCHAR(10),
@DESPLAZAMIENTO INT
)
RETURNS NVARCHAR(200)
AS
BEGIN
DECLARE @indiceSubstring INT
DECLARE @RESULTADO NVARCHAR(200)
SELECT @indiceSubstring = CHARINDEX(@SUBSTRING_INDEX,@TEXTO)
IF @DESPLAZAMIENTO > 0
BEGIN
SELECT @RESULTADO=SUBSTRING(@TEXTO,@indiceSubstring+@DESPLAZAMIENTO+1,LEN(@TEXTO))
END 
ELSE
BEGIN
SELECT @RESULTADO=SUBSTRING(@TEXTO,0,@indiceSubstring-@DESPLAZAMIENTO-1)
END 
RETURN @RESULTADO
END
GO

à droite
select dbo.FN_SUBSTRING_INDEX(N'prueba','ue',1);

à gauche
select dbo.FN_SUBSTRING_INDEX(N'prueba','ue", -1);

InformationsquelleAutor Gorson

Essayer Celui-Ci.....

CREATE FUNCTION SubString_Index
(
@ExistingString NVARCHAR(200),
@BreakPoint NVARCHAR(10),
@number INT
)
RETURNS NVARCHAR(200)
AS
BEGIN
DECLARE @Count INT
DECLARE @Substring NVARCHAR(200)
DECLARE @ssubstring NVARCHAR(200)
SET @ssubstring=@ExistingString
DECLARE @scount INT
SET @scount=0
DECLARE @sscount INT
SET @sscount=0
WHILE(@number>@scount)
BEGIN
Select @Count=CHARINDEX(@BreakPoint,@ExistingString)
Select @ExistingString=SUBSTRING(@ExistingString,@Count+1,LEN(@ExistingString))
Select @scount=@scount+1 
select @sscount=@sscount+@Count
END
SELECT @Substring=SUBSTRING(@ssubstring,0,@sscount)
RETURN @Substring
END
GO
select dbo.SubStringIndex('hi.you.com','.',1)

InformationsquelleAutor ashik vokkaliga

Vous devez vous connecter pour publier un commentaire.