SELECT first 10 words from SQL Table

Question

I need select query for first 10 words from SQL Table Field.

I have field Name: Description, so i need first 10 words from description field.

EX: Description: I don't feel good about this answer; I don't like the GROUP BY and I would rather not do the table of numbers. Hopefully somebody can pick me up on this.

output: I don't feel good about this answer; I don't like

Answer 1

Another option is to use a CROSS APPLY and a little XML. The logic in the CROSS APPLY could easily be ported into a UDF (scalar or table-valued).

Example

Declare @YourTable table (ID int,LongDesc varchar(max))
Insert into @YourTable values
(1,'I don''t feel good about this answer; I don''t like the GROUP BY and I would rather not do the table of numbers. Hopefully somebody can pick me up on this.')

Select A.ID
      ,ShortDesc = B.S
 From  @YourTable A
 Cross Apply (
                Select S = xDim.value('/x[1]','varchar(100)')+' '
                          +xDim.value('/x[2]','varchar(100)')+' '
                          +xDim.value('/x[3]','varchar(100)')+' '
                          +xDim.value('/x[4]','varchar(100)')+' '
                          +xDim.value('/x[5]','varchar(100)')+' '
                          +xDim.value('/x[6]','varchar(100)')+' '
                          +xDim.value('/x[7]','varchar(100)')+' '
                          +xDim.value('/x[8]','varchar(100)')+' '
                          +xDim.value('/x[9]','varchar(100)')+' '
                          +xDim.value('/x[10]','varchar(100)')
                From  (Select Cast('<x>' + replace((Select replace(A.LongDesc,' ','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

Returns

ID  ShortDesc
1   I don't feel good about this answer; I don't like

Answer 2

Is the left() function ok?

select left(Description, 10) from your_table;

Answer 3

You can check this answer in order to learn how to implement and use .net functions in the context of SQL Server. Here, I am using SQLSCLR implementation of the of the .net Regex.Matchfunction.

DECLARE @DataSource TABLE
(
    [Description] NVARCHAR(MAX)
);

INSERT INTO @DataSource ([Description])
VALUES ('word01, word02,         word03, word04, word05, word06')
      ,('word01,word02, word03, word04, word05, word06')
      ,('word01!word02, word03: word04, word05, word06');

SELECT *
FROM @DataSource DS
CROSS APPLY [dbo].[fn_Utils_RegexMatches] ([Description], '^(\w+\b.*?){3}') RM;

This gives you the following output:

which is too detailed (and extraging only the first three words). Your final query can be something like this:

SELECT DS.[Description], RM.[CaptureValue]
FROM @DataSource DS
CROSS APPLY [dbo].[fn_Utils_RegexMatches] ([Description], '^(?n)(\w+\b.*?){3}') RM;

Anyway, using regex you can use the any separators you want, but the more important thing is you can execute .net code in the context of T-SQL which is huge.

You have a lot of to read and learn from here. The previous answer is more easy and faster to be implemented.

Answer 4

can you please try this

declare @message nvarchar(max) = 'I don''t feel good about this answer; I don''t like the GROUP BY and I would rather not do the table of numbers. Hopefully somebody can pick me up on this'

SELECT left(@message,
CHARINDEX(' ', @message,
  CHARINDEX(' ', @message,
    CHARINDEX(' ',@message,
     CHARINDEX(' ',@message,
      CHARINDEX(' ',@message,
       CHARINDEX(' ',@message,
        CHARINDEX(' ',@message,
         CHARINDEX(' ',@message,
          CHARINDEX(' ',@message,
            CHARINDEX(' ',@message) + 1 ) + 1 ) + 1 ) + 1) + 1) + 1) + 1 )+ 1 ) + 1))

Regards

Answer 5

try this with big data.

DECLARE @T TABLE(COL1 NVARCHAR(MAX))
INSERT INTO @T VALUES
('I dont feel good about this answer; I dont like the GROUP BY and I would rather not do the table of numbers. Hopefully somebody can pick me up on this.')

;WITH CTE AS
(
select col1,substring(col1,1,CHARINDEX(' ',col1))Words
,substring(col1,CHARINDEX(' ',col1)+1,len(col1))Residue
, 1 rn from @t

union ALL
select col1,substring(Residue,1,CHARINDEX(' ',Residue))
,substring(Residue,CHARINDEX(' ',Residue)+1,len(Residue))
,rn+1
from cte
where len(Words)>0 and rn<10
)
SELECT Words FROM cte

Answer 6

I know you already have an answer, but I just wanted to do it in a different way.

DECLARE @WORD VARCHAR(8000)= 'I don''t feel good about this answer; I don''t like the GROUP BY and I would rather not do the table of numbers. Hopefully somebody can pick me up on this.'
DECLARE @LOOP INT = 1
DECLARE @STR_POS INT = 1
DECLARE @CI INT
DECLARE @RES_STR VARCHAR(8000) = ''

WHILE(@LOOP<=10)
    BEGIN
        SELECT @CI = CHARINDEX(' ',@WORD,@STR_POS)
        SELECT @RES_STR = @RES_STR +' '+ SUBSTRING(@WORD,@STR_POS,(CHARINDEX(' ',@WORD,@STR_POS)-@STR_POS))
        SET @STR_POS = @CI+1
        SET @LOOP=@LOOP+1

    END
PRINT @RES_STR

Answer 7

This does not give you a row set solution, but another option to parse out the first ten words using a similar technique with less code, purely for illustration.. i suppose this can be converted to a user defined function, but we know they don't perform well. it can probably be combined using CrossApply to eliminate the need for a UDF. I must credit this for help with xml syntax: https://www.mssqltips.com/sqlservertip/1771/splitting-delimited-strings-using-xml-in-sql-server/

DECLARE 
@xml as xml,
@str as varchar(1000)= 'I don''t feel good about this answer; I don''t like the GROUP BY and I would rather not do the table of numbers. Hopefully somebody can pick me up on this.',
@delimiter as char(1) = char(32),
@xmlspace  as char(13) = '<y>&#160;</y>'

SET @xml = cast(('<x>'+replace(@str,@delimiter,@xmlspace)+'</x>') as xml)

SELECT TOP 10 N.value('.', 'varchar(50)') FROM @xml.nodes('x') as T(N)