SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 extract only 50 words
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

oracle_corrgi
Yak Posting Veteran

India
98 Posts

Posted - 09/04/2006 :  08:58:53  Show Profile  Reply with Quote
hi

pls let me know how to get only 50 words from the data
if i have have 2000 word i need to get 1 to 50 ,51 to 100 ,101 to 150 and so on ,i dont know how much word is their in the data

thanxs
bangalore india


database

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 09/04/2006 :  09:02:30  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Maybe something like this?
CREATE PROCEDURE uspGetThatPage
(
	@PageNo INT,
	@RowInPage INT,
	@OrderBy VARCHAR(100)
)
AS

SET NOCOUNT ON

-- Get the Pk in the right order
DECLARE	@Items TABLE (ItemID INT IDENTITY(0, 1), PkColumn INT)

INSERT		@Items
		(
			PkColumn
		)
SELECT		PkColumn
FROM		YourTable
ORDER BY	CASE
			WHEN @OrderBy = 'RegionID' THEN RegionID
			WHEN @OrderBy = 'AddedOn' THEN AddedOn
			ELSE ItemName
		END

-- Now open the records for the right page
SELECT		yt.*
FROM		YourTable yt
INNER JOIN	@Items i ON i.PkColumn = yt.PkColumn
WHERE		i.ItemID / @RowsInPage = @PageNo - 1

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 09/04/2006 09:03:52
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 09/04/2006 :  09:04:45  Show Profile  Visit SwePeso's Homepage  Reply with Quote
And of course the @PageNo is the page number you want to fetch, @RowsInPage is the number of rows for each page (in your case 50) and the @OrderBy is a column to sort by.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 09/04/2006 :  09:24:35  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
Peter, are you sure thatz what the orginal poster wanted..???

What i understood, is that he want to break the a particular string in the batches of 50, and then want to display...?/ somthing like split function???

Chirag
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 09/04/2006 :  09:47:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Aha. Now I see.

Sorry for the confusion. Using a function like this will be the same
CREATE FUNCTION dbo.fnSplitDelimitedString
(
	@Text VARCHAR(8000),
	@Delimiter VARCHAR(8000)
)
RETURNS @Parts TABLE
	(
		i SMALLINT IDENTITY(0, 1) PRIMARY KEY CLUSTERED,
		Part VARCHAR(8000)
	)
AS

BEGIN
	DECLARE	@LastIndex SMALLINT,
		@NextIndex SMALLINT

	IF @Text IS NULL OR DATALENGTH(@Text) = 0
		RETURN

	IF @Delimiter IS NULL
		SELECT	@Delimiter = ';'

	SELECT	@LastIndex = 0,
		@NextIndex = 1

	WHILE @NextIndex > 0
		BEGIN
			SELECT	@NextIndex = CHARINDEX(@Delimiter, @Text, @LastIndex + 1)

			INSERT	@Parts
				(
					Part
				)
			SELECT	CASE
					WHEN @NextIndex = 0 THEN SUBSTRING(@Text, @LastIndex + 1, DATALENGTH(@Text) - @LastIndex)
					ELSE SUBSTRING(@Text, @LastIndex + 1, @NextIndex - @LastIndex - 1)
				END

			SELECT	@LastIndex = @NextIndex
		END

	RETURN
END


SELECT * FROM dbo.fnSplitDelimitedString('some data here', ' ')
WHERE i / 50 = @ThisBlock - 1


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 09/04/2006 :  10:51:55  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
Not sure but he wants somthing like this...

Create Function SplitBasedOnNumber
(
	@Number int, 
	@Phrase varchar(8000)
)
Returns @Split Table
(
	InRows varchar(100)
)
As
Begin 
	DECLARE	@LastIndex SMALLINT,
		@NextIndex SMALLINT,
		@Value     Varchar(100)
	
	if (@Phrase is null) Or (@Phrase = '') or (@Number Is Null )
		return 

	if (@Number = 0) or (@Number > Len(@Phrase) )
	Begin 
		Insert @Split 
		Select @Phrase 
		return 
	End 
	
	SELECT	@LastIndex = 0,@NextIndex = 0	
	while @NextIndex < Len(@Phrase)
	Begin 
		Select @value = Substring(@Phrase,@NextIndex,@Number)

		Insert @Split 
		Select @value 
		
		Set @NextIndex = @NextIndex + @Number 
		
	End 
	return 
End 
GO 

Select * From dbo.SplitBasedOnNumber(3,'Hi What up')


Chirag
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 09/06/2006 :  07:03:42  Show Profile  Visit SwePeso's Homepage  Reply with Quote
See also topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71569


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 09/06/2006 :  08:57:53  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
More methods on Split

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000