Removing whitespace in MSSQL and trailing spaces

Question

I'm trying to remove whitespace and trailing white space from right and left side of my string in DB.

Note how the current results looklike:

Note the string named:

*excellent-purchase*

When I fetch it in my C# application like this:

ctx.Users.ToList();

The output for this string that I get is:

\t*excellent-purchase*

I need to remove this "\t" sign from my C# application either on DB level or inside the C# application.

The way I've tried it is like doing it is like this:

UPDATE
    TableName
SET
    ColumnName = LTRIM(RTRIM(ColumnName))

But I still get this \t sign in my C# app...

How can I fix this?

Edit:

guys I still have a weird characther like this:

"nl_holyland*555*

And in the C# App it looks like:

\"nl_holyland*555*

Theres an extra \ with this solution like

\t isn't whitespace it's a tab. use `Replace(ColumnName, char(9), '')` in addition. http://stackoverflow.com/questions/12906949/how-eliminate-the-tab-space-in-the-column-in-sql-server-2008 — Danny Cullen, Jan 18 '17 at 15:22
You could also use [TrimStart](https://msdn.microsoft.com/en-us/library/system.string.trimstart(v=vs.110).aspx) — James Z, Jan 18 '17 at 15:24
If there are other characters, you could look at [RegEx](http://stackoverflow.com/questions/1046740/how-can-i-validate-a-string-to-only-allow-alphanumeric-characters-in-it) — SQLMason, Jan 18 '17 at 15:25
http://stackoverflow.com/questions/14211346/how-to-remove-white-space-characters-from-a-string-in-sql-server — Pரதீப், Jan 18 '17 at 15:30
Possible duplicate of [How to remove white space characters from a string in SQL Server](http://stackoverflow.com/questions/14211346/how-to-remove-white-space-characters-from-a-string-in-sql-server) — SQLMason, Jan 18 '17 at 15:37

score 1 · Answer 1 · answered Jan 18 '17 at 15:24

1

You can try it:

string value= Regex.Replace(value, @"\t|\n|\r", "");

answered Jan 18 '17 at 15:24

Junior Porfirio

Yes, it case use Replace(Replace(column,char(9),''), char(13),'') in SQL Server. – Junior Porfirio Jan 18 '17 at 15:31
Junior, I still get a \ sign left with this solution – User987 Jan 18 '17 at 15:56
Probably you are with caracter " in register. You can try replace Replace(Replace(column,char(9),''), char(34),''). – Junior Porfirio Jan 18 '17 at 16:48

score 1 · Answer 2 · answered Jan 18 '17 at 15:28

1

You might want to try the following:

UPDATE
    TableName
SET
    ColumnName = LTRIM(RTRIM(REPLACE(ColumnName,char(9),'')))

answered Jan 18 '17 at 15:28

Tyron78

I still have a weird characther like this: "nl_holyland*555* And in the C# App it looks like: \"nl_holyland*555* Theres an extra \ with this solution like – User987 Jan 18 '17 at 15:42
This is most certainly because " is a reserved character and has to be escaped, which is done via adding \ to the beginning. – Tyron78 Jan 18 '17 at 16:19

score 1 · Answer 3 · answered Jan 18 '17 at 15:29

1

You can use the Replace also:

UPDATE
    TableName
SET
    ColumnName =  REPLACE(ColumnName, ' ', '')

answered Jan 18 '17 at 15:29

Afnan Ahmad

3 Answers3