Hype Blast

Home / news

SQL Server equivalent of substring_index function in MySQL

Mia Morrison |

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

How do i write an equivalent for MySQL's substring_index()?

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

SUBSTRING_INDEX(str, delim, count) 

SELECT SUBSTRING_INDEX(');

Output: '

2

7 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' N'.', 2) AS Result;

Output:

/*
Result
---------------
*/

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' UNION ALL SELECT N' UNION ALL SELECT N'
) a(Value)
CROSS APPLY dbo.SUBSTRING_INDEX(a.Value, N'.', 2) b;

Output:

/*
Value Result
------------------- ---------------
*/
4

I needed this recently, so I wrote the following stored function. At the end are a bunch of tests to make sure it operates exactly as the MySql function does (the expected results were copied from MySql after running the same tests there):

-- 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' N'.', 2) as r, ' as e) UNION (SELECT 2 as i, [dbo].SUBSTRING_INDEX(N' N'.', 2) as r, ' as e) UNION (SELECT 3 as i, [dbo].SUBSTRING_INDEX(N' N'.', 2) as r, ' as e) UNION (SELECT 4 as i, [dbo].SUBSTRING_INDEX(N' N'.', -2) as r, 'somewebsite.com' as e) UNION (SELECT 5 as i, [dbo].SUBSTRING_INDEX(N' N'.', -2) as r, 'yahoo.com' as e) UNION (SELECT 6 as i, [dbo].SUBSTRING_INDEX(N' 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 inspired by Bogdan Sahlean's answer using SQL Server's XML functionality to do the parsing and combining:

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 the tests produces this:

i answer expected testState
1 Test Succeeded
2 Test Succeeded
3 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

My best bet is:

select substring(email,(charindex('@', email,1)+1),100) from yourtable;

assuming that TLD.EXT max 100 characters. You can increase as you like.

Good Luck

0 
 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

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

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

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)

This microsoft sql function Works exactly like substring_index function in mysql

/** This microsoft sql function Works exactly like substring_index function in mysql **/
CREATE FUNCTION SubString_Index
( @ExistingString NVARCHAR(MAX), @BreakPoint NVARCHAR(MAX), @number INT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Count INT
DECLARE @SubstringLength INT
DECLARE @Substring NVARCHAR(MAX)
DECLARE @ssubstring NVARCHAR(MAX)
SET @ssubstring=@ExistingString
DECLARE @scount INT
SET @scount=0
DECLARE @sscount INT
SET @sscount=0
DECLARE @number2 INT
DECLARE @occurence INT
SET @occurence=LEN(@ExistingString) - LEN(REPLACE(@ExistingString, @BreakPoint, ''))
If @number<0 BEGIN SET @number2= @occurence-(-1*@number)+1 END
If @number>0 BEGIN SET @number2=@number END
WHILE(@number2>@scount) BEGIN Select @Count=CHARINDEX(@BreakPoint,@ExistingString) Select @SubstringLength=@Count+LEN(@BreakPoint) Select @ExistingString=SUBSTRING(@ExistingString,@SubstringLength,LEN(@ExistingString)-@Count) Select @scount=@scount+1 select @sscount=@sscount+@Count END
If @number<0 BEGIN if (@number = -1) and (@sscount+LEN(@BreakPoint)) = (LEN(@ssubstring)+1) BEGIN SELECT @Substring='' END else if @occurence = 0 BEGIN SELECT @Substring='' END else BEGIN SELECT @Substring=SUBSTRING(@ssubstring, @sscount+LEN(@BreakPoint), LEN(@ssubstring)) END END
If @number>0 if @occurence = 0 BEGIN SELECT @Substring='' END else BEGIN SELECT @Substring=SUBSTRING(@ssubstring,0,@sscount) END
RETURN @Substring
END
GO
1

GETTING INDEX -2 WITH PYTHON

use my_guitar_shop;
go
EXEC sp_execute_external_script
@language =N'Python',
@script=N'
import pandas as pd
import numpy as np
x = np.array(InputDataSet["email_address"], dtype = str)
broken = np.char.split(x, sep = ".")
OutputDataSet = pd.DataFrame([".".join(e[-2:]) if len(e)>2 else ".".join(e) for e in broken])
',
@input_data_1 = N'SELECT email_address from customers;'
WITH RESULT SETS(([indexed_email] nvarchar(250)));

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password

Post as a guest

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

You Might Also Like