| Author |
 Topic  |
|
|
oracle_corrgi
Yak Posting Veteran
India
98 Posts |
 Posted - 09/04/2006 : 08:58:53
|
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
29138 Posts |
Posted - 09/04/2006 : 09:02:30
|
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 |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/04/2006 : 09:04:45
|
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 |
|
|
|
chiragkhabaria
Flowing Fount of Yak Knowledge
India
1907 Posts |
Posted - 09/04/2006 : 09:24:35
|
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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/04/2006 : 09:47:03
|
Aha. Now I see.
Sorry for the confusion. Using a function like this will be the sameCREATE 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 |
|
|
|
chiragkhabaria
Flowing Fount of Yak Knowledge
India
1907 Posts |
Posted - 09/04/2006 : 10:51:55
|
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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
|
| |
Topic |
|
extract only 50 words
