Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MS SQL alternative to SUBSTRING_INDEX Expand / Collapse
Author
Message
Posted Wednesday, October 21, 2009 8:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 22, 2001 12:00 AM
Points: 2, 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



Post #806552
Posted Wednesday, October 21, 2009 10:21 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
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? -- Stephen Stills
Post #806652
Posted Wednesday, October 21, 2009 11:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 9, 2014 9:24 AM
Points: 186, Visits: 3,073
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
Post #806682
Posted Wednesday, October 21, 2009 12:48 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
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? -- Stephen Stills
Post #806729
Posted Thursday, October 22, 2009 2:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 22, 2001 12:00 AM
Points: 2, Visits: 1
Wow, that's complex! - Thanks


Post #806995
Posted Thursday, October 22, 2009 4:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 9, 2014 9:24 AM
Points: 186, Visits: 3,073
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

Post #807022
Posted Thursday, October 22, 2009 6:59 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
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? -- Stephen Stills
Post #807106
Posted Thursday, October 22, 2009 7:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
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? -- Stephen Stills
Post #807121
Posted Friday, December 9, 2011 6:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 9, 2011 6:10 AM
Points: 1, 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
Post #1219289
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse