All geek questions in one place

SQL Server equivalent of substring_index function in MySQL

I am trying to transfer a query from MySQL to SQL SERVER 2012.

How to write equivalent for MySQL substring_index ()?

MySQL SUBSTRING_INDEX () returns the substring from the given string before the specified number of separator occurrences.

SUBSTRING_INDEX (str, delim, count)

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

Exit: 'www.somewebsite'

+6
source share
4 answers

Try this solution based on T-SQL and XQuery ( (root/row)[position() <= sql:variable("@count")] ):

T-SQL scalar function:

 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;

Output:

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

or

TSQL Inline Table-Valued Function:

 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;

Output:

 /* Value Result ------------------- --------------- www.somewebsite.com www.somewebsite www.yahoo.com www.yahoo www.outlook.com www.outlook */
+5
source 
  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

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

on the left, select dbo.FN_SUBSTRING_INDEX (N'prueba ',' ue ', - 1);

+1
source

I needed this recently, so I wrote the following stored function. At the end, a bunch of tests to make sure that it works exactly like the MySql function (the expected results were copied from MySql after the same tests were run):

 -- 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;

Here's a version based on Bogdan Sahlean that responds to using SQL Server XML functionality to parse and merge:

 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;

Running tests gives the following:

 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
+1
source

Try this one ...

 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)
0
source

Source: https://habr.com/ru/post/969826/

More articles:


All Articles