-1

I have string for eg : 'test 123 Add '. I want to remove last 2 white space from string, the space count is dynamic. RTRIM() function is not working on my case. I am using the following query, but it removes only static value from last.

Left(mycolumndata,len(mycolumndata)-1)

Please suggest me best solution.

Zhorov
  • 28,486
  • 6
  • 27
  • 52
ankit tewari
  • 1
  • 2
  • 3
    What's wrong with `TRIM`/`LTRIM`/`RTRIM`? You say it "isn't working", what do you mean by this? Are there, perhaps *other* characters other than whitespace? Those functions, by default, only remove whitespace, not other characters. – Thom A Jul 19 '21 at 14:22
  • Does https://learn.microsoft.com/en-us/sql/t-sql/functions/trim-transact-sql?view=sql-server-ver15 answer your question? – Reporter Jul 19 '21 at 14:23
  • You'll want to link to the English documentation, @Reporter , not the German . – Thom A Jul 19 '21 at 14:24
  • @Larnu Yes, I didn't notice that google gave me the german version^^ I edited the link in my previous comment. – Reporter Jul 19 '21 at 14:25
  • Be aware - if you're *storing* these values in fixed-length columns (`(N)CHAR` instead of `(N)VARCHAR`), you always have the trailing spaces. – Damien_The_Unbeliever Jul 19 '21 at 14:27
  • Also, for the *single* value you have given, both `TRIM` and `RTRIM` work fine: [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=6fb0e0e009fa974e072872e3b4c76664). – Thom A Jul 19 '21 at 14:28
  • Odds are you only have some trailing control characters like CRLF or TAB. That said, this function may help https://stackoverflow.com/questions/43148767/sql-server-remove-all-non-printable-ascii-characters/43149139#43149139 – John Cappelletti Jul 19 '21 at 14:29
  • What you ask for is exactly what `rtrim` does - can you supply sample data that illustrates it not working? – Stu Jul 19 '21 at 14:31
  • @Damien_The_Unbeliever both `TRIM` and `RTRIM` convert the data type to a `varchar`/`nvarchar`. [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=679547a6c8d3192748965873d79e22c5) – Thom A Jul 19 '21 at 14:35
  • @Larnu - I was meaning in a context where e.g. they're running this as an `UPDATE` to try to clean the data - if it's being stored *back* into a `char` then it gains the trailing spaces back again. I have seen people trying to do this. – Damien_The_Unbeliever Jul 19 '21 at 14:36
  • Yes, that makes more sense , @Damien_The_Unbeliever . – Thom A Jul 19 '21 at 14:41
  • "`RTRIM()` function is not working on my case." Please note that `RTRIM` only removes regular spaces. If your string contains tabs, newlines, non-breaking spaces or other more exotic whitespace characters, then this may help: https://dba.stackexchange.com/questions/132966/trim-whitespace-spaces-tabs-newlines – Ruud Helderman Jul 19 '21 at 14:48

2 Answers2

0

This issue is likely that you're trailing whitespace includes things other than spaces. The most common culprits are Tabs (CHAR(9)), and like breaks CHAR(10) and CHAR(13). Using Ngrams8K you can easily identify the problem; consider this example:

DECLARE 
  @string VARCHAR(100) = 'test 123 Add
      '

I include a tab, a couple spaces and more. Using Ngrams8k we can do this:

SELECT ng.Position, ng.Token, [ascii] = ASCII(ng.Token)
FROM   dbo.ngrams8k(@string,1) AS ng;

Returns:

enter image description here

Here we can use TRIM.

THE MOST IMPORTANT THING TO KNOW ABOUT TRIM IS THAT IT IS NOT RTRIM(LTRIM( !!! TRIM is power-packed and is intended for handling more than whitespace; note this example from the documentation:

enter image description here

The culprit in this example are CHAR(9)'s (10)'s and (13)'s. Armed with this knowledge we can use TRIM like so.

DECLARE 
  @string VARCHAR(100) = 'test 123 Add
      ',
  @trim VARCHAR(10) = CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32);

SELECT TRIM(@trim FROM  @string) AS Result;

If you are not on 2019 you can always do this:

SELECT 
  SUBSTRING(@string,1,
    DATALENGTH(@string) - PATINDEX('%[A-Z]%',REVERSE(@String))+1);
Alan Burstein
  • 7,770
  • 1
  • 15
  • 18
0

Using a text editor like notepad++ or EmEditor, you can copy/paste the data you're having issues with, turn on show all characters, and figure out exactly what those characters are so you can account for them in your code. as Alan says above, the usual suspects are CHAR(10) and CHAR(13) (CR/LF). -D

Salahuddin Ahmed
  • 4,854
  • 4
  • 14
  • 35
dfaustine
  • 1
  • 2