May 1, 2015 at 10:48 am
I have a column A as 'text' datatype. This has CR-LF in between data.
How to remove it.
I am trying to copy result from SQL to Excel
Eg:
A CR LF
ABC CR LF
DVB CR LF
The output should be:
A
ABC DVB
May 1, 2015 at 11:25 am
PJ_SQL (5/1/2015)
I have a column A as 'text' datatype. This has CR-LF in between data.How to remove it.
I am trying to copy result from SQL to Excel
Eg:
A CR LF
ABC CR LF
DVB CR LF
The output should be:
A
ABC DVB
Quick suggestion, this fits your current requirements
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @CLF CHAR(2) = CHAR(13) + CHAR(10);
DECLARE @SAMPLE_TEXT VARCHAR(1024) = 'A' + @CLF + 'ABC' + @CLF + 'DVB' + @CLF
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
, NUMS(N) AS (SELECT TOP (LEN(@SAMPLE_TEXT)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4)
,BASE_DATA AS
(
SELECT
NM.N
,ROW_NUMBER() OVER
(
ORDER BY NM.N
) AS N_RID
FROM NUMS NM
WHERE NM.N = CHARINDEX(@CLF,@SAMPLE_TEXT,NM.N)
)
,PARSED_DATA AS
(
SELECT
SIGN(BD.N_RID - 1) AS T_ID
,CASE
WHEN BD.N_RID = 1 THEN SUBSTRING(@SAMPLE_TEXT,1,BD.N - 1)
ELSE REPLACE(SUBSTRING(@SAMPLE_TEXT,FIRST_VALUE(BD.N) OVER
(
ORDER BY BD.N
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) + LEN(@CLF),LEN(@SAMPLE_TEXT)),@CLF,CHAR(32))
END AS T_DATA
FROM BASE_DATA BD
)
SELECT
MAX(PD.T_DATA) AS T_DATA
FROM PARSED_DATA PD
GROUP BY PD.T_ID;
Results
T_DATA
--------
A
ABC DVB
May 1, 2015 at 12:22 pm
You should stop using the text datatype. It has been deprecated for a decade and is a serious pain to deal with. You have to cast/convert it to varchar(max) every time you want to use it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply