keeping sting same size cuting of begining

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)

keeping sting same size cuting of begining

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2010-05-13 : 02:22:15

Hi All,

I got a Nvarchar(MAX) field that keeps adding text ex:

house
car
dog
tree
kid

what i want is when adding (updating record) a new word the first one is removed so that the string always have a fixed size (note: words are separated by VBCLRF line carrier feed)
so that new string would look like

car
dog
tree
kid
bike

Thanks a lot,

Mike

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-13 : 06:22:26

You should do that in your VB application and not in T-SQL if possible.

No, you're never too old to Yak'n'Roll if you're too young to die.

mike13
Posting Yak Master

219 Posts

Posted - 2010-05-13 : 06:57:37

the fields are huge, and would make it very heavy going back and forward with them. that's why i want to do it on the sql end

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-13 : 07:13:10

[code]declare @test nvarchar(max)
declare @newvalue nvarchar(50)
set @newvalue='bike'

set @test='house
car
dog
tree
kid'

select @test

set
@test=substring(@test,case when charindex(char(13)+char(10),@test)=0 then 1 else charindex(char(13)+char(10),@test)+2 end,len(@test))+char(13)+char(10)+@newvalue

select @test
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

mike13
Posting Yak Master

219 Posts

Posted - 2010-05-13 : 09:10:35

Hi Webfred,

Thanks for the example.
One question where can i say how many words the @test string can be?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-05-13 : 11:38:01

"number of words in sentance" = "number of spaces in sentance + 1"

mike13
Posting Yak Master

219 Posts

Posted - 2010-05-13 : 19:40:32