| Author |
 Topic  |
|
|
under2811
Constraint Violating Yak Guru
India
347 Posts |
 Posted - 06/15/2012 : 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 |
Edited by - under2811 on 06/15/2012 05:22:39
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 06/15/2012 : 00:19:01
|
where do you get CodeXX,CodeYY etc from?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
under2811
Constraint Violating Yak Guru
India
347 Posts |
Posted - 06/15/2012 : 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 |
Edited by - under2811 on 06/15/2012 05:23:02 |
|
|
|
under2811
Constraint Violating Yak Guru
India
347 Posts |
Posted - 06/15/2012 : 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 |
Edited by - under2811 on 06/15/2012 05:24:49 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 06/15/2012 : 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
India
347 Posts |
Posted - 06/18/2012 : 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
India
47069 Posts |
Posted - 06/18/2012 : 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/
|
|
|
| |
Topic |
|
STUFF update/Replace
