1

On running the below query:

SELECT DISTINCT [RULE], LEN([RULE]) FROM MYTBL WHERE [RULE] LIKE 'Trademarks, logos, slogans, company names, copyrighted material or brands of any third party%'

I am getting the output as: enter image description here

The column datatype is NCHAR(120) and the collation used is SQL_Latin1_General_CP1_CI_AS

The data is inserted with an extra leading space in the end. But using RTRIM function also I am not able to trim the extra space. I am not sure which type of leading space(encoded) is inserted here.

Can you please suggest some other alternative except RTRIM to get rid of extra white space at the end as the Column is NCHAR.

Below are the things which I have already tried:

  1. RTRIM(LTRIM(CAST([RULE] as VARCHAR(200))))
  2. RTRIM([RULE])

Update to Question Please download the Database from here TestDB

Please use below query for your reference:

SELECT DISTINCT [RULE], LEN([RULE]) FROM [TestDB].[BI].[DimRejectionReasonData]
WHERE  [RULE] 
LIKE 'Trademarks, logos, slogans, company names, copyrighted material or brands of any third party%'
RahulGo8u
  • 148
  • 2
  • 19
  • I suspect a control character like CR or TAB. Take a peek at https://stackoverflow.com/questions/42958278/sql-server-search-using-like-while-ignoring-blank-spaces/42958939#42958939 – John Cappelletti Mar 22 '18 at 12:38
  • 1
    A `(n)char` will always pad the right side of the column/variable out with whitespace. If you want the value to not have whitespace at the end then you'll need to use a `(n)varchar`. – Thom A Mar 22 '18 at 12:38
  • How about just comparing the first 92 characters? – Gordon Linoff Mar 22 '18 at 12:40
  • @Larnu, I cannot change the column datatype. But I have also tried to cast/convert [Rule] column in NVarchar(120). It is still containing the leading space. I am suspecting it is not just a simple whitespace at the end. How can I get rid of this extra space if it is not just a simple white space? – RahulGo8u Mar 22 '18 at 12:51
  • `(n)varchar` won't hide leading spaces, only trailing. In fact, it's it's a leading space, then changing to `varchar` won't do anything (sorry, I must have read leading as trailing >_< ). What does `UNICODE(LEFT([Rule],1))` return? – Thom A Mar 22 '18 at 12:57
  • @Larnu It is returning 84, Please download the database from the link. And use the query given in the last for your reference. – RahulGo8u Mar 22 '18 at 13:13
  • No chance I'm downloading an unknown file from someone anonymous. And 84 isn't a space. That's your problem. – Thom A Mar 22 '18 at 13:16
  • Can you give the result of `UNICODE(RIGHT([Rule],1))`, not `LEFT` – Kobi Mar 22 '18 at 13:19
  • @Kobi it is returning me 32 – RahulGo8u Mar 22 '18 at 13:21
  • 32 is the ASCII code for a space, you can replace it as I suggest in my answer. – Kobi Mar 22 '18 at 13:28
  • @Kobi It is **NChar** column. So Anyhow the rest of the space will have white spaces only. – RahulGo8u Mar 22 '18 at 13:40

3 Answers3

0

You may have a non-breaking space nchar(160) inside the string. You can convert it to a simple space and then use the usual trim function

LTRIM(RTRIM(REPLACE([RULE], NCHAR(160), ' ')))

In case of unicode space

LTRIM(RTRIM(REPLACE(RULE, NCHAR(0x00A0), ' ')))
Kobi
  • 2,494
  • 15
  • 30
0

I guess this is what you are looking for ( Not sure ) . Make a try with this approach

SELECT REPLACE(REPLACE([RULE], CHAR(13), ''), CHAR(10), '')

Reference links : Link 1 & Link 2

Note: FYI refer those links for better understanding .

Mahesh.K
  • 901
  • 6
  • 15
0

change the type nchar into varchar it will return the result without extra space

Chamin Thilakarathne
  • 347
  • 1
  • 6
  • 19