pick a random section from a string

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 2005 Forums

Transact-SQL (2005)

pick a random section from a string

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2010-05-14 : 02:51:21

Hi All,

i got a nvachar(max) field with words in it like:

house
car
dog
tree
kid
cat
kid
school
book
apple

Separated with vbcrlf

I want so select a couple of those words , just a random group out of it. that i specify the amount of words that i need.
if specify like 3, it would return:

cat
kid
school

or

kid
school
book

just random out of the string

Thanks a lot
Mike

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-14 : 03:04:03

[code]
declare @words nvarchar(max),
@delimiter varchar(10)

select @delimiter = char(13) + char(10)

select @words = 'hourse' + @delimiter + 'car' + @delimiter + 'dog' + @delimiter + 'tree' + @delimiter
+ 'kid' + @delimiter + 'school' + @delimiter + 'book' + @delimiter + 'apple'

select top (3) Data
from fnParseList(@delimiter, @words)
order by newid()
[/code]

KH
[spoiler]Time is always against us[/spoiler]

mike13
Posting Yak Master

219 Posts

Posted - 2010-05-14 : 04:28:21

Thanks, so if i understand right i need to change the top(3) to get more words.

will give it a try

thanks a lot

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-14 : 04:33:28

yes.

KH
[spoiler]Time is always against us[/spoiler]

mike13
Posting Yak Master

219 Posts

Posted - 2010-05-14 : 13:49:55

Hi,

Something not how i wanted, now it is giving each keywords as a different record.
I want to pick an amount of words from the string but still have it in 1 string.
Easy way to do that?

Thanks a lot,
Mike

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-14 : 22:52:27

Try this:
select d.Data
from table_name t
cross apply
(select top 3 data + ''
from fnParseList(@delimiter, t.string)
order by newid()
for xml path(''))d(data)

______________________
Mayazar Mori Ke Dane Keshast

mike13
Posting Yak Master

219 Posts

Posted - 2010-05-15 : 03:14:34

hi thanks,

can you implement it with this data please, i tried without success :-( - keep having trouble with the t and t.string

declare @words nvarchar(max),
@delimiter varchar(10)

select @delimiter = char(13) + char(10)

select @words = 'hourse' + @delimiter + 'car' + @delimiter + 'dog' + @delimiter + 'tree' + @delimiter
+ 'kid' + @delimiter + 'school' + @delimiter + 'book' + @delimiter + 'apple'

thanks a lot

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-05-15 : 08:09:14

Try this -

declare @words nvarchar(max),
@delimiter varchar(10),
@result nvarchar(max)
SET @result = ''

select @delimiter = char(13) + char(10)

select @words = 'hourse' + @delimiter + 'car' + @delimiter + 'dog' + @delimiter + 'tree' + @delimiter
+ 'kid' + @delimiter + 'school' + @delimiter + 'book' + @delimiter + 'apple'

select top 3 @result = @result + data + CHAR(13)
from fnParseList(@delimiter, @words)
order by newid()

SELECT @result
PRINT @result

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

mike13
Posting Yak Master

219 Posts

Posted - 2010-05-15 : 08:44:32

Figured it out, thanks a lot all

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources