How to get first n words from a sql server column that have saved a text?

Question

I think that my question is clear.

I have a column in my database table that is named Description with type nvarchar(MAX).
It saves a Persian text. I want to get for example first 100 words from this text.
What is your Idea?

Check this out http://stackoverflow.com/questions/697519/split-function-equivalent-in-tsql. **EDIT** First split your string with space and then get the first 100 words. — kostas ch., Jan 03 '14 at 13:58
You want 100 words? So split your field with space and get the fisrt 100 worlds. — kostas ch., Jan 03 '14 at 14:02
So check the example i have posted. There a function that splits text with any delimiter and then returns a table. So do that and get top 100 from function's table :) — kostas ch., Jan 03 '14 at 14:15
I suppose SQL server doesn't have any REGEX support. If it had this would have been easy enough. — Rajesh Paul, Jan 03 '14 at 14:30

Sunny · Answer 1 · 2014-01-03T17:46:11.830

this method breaks your string in words

-- ================================================
Create FUNCTION [dbo].[GetRequiredWordsFromString]
(
@string nvarchar(max),
@wordcount int
)
RETURNS nvarchar(max)
AS
BEGIN

    DECLARE @out nvarchar(max) declare @count int
    SET @out=''  SET @count=0
    DECLARE   @pos        int,
              @nextpos    int,
              @valuelen   int

 SELECT @pos = 0, @nextpos = 1

    WHILE (@nextpos > 0)
   BEGIN
       IF(@count=@wordcount)break;
      SELECT @nextpos = charindex(' ', @string, @pos + 1)
      SELECT @valuelen = CASE WHEN @nextpos > 0
                              THEN @nextpos
                              ELSE len(@string) + 1
                         END - @pos - 1
      SET @out+=(convert(nvarchar, substring(@string, @pos + 1, @valuelen)))+' '
      SELECT @count=@count+1
      SELECT @pos = @nextpos
   END

    RETURN @out

END
GO

and run this query on the table from where you want to get n words

   Declare @myDescripition table
(
Descp nvarchar(max)
)

declare @name nvarchar(max)
declare cur cursor local for select Name from Employees
open cur
fetch cur into @name
while (@@fetch_status=0)
BEGIN
   insert into @myDescripition 
   select [dbo].[GetRequiredWordsFromString](@name,100)
fetch cur into @name
END
Close cur
Deallocate cur

select * from @myDescripition

score 0 · Answer 2 · answered Jan 03 '14 at 14:58

Create this function:

create function f_getword
(
@txt varchar(max),
@pos int
)
returns varchar(max)
begin
declare @separators varchar(max) = '%[^a-z]%'
declare @returnword varchar(max)
;with x as
(
select patindex('%[a-z][^a-z]%', @txt + ' ') l, 1 cnt
union all
select patindex('%[a-z][^a-z]%', stuff(@txt + ' ', 1, l, '')) + l, cnt + 1
from x
where cnt < @pos and l is not null
)
select top 1 @returnword = left(@txt, l) from x order by cnt desc
option (MAXRECURSION 0)
return coalesce(@returnword, '')
end

Here is a table variable using the function:

declare @numberofwords int = 4
declare @t table(txt varchar(max))
insert @t values('here is a text'),
('here is another text, this part will be omitted'),
(''),
('text')

select dbo.f_getword(txt, @numberofwords)
from @t

Result:

here is a text
here is another text

text

score -1 · Answer 3 · answered Jan 03 '14 at 14:01

-1

Try this..

SubString(Column_Name,Start_Index,Length)
select SubString(Name,0,100) from Employee

answered Jan 03 '14 at 14:01

Sunny

219
1
10

2

Please be careful! I want to get words no letters. – Ali Sohrabi Jan 03 '14 at 14:11

How to get first n words from a sql server column that have saved a text?

3 Answers3