select/replace sql2000/2005 - 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 2005 Forums

Transact-SQL (2005)

select/replace sql2000/2005

Author

Topic

iminore
Posting Yak Master

141 Posts

Posted - 2009-10-30 : 08:10:33

This statement:

declare @kwds nvarchar(800)
set @kwds = '#'
select @kwds = @kwds + replace(replace(keyword,'#',''),'~','') + '#' from SubjectIndex S join Keywords K on S.keywordid = K.keywordid where S.titleid = 34 order by keyword
print(@kwds)

When run on SQL2000 it returns the correct result - five keywords, say.
However when run against the same database only the last keyword is returned. If I take away the replace functions then the five keywords are returned.
Is SQL2005 different to SQL2000 somewhere?

My word is my code.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-30 : 09:09:04

Remove order by and see

Madhivanan

Failing to plan is Planning to fail

iminore
Posting Yak Master

141 Posts

Posted - 2009-10-30 : 10:23:08

Mad - that did it. Not sure where that leaves me as I want them in keyword order.
SQL 2005 seems a bit iffy with ordering.
Thanks for the suggestion.

My word is my code.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-31 : 02:43:21

If you want ordering, try

declare @kwds nvarchar(800)
set @kwds = '#'
select @kwds = @kwds + replace(replace(keyword,'#',''),'~','') + '#' from
(select top 100 percent keyword from SubjectIndex S join Keywords K on S.keywordid = K.keywordid where S.titleid = 34 order by keyword) as t
print(@kwds)

Madhivanan

Failing to plan is Planning to fail

iminore
Posting Yak Master

141 Posts

Posted - 2009-11-03 : 04:46:51

Mad

Apologies for the delay, went walkabout.

Yes, that worked. SQL 2005 is evidently a little more picky than 2000.

I've actually changed my code. I was using '#' and '~' as separators and felt guilty using characters that could have been input. Now I use char(3) and char(4) and can sleep more easily.

Thanks again for your input.

My word is my code.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-03 : 04:50:32

quote:
Originally posted by iminore

Mad

Apologies for the delay, went walkabout.

Yes, that worked. SQL 2005 is evidently a little more picky than 2000.

I've actually changed my code. I was using '#' and '~' as separators and felt guilty using characters that could have been input. Now I use char(3) and char(4) and can sleep more easily.

Thanks again for your input.

My word is my code.

You are welcome
Read about Behavioural changes
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/10/12/behaviour-changes-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources