March 12, 2007 at 2:57 pm
Hi,
I am developing a SSIS Package and one of the Text field has values with Carriage Return and Line Feed {CR|LF}. I would like to replace this with '' or any semi colon. I can do that for any column but this is TEXT column and I cannot use the REPLACE function in the query. Is there any work around for this? Any suggestions will be appreciated.
I'm using SELECT REPLACE(col, 'CHAR(13)', '') and for CHAR(10) but throwing an error that I cannot use with TEXT field
Thanks in Advance - Mubeen
March 12, 2007 at 3:22 pm
I would think you'd be able to convert text to varchar(max) and do the replace operation on it.
March 13, 2007 at 2:42 pm
The REPLACE function will work if you use SUBSTRING to get the text data, or if you cast to varchar(max) as Aaron suggested.
SELECT
REPLACE(SUBSTRING(pr_info, 1, DATALENGTH(pr_info)), CHAR(13)+CHAR(10), '; ')
FROM pubs.dbo.pub_info
SELECT
REPLACE(CAST(pr_info AS VARCHAR(MAX)), CHAR(13)+CHAR(10), '; ')
FROM pubs.dbo.pub_info
March 14, 2007 at 2:05 pm
Thanks Scott and all who ever responded to my question.
It was successfull with the Replace function. Somehow it is not working with Char strings char(13)+char(10). It worked well with just CHAR(10) with out specifying combination of both. Thanks a lot for all your help - Mubeen
March 14, 2007 at 2:36 pm
Maybe you've got char(10)+char(13) instead, or some other goofy combination. You could take a look at the characters before and after the linefeed with something like this:
SELECT
pub_id, ASCII(SUBSTRING(pr_info, pos-3, 1)), ASCII(SUBSTRING(pr_info, pos-2, 1)), ASCII(SUBSTRING(pr_info, pos-1, 1)),
ASCII(SUBSTRING(pr_info, pos, 1)), ASCII(SUBSTRING(pr_info, pos+1, 1)), ASCII(SUBSTRING(pr_info, pos+2, 1)),
ASCII(SUBSTRING(pr_info, pos+3, 1))
FROM (
SELECT pub_id, pr_info, CHARINDEX(CHAR(10), pr_info) AS pos
FROM pubs.dbo.pub_info
WHERE pr_info LIKE '%' + CHAR(10) + '%') x
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply