getting complete word from a string in simple way

[uglyeyes]

Hi! I would like to get first 15 word from a long sentence but not sure how to do this. I guess I should be looking for 15th space in the word but not sure how to do this could some please help? thank you

[_Damian S_]

Looking for the 15th space won't help you, as there may be more than one space between words. What about punctuation? Do you need to take that into account? Basically, you need to parse the string, finding one word at a time (and adding them into a new string) until you reach the end of your 15th word... There are plenty of ways to do this. One simple way is to read the string one character at a time until you either reach the 15th word (at which point you have the answer you are after) or you reach the end of the string (at which point you can return some meaningful error such as 'string too short'). Have a crack at it and post again when you have some code to show us!!

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!

[uglyeyes]

If there is more than one space for e.g. 2 space I will count that in too so I will be printing only upto 14 words. could you please help. with below i can count number of words based on space. could you please give me one simple example as to how i can obtain without writing complex function. SELECT LEN(text) - LEN(REPLACE(text, ' ', '')) + 1 from table

[Luc Pattyn]

If you want to keep the beginning of a text, not necessarily 15 words, but as much as possible, not exceeding N characters, then you could search for the last space before position N, and keep everything up to that space. This[^] would give some clues then. :)

Luc Pattyn

---

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

---

[_Damian S_]

I think you are confusing words with characters... If you are after 15 characters, simply use the left function. select left(FIELDNAME, 15) from TABLENAME

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!

[uglyeyes]

Hi! I am after 15 words not characters. thanks

[Niladri_Biswas]

Try this

declare @str as varchar(100)
declare @startposition int
declare @endposition int
declare @stopingcondition int
declare @delimeter char(1)
set @startposition = 0
set @stopingcondition = 15
set @delimeter =''
set @str = 'I would like to get first 15 word from a long sentence but not sure how to do this. I guess I should be looking for 15th space in the word but not sure how to do this could some please help?' -- original data

--Program starts
;with num_cte as
(
select 1 as rn
union all
select rn +1 as rn
from num_cte
where rn <= len(@str)
)
, get_all_delimited_char_pos_cte as
(
select row_number()over(order by rn) cnt ,rn,chars
from num_cte
cross apply( select substring(@str,rn,1) AS chars) splittedchars
where chars = @delimeter
)
select @endposition = rn from get_all_delimited_char_pos_cte where cnt = @stopingcondition
select SUBSTRING(@str,@startposition,@endposition) as First15thWords

Output: First15thWords

I would like to get first 15 word from a long sentence but not sure

The approach: If you do a substring like

declare @str as varchar(100)
set @str = 'I would like to get first 15 word from a long sentence but not sure how to do this. I guess I should be looking for 15th space in the word but not sure how to do this could some please help?' -- original data

select SUBSTRING(@str,0,68)

you will get the first 15 words. So the challenge was to get the number 68(count by youself and you will find that) By using the get_all_delimited_char_pos_cte CTE and with the help of cross apply I am able to get the position of the delimited characters and from there I just picked up the position of the 15th occurrence of the delimited charecter Hope this helps :)

Niladri Biswas

modified on Wednesday, November 11, 2009 7:58 AM