October 21, 2009 at 8:40 am
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
October 21, 2009 at 10:21 am
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
October 21, 2009 at 11:33 am
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
October 21, 2009 at 12:48 pm
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
October 22, 2009 at 2:33 am
Wow, that's complex! - Thanks
October 22, 2009 at 4:29 am
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
October 22, 2009 at 6:59 am
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
October 22, 2009 at 7:11 am
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
December 9, 2011 at 6:12 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply