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 fnGetNumberOfWords (
@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(@stringToSplit))
Declare @index int

WHILE @wordcount < @numberOfWords AND len(@stringToSplit) > 0
BEGIN
Select @index = CHARINDEX(' ', @stringToSplit)
if @index = 0
BEGIN
SELECT @currentword = ltrim(rtrim(@stringToSplit))
SELECT @wordcount = @numberOfWords
END
else
BEGIN
IF (len(@stringToSplit) - @index > 0) BEGIN
SELECT @currentword = ltrim(rtrim(LEFT(@stringToSplit, @index-1)))--the new shortened string
SELECT @stringToSplit = RIGHT(@stringToSplit,LEN(@stringToSplit) - @index) -- the rest
END
END
SELECT @returnstring = @returnstring + ' ' + @currentword
SELECT @wordcount = @wordcount + 1
END

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

END


Call it like this:

SELECT dbo.fnGetNumberOfWords(MyField, 10) FROM mytable

[Ray Perea]

Here is the way I did it.

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