CODEWITHSUNDEEP
t-sql
Jader Dias
Jader Dias

Reputation: 90475

How to remove invisible characters in t-sql?

I tried

UPDATE TABLENAME SET COLUMNNAME = REPLACE(COLUMNNAME, '\t', '')

But I don't know how to write the TAB in t-sql

Upvotes: 31

Views: 86404

Answers (7)

Joshylad
Joshylad

Reputation: 133

One of the comments I tried above was only reading the data and not actually updating the data, I had the best success with the following

UPDATE Tbl
SET Tbl.[ColumnName] = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(Tbl.[ColumnName], CHAR(9), ' '),CHAR(13), ' '),CHAR(10), ' ')))
FROM [TableName] AS Tbl

I know this is a old question hope this helps someone

Upvotes: 2

Eduardo Cuomo
Eduardo Cuomo

Reputation: 18937

For TAB and ENTER

SELECT
    -- TRIM AND REPLACE `TAB` AND `ENTER`
    LTRIM(RTRIM(
        REPLACE(
            REPLACE(
                REPLACE(columnname, CHAR(9), ' '),
            CHAR(13), ' '),
        CHAR(10), ' ')
    ))

Upvotes: 29

Mohammad Kani
Mohammad Kani

Reputation: 942

Checkout this function. This will remove every invalid char

-- =============================================
-- Author:      xenoivan
-- Description: clean invalid chars
-- =============================================
CREATE FUNCTION [dbo].[fnClean] 
(
    @in NVARCHAR(MAX)
)
RETURNS  NVARCHAR(MAX)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @out NVARCHAR(MAX)

    -- Add the T-SQL statements to compute the return value here
    SELECT @out = REPLACE(@in,N'َ','')
    SELECT @out = REPLACE(@out,char(9),'')
    SELECT @out = REPLACE(@out,char(13),'')
    SELECT @out = REPLACE(@out,char(10),'')
    SELECT @out = REPLACE(@out,N'‬','')
    SELECT @out = REPLACE(@out,N'‬','')
    SELECT @out = REPLACE(@out,N'‬‬','')
    SELECT @out = REPLACE(@out,N'‎', '')--its a hidden character
    SELECT @out = REPLACE(@out,N'‎', '')--ltr code
    SELECT @out = REPLACE(@out,N'‎', '')--rtl code
    SELECT @out = REPLACE(@out,N'۰', '0')
    SELECT @out = REPLACE(@out,N'۱', '1')
    SELECT @out = REPLACE(@out,N'۲', '2')
    SELECT @out = REPLACE(@out,N'۳', '3')
    SELECT @out = REPLACE(@out,N'۴', '4')
    SELECT @out = REPLACE(@out,N'۵', '5')
    SELECT @out = REPLACE(@out,N'۶', '6')
    SELECT @out = REPLACE(@out,N'۷', '7')
    SELECT @out = REPLACE(@out,N'۸', '8')
    SELECT @out = REPLACE(@out,N'۹', '9')
    SELECT @out = REPLACE(@out,N'٠', '0')
    SELECT @out = REPLACE(@out,N'١', '1')
    SELECT @out = REPLACE(@out,N'٢', '2')
    SELECT @out = REPLACE(@out,N'٣', '3')
    SELECT @out = REPLACE(@out,N'٤', '4')
    SELECT @out = REPLACE(@out,N'٥', '5')
    SELECT @out = REPLACE(@out,N'٦', '6')
    SELECT @out = REPLACE(@out,N'٧', '7')
    SELECT @out = REPLACE(@out,N'٨', '8')
    SELECT @out = REPLACE(@out,N'٩', '9');

    -- Return the result of the function
    RETURN @out

END

Upvotes: 3

Charles Bretana
Charles Bretana

Reputation: 146499

In the beginning of my TSql sProcs, I often put

   Declare @nl Char(2) = char(13) + char(10)
   Declare @tab Char(1) = char(9)
   etc...

Then you can use those declared variables anywhere in the rest of the proc without loss of clarity...

Upvotes: 15

Jader Dias
Jader Dias

Reputation: 90475

I found the solution:

In T-SQL you do not escape characters, you paste or type them directly into the quotes. It works even for \r\n (carriage return, new line = you press enter)

Upvotes: 3

Guffa
Guffa

Reputation: 700372

You can put a tab character in the string, just press the tab key.

That will work, but it's not very readable.

Upvotes: 4

Adrien
Adrien

Reputation: 3195

The ASCII code for tab is 9; you could try

update tablename set columnname = replace(columnname, char(9), '')

Upvotes: 47

Related Questions