select 10 word - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

select 10 word

Author

Topic

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2006-09-06 : 06:34:52

hi

pls let me know how to select 10 word(both numeric and char etc)

1. i have round 3000 length(can increase) value which contains everything blankspace , 23; . etc
2. need to remove the blank space
3. i need to wrap the text then get 1 to 10 word then next 11 to 20
21 to 30 and so on words
eg:hiiamfineh
owruhopeur
fine.20060
9at10pm,iwi
llsenduth
edata56.

thanxs
india-bangalore

database

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-06 : 06:42:10

so what in the end you want???

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-06 : 06:49:02

This?

declare @word varchar(8000)

select @word = 'hi i am fine how r u hope u r fine. 200609 at10pm, i will send u the data 56.'

SELECT		1 + n.x partnumber,
		SUBSTRING(REPLACE(@Word, ' ', ''), 10 * n.x + 1, 10) parttext
FROM		(
			SELECT		b9.i + b8.i + b7.i + b6.i + b5.i + b4.i + b3.i + b2.i + b1.i + b0.i x
			FROM		(SELECT 0 i UNION ALL SELECT 1) b0
			CROSS JOIN	(SELECT 0 i UNION ALL SELECT 2) b1
			CROSS JOIN	(SELECT 0 i UNION ALL SELECT 4) b2
			CROSS JOIN	(SELECT 0 i UNION ALL SELECT 8) b3
			CROSS JOIN	(SELECT 0 i UNION ALL SELECT 16) b4
			CROSS JOIN	(SELECT 0 i UNION ALL SELECT 32) b5
			CROSS JOIN	(SELECT 0 i UNION ALL SELECT 64) b6
			CROSS JOIN	(SELECT 0 i UNION ALL SELECT 128) b7
			CROSS JOIN	(SELECT 0 i UNION ALL SELECT 256) b8
			CROSS JOIN	(SELECT 0 i UNION ALL SELECT 512) b9
			WHERE		b9.i + b8.i + b7.i + b6.i + b5.i + b4.i + b3.i + b2.i + b1.i + b0.i < 800
		) n
WHERE		10 * n.x < LEN(REPLACE(@Word, ' ', ''))
ORDER BY	n.x

Output is

1	hiiamfineh
2	owruhopeur
3	fine.20060
4	9at10pm,iw
5	illsenduth
6	edata56.

Peter Larsson
Helsingborg, Sweden

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2006-09-06 : 07:03:01

thanxs a lot
it works

database

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-06 : 07:06:56

Thank you, you're welcome!
See how easy that was when you gave us sample data and the expected output? Please do so in the future too.

How about give us feedback about all the other topics you have started?

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-06 : 07:37:31

Perter Good One

Just Applying your Query to my Function it will look like this ..

Peter, i hope its not the copyright version

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


Create Function SplitBasedOnNumber
(
	@Number int, 
	@Phrase varchar(8000)
)
Returns @Split Table
(
	[ID] Int ,
	InRows varchar(100)
)
As
Begin 
	DECLARE	@LastIndex SMALLINT,
		@NextIndex SMALLINT,
		@Value     Varchar(100)
	
	if (@Phrase is null) Or (@Phrase = '') or (@Number Is Null )
		return 
	
	Insert @Split 
	SELECT		1 + n.x partnumber,
	SUBSTRING(REPLACE(@Phrase, ' ', ''), @Number * n.x + 1, @Number) parttext
	FROM		(
			SELECT		b9.i + b8.i + b7.i + b6.i + b5.i + b4.i + b3.i + b2.i + b1.i + b0.i x
			FROM		(SELECT 0 i UNION ALL SELECT 1) b0
			CROSS JOIN	(SELECT 0 i UNION ALL SELECT 2) b1
			CROSS JOIN	(SELECT 0 i UNION ALL SELECT 4) b2
			CROSS JOIN	(SELECT 0 i UNION ALL SELECT 8) b3
			CROSS JOIN	(SELECT 0 i UNION ALL SELECT 16) b4
			CROSS JOIN	(SELECT 0 i UNION ALL SELECT 32) b5
			CROSS JOIN	(SELECT 0 i UNION ALL SELECT 64) b6
			CROSS JOIN	(SELECT 0 i UNION ALL SELECT 128) b7
			CROSS JOIN	(SELECT 0 i UNION ALL SELECT 256) b8
			CROSS JOIN	(SELECT 0 i UNION ALL SELECT 512) b9
			WHERE		b9.i + b8.i + b7.i + b6.i + b5.i + b4.i + b3.i + b2.i + b1.i + b0.i < 800
		) n
	WHERE		@Number * n.x < LEN(REPLACE(@Phrase, ' ', ''))
	ORDER BY	n.x	
return 
End 
GO 

Select * From dbo.SplitBasedOnNumber(10,'hi i am fine how r u hope u r fine. 200609 at10pm, i will send u the data 56')

--Output
ID          InRows                                                                                               
----------- ---------------------------------------------------------------------------------------------------- 
1           hiiamfineh
2           owruhopeur
3           fine.20060
4           9at10pm,iw
5           illsenduth
6           edata56

(6 row(s) affected)

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-06 : 07:45:09

I think the function you made in the linked topic is faster.

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-06 : 07:48:44

I Always had the perception that, while loop will reduce the speed?? is that so?

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-06 : 07:57:34

Chirag, I took the liberty to check your function for some bugs...

CREATE FUNCTION fnSplitBasedOnNumber
(
	@Number SMALLINT,
	@Phrase VARCHAR(8000)
)
RETURNS @Split	TABLE
		(
			RowID SMALLINT IDENTITY(1, 1),
			RowText VARCHAR(8000)
		)
AS

BEGIN
	DECLARE	@Index SMALLINT

	IF @Phrase IS NULL OR @Phrase = '' OR @Number IS NULL
		RETURN

	IF @Number <= 0 OR @Number >= DATALENGTH(@Phrase)
		BEGIN
			INSERT	@Split
				(
					RowText
				)
			SELECT	@Phrase

			RETURN
		END

	SELECT	@Index = 1

	WHILE @Index <= DATALENGTH(@Phrase)
		BEGIN
			INSERT	@Split
				(
					RowText
				)
			SELECT	SUBSTRING(@Phrase, @Index, @Number)

			SELECT	@Index = @Index + @Number		
		END

	RETURN
END

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-06 : 07:58:57

A cross join is costsome. It takes some time to create the cartesian product.
It can be effective with setbased data, but with string, a WHILE loop is faster overall.

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-06 : 08:14:05

Aha... Thanks for resolving the bugs.. :-)

Chirag

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-06 : 08:58:02

Also refer

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

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources