Search code examples
sql-serverssms-2012

Client database is using Text field, I'm using nvarchar

In relation to Need to convert Text field to Varchar temporarily so that I can pass to a stored procedure

I've tried the cast, but I still get a surviving CRLF that plays havoc with my system. There is no way I can alter anything on the client, I can't even CREATE FUNCTION. I am as such not meant to alter anything on the client side, so that is all good.

Is there a way for me to remove any and all CRLF, Tabulator and other likewise ascii codes from the text field as part of a SELECT FROM script?

As far as I'm allowed to inquire the database is a SQL Server 11.0.2100

Solution

  • If you think about smaller set of special characters to replace/delete, you can use nested REPLACE() in your SELECT:

    -- preparation of the example
CREATE TABLE #tt (id int identity (1,1), col text)
GO
INSERT INTO #tt (col) VALUES (N'this is a text
multiline
3rd line')
GO
-- run of the example
SELECT REPLACE(REPLACE(REPLACE(CAST(col as varchar(MAX)) ,
    CHAR(9), '<9>'),    -- replace '<9>' for '' to achieve removal
    CHAR(10), '<10>'),  
    CHAR(13), '<13>') AS NewText
    FROM #tt

--cleanup
--DROP TABLE #tt

    Output:

    (1 row(s) affected)
NewText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
this is a text<13><10>multiline<13><10>3rd line

(1 row(s) affected)