STUFF update/Replace - 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 2008 Forums

Transact-SQL (2008)

STUFF update/Replace

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2012-06-15 : 00:08:08

Hi All,

I already posted my query onto "Analysis Server and Reporting Services (2008)" forum - addressing "Need to remove extra line space for RTF data." i thought if we can able to get that solution in T-SQL.
Is there any way to update/Replace the last record getting from STUFF query
like if i have few records like
ID | description
1 | Code1
1 | Code2
1 | Code3
2 | Code1
2 | Code4
Query is like below

select ID,Notes
from
(
select
B.ID,
STUFF((
select dbo.RTFtoPlainText(Description) from dbo.A where A.ID = B.ID
For XML Path('')
)
,1,1,'')
as Notes
from dbo.B
) p

-- dbo.RTFtoPlainText its function
Output::
1, Code1 Code2 Code3
2, Code1 Code4

But can i get like below

1, Code1 Code2 CodeYY
2, Code1 CodeYY

T.I.A

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-15 : 00:19:01

where do you get CodeXX,CodeYY etc from?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2012-06-15 : 00:24:44

Hi,

I am updating Code3 to CodeYY and also Code4 to CodeYY. (This is just for example)
In short if you saw my query on reporting side i am facing problem to remove extra line space (CHAR(10)) over there. So what I am thinking is that if it possible to get last record update then its easily to replace extra line space (CHAR(10)) by '' over there.

T.I.A

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2012-06-15 : 05:22:02

Hi

This is for remove extra line space at end of each record

SELECt * INTO #t
FROM
(
SELECT
TOP 100 PERCENT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID DESC) AS RNP
, dbo.dbo.RTFtoPlainText(Description) Notes
, ID
FROM dbo.A
ORDER BY ID
) MM

UPDATE #t
SET Notes = REPLACE(Notes,CHAR(10),'')
FROM #t INNER JOIN (SELECT MAX(RNP) RNP_MAX FROM #t GROUP BY ID) A
ON RNP = A.RNP_MAX

SELECT * FROM #t

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-15 : 16:12:50

for removing trailing spaces RTRIM() should be sufficient right? unless its hard space?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2012-06-18 : 01:45:21

Hi,

I have tried (TRIM function) that one also but as the text is in RTF format I am not able to TRIM it at right side. And yes you are right I think it is hard spcae at right side.

T.I.A

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-18 : 12:24:12

quote:
Originally posted by under2811

Hi,

I have tried (TRIM function) that one also but as the text is in RTF format I am not able to TRIM it at right side. And yes you are right I think it is hard spcae at right side.

T.I.A

if its hardspace you can use REPLACE

REPLACE(Column,CHAR(160),'')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources