mysql first 10 words in a column

[mahir78]

I want to get first 10 words in a column in sql query. (by using space)

any help is appreciated

[vssp]

I hope this This queris will helpful for you

LENGTH(str)

Returns the length of the string str, measured in bytes. A multi-byte character counts as multiple bytes. This means that for a string containing five two-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.

mysql> SELECT LENGTH('text');
-> 4



mysql> SELECT LEFT('foobarbar ', 5);
-> 'fooba'


Thanks
Prabu

[phpmaet]

Hi,
let's solve this problem by PL-SQL query. I think, that is not possible to write a single query. To see the following example.

CREATE FUNCTION fnGetNumberOfWo rds (
@stringToSplit varchar(8000),
@numberOfWords int
)

RETURNS varchar(8000) AS

BEGIN

DECLARE @currentword varchar(8000)
DECLARE @returnstring varchar(8000)
DECLARE @wordcount int
SET @wordcount = 0
SET @returnstring = ''
SET @currentword = ''
SET @stringToSplit = ltrim(rtrim(@st ringToSplit))
Declare @index int

WHILE @wordcount < @numberOfWords AND len(@stringToSp lit) > 0
BEGIN
Select @index = CHARINDEX(' ', @stringToSplit)
if @index = 0
BEGIN
SELECT @currentword = ltrim(rtrim(@st ringToSplit))
SELECT @wordcount = @numberOfWords
END
else
BEGIN
IF (len(@stringToS plit) - @index > 0) BEGIN
SELECT @currentword = ltrim(rtrim(LEF T(@stringToSpli t, @index-1)))--the new shortened string
SELECT @stringToSplit = RIGHT(@stringTo Split,LEN(@stri ngToSplit) - @index) -- the rest
END
END
SELECT @returnstring = @returnstring + ' ' + @currentword
SELECT @wordcount = @wordcount + 1
END

SET @returnstring = LTRIM(@returnst ring)
RETURN @returnstring

END


Call it like this:

SELECT dbo.fnGetNumber OfWords(MyField , 10) FROM mytable

[Ray Perea]

Here is the way I did it.

SELECT SUBSTRING_INDEX (`field`, ' ', 10)