SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MS SQL alternative to SUBSTRING_INDEX


MS SQL alternative to SUBSTRING_INDEX

Author
Message
test
test
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 1
Hi

I am new to MS SQL but in MY SQL I used SUBSTRING_INDEX to extract a number of words. Is there an equiv. in MS SQL.

For example, I want to Select the first 5 words from a product description

The skilful blending of drawn animation and computer-generated imagery excited anime fans when this science fiction mystery was released in 1995

I need

The skilful blending of drawn

Thanks



The Dixie Flatline
The Dixie Flatline
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5496 Visits: 6900
The following code could be incorporated into a stored procedure, user defined function, or an inline table valued function.


declare @input varchar(max)
declare @count int
set @count = 5

set @input = 'The skilful blending of drawn animation and computer-generated imagery excited anime fans when this science fiction mystery was released in 1995'
set @input = @input+' '

-- this is the actual code, @input and @count would be passed as parameters
;with tally (N) as
(select row_number() over(order by (select null)) from master.sys.all_columns)
,Array AS
(select Row_Number() over (order by N) as X, N
from tally
where substring(@input,N,1) = ' '
)

select X as Words, left(@input,N-1) as sub_string from Array
where X = @count




__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Allister Reid
Allister Reid
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 3073
Ahhh, beat me to it Bob, been banging my head against the wall trying to get it to work on a table...



DECLARE @temp TABLE (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, TextDescription VARCHAR(255))
INSERT INTO @temp
SELECT 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.' UNION ALL
SELECT 'Nullam a libero et dui tristique malesuada.' UNION ALL
SELECT 'Donec ultricies sollicitudin magna, eu gravida lectus fringilla ut.' UNION ALL
SELECT 'Nunc vestibulum elit tincidunt velit pellentesque feugiat.' UNION ALL
SELECT 'Aliquam eu purus a neque fringilla porta.' UNION ALL
SELECT 'Fusce pretium justo vitae mauris tempor porttitor tincidunt orci laoreet.'

DECLARE @NumberOfWords INT
SET @NumberOfWords = 5

;WITH Tally AS
(SELECT TOP (255)
ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
FROM Master.sys.All_Columns t1
CROSS JOIN Master.sys.All_Columns t2
)
SELECT ID,
(
SELECT ' ' + Word
FROM
(
SELECT
ID, SUBSTRING(' ' + TextDescription + ' ', N + 1, CHARINDEX(' ', ' ' + TextDescription + ' ', N + 1) - N - 1) AS Word,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY N) AS Row
FROM Tally
CROSS JOIN @temp
WHERE N < LEN(TextDescription) AND SUBSTRING(' ' + TextDescription + ' ', N, 1) = ' '
) Words
WHERE temp.ID = Words.ID AND Row <= @NumberOfWords FOR XML PATH (''))
FROM @temp temp




///Edit - use @NumberOfWords
The Dixie Flatline
The Dixie Flatline
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5496 Visits: 6900
That'll work!!! I would have done it with a CROSS-APPLY like this, but for re-use an inline table valued function would be sweet. If anyone is interested, there is an monster discussion about about SQL vs. CLR text parsing here.


declare @count int
set @count = 5

DECLARE @temp1 TABLE (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, TextDescription VARCHAR(255))
INSERT INTO @temp1
SELECT 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.' UNION ALL
SELECT 'Nullam a libero et dui tristique malesuada.' UNION ALL
SELECT 'Donec ultricies sollicitudin magna, eu gravida lectus fringilla ut.' UNION ALL
SELECT 'Nunc vestibulum elit tincidunt velit pellentesque feugiat.' UNION ALL
SELECT 'Aliquam eu purus a neque fringilla porta.' UNION ALL
SELECT 'Fusce pretium justo vitae mauris tempor porttitor tincidunt orci laoreet.'

;with tally (N) as
(select row_number() over(order by (select null)) from master.sys.all_columns)

select id, left(textDescription,N-1) as sub_string
from @temp1
cross apply (select Row_Number() over (order by N) as X, N
from tally
where substring(textDescription,N,1) = ' '
and N <= len(textDescription)
) ca
where ca.X = @count



__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
test
test
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 1
Wow, that's complex! - Thanks



Allister Reid
Allister Reid
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 3073
Bob, thanks for the link.

Problem with your solution when @count < word count of description then nothing returned here's a fix:

declare @count int
set @count = 10000

DECLARE @temp1 TABLE (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, TextDescription VARCHAR(255))
INSERT INTO @temp1
SELECT 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.' UNION ALL
SELECT 'Nullam a libero et dui tristique malesuada.' UNION ALL
SELECT 'Donec ultricies sollicitudin magna, eu gravida lectus fringilla ut.' UNION ALL
SELECT 'Nunc vestibulum elit tincidunt velit pellentesque feugiat.' UNION ALL
SELECT 'Aliquam eu purus a neque fringilla porta.' UNION ALL
SELECT 'Fusce pretium justo vitae mauris tempor porttitor tincidunt orci laoreet.'

;with tally (N) as
(select row_number() over(order by (select null)) from master.sys.all_columns)

select id, max(left(textDescription,N-1)) as sub_string
from @temp1
cross apply (select Row_Number() over (order by N) as X, N
from tally
where substring(textDescription,N,1) = ' '
and N <= len(textDescription)
) ca
where ca.X <= @count
group by id


The Dixie Flatline
The Dixie Flatline
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5496 Visits: 6900
Thanks for the save, Allister. :-)

At one point that occurred to me, because the usual concatenation routine pads the string on the left and right with separator characters. But I forgot to test for all words on a line.

Having said that, I just realized that the code should really LTRIM each line, just in case a line starts with one or more spaces.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
The Dixie Flatline
The Dixie Flatline
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5496 Visits: 6900
test (10/22/2009)
Wow, that's complex! - Thanks


You're welcome, Test. Just make that code a user-defined function and the complexity goes away.

Better yet, make it an inline table-valued function. (Slightly more complex, but performs better for large batches).

Create a permanent tally table, and some of the complexity of the code goes away. All it really does is get a list of the positions that have spaces and uses the position of the fifth space as the ending position of the substring.

Let me know if you have any questions.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
santiaguero81
santiaguero81
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 0
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
create FUNCTION [dbo].fnSubstring_Index
(
@BaseString varchar(255),
@caracter varchar(255),
@pos tinyint
) RETURNS varchar(255)

AS
/* ****************************************************
Description:
EQuivalent a mysql substring_index---- ---- -----------
Created by Omar Rodriguez Tineo
**************************************************** */
BEGIN

/*
DECLARE @pos INT
Declare @BaseString varchar(255)
Declare @caracter varchar(255)
*/
Declare @indice tinyint
Declare @pos2 tinyint
Declare @result varchar(255)

set @pos2= 1
set @indice = 0
--set @BaseString='hola mudo sato bueno cinco seis siete'
--set @pos = 2
--set @caracter= ' '


while @indice < @pos
begin
begin


set @pos2 = CHARINDEX(@caracter,@BaseString,@pos2+1)
-- print @pos2

set @indice = @indice +1

end


if @indice = @pos
begin
set @result= left(@BaseString,@pos2)
--print @result
break
end
else
continue

end




RETURN @result
END
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search