How to remove white space characters from a string in SQL Server

Question

I m trying to remove white spaces from a string in SQL but LTRIM and RTRIM functions don t seem to work   Column    ProductAlternateKey   nvarchar  25  COLLATE Latin1 General CS AS NULL   Query   select REPLACE ProductAlternateKey                    LEN ProductAlternateKey          LTRIM RTRIM ProductAlternateKey        AS LRTrim         LEN LTRIM RTRIM ProductAlternateKey    AS LRLen         ASCII RIGHT ProductAlternateKey  1     AS ASCIIR         ASCII LEFT ProductAlternateKey  1      AS ASCIIL         ProductAlternateKey from DimProducts where ProductAlternateKey  like  46783815     Result      COLUMN 0   COLUMN 1   LRTrim   LRLen   ASCIIR   ASCIIL   PRODUCTALTERNATEKEY   ---------------------------------------------------------------------------------    46783815          8   46783815      8    53       52                46783815     46783815           10 46783815      10    10       52               46783815      Can it be other symbols if LTRIM and RTRIM are not working  like  Enter

User · Answer

How about this   CASE WHEN ProductAlternateKey is NOT NULL THEN CONVERT NVARCHAR 25   LTRIM RTRIM ProductAlternateKey     FROM DimProducts where ProductAlternateKey  like  46783815

User · Answer

There may be 2 spaces after the text  please confirm  You can use LTRIM and RTRIM functions also right   LTRIM RTRIM ProductAlternateKey     Maybe the extra space isn t ordinary spaces  ASCII 32  soft space   Maybe they are  hard space   ASCII 160   ltrim rtrim replace ProductAlternateKey  char 160   char 32

User · Answer

Looks like the invisible character -   ALT 255   Try this  select REPLACE ProductAlternateKey            --type ALT 255 instead of space for the second expression in REPLACE  from DimProducts where ProductAlternateKey  like  46783815     Raj  Edit  Based on ASCII   results  try ALT 10 - use numeric keypad

User · Answer

In that case  it isn t space that is in prefix suffix  The 1st row looks OK  Do the following for the contents of 2nd row   ASCII RIGHT ProductAlternateKey  1     and   ASCII LEFT ProductAlternateKey  1

User · Answer

Using ASCII RIGHT ProductAlternateKey  1   you can see that the right most character in row 2 is a Line Feed or Ascii Character 10   This can not be removed using the standard LTrim RTrim functions   You could however use  REPLACE ProductAlternateKey  CHAR 10        You may also want to account for carriage returns and tabs  These three  Line feeds  carriage returns and tabs  are the usual culprits and can be removed with the following    LTRIM RTRIM REPLACE REPLACE REPLACE ProductAlternateKey  CHAR 10        CHAR 13        CHAR 9           If you encounter any more  white space  characters that can t be removed with the above then try one or all of the below   --NULL Replace  YourString  CHAR 0       --Horizontal Tab Replace  YourString  CHAR 9       --Line Feed Replace  YourString  CHAR 10       --Vertical Tab Replace  YourString  CHAR 11       --Form Feed Replace  YourString  CHAR 12       --Carriage Return Replace  YourString  CHAR 13       --Column Break Replace  YourString  CHAR 14       --Non-breaking space Replace  YourString  CHAR 160         This list of potential white space characters could be used to create a function such as    Create Function  dbo   CleanAndTrimString     MyString as varchar Max   Returns varchar Max  As Begin     --NULL     Set  MyString   Replace  MyString CHAR 0           --Horizontal Tab     Set  MyString   Replace  MyString CHAR 9           --Line Feed     Set  MyString   Replace  MyString CHAR 10           --Vertical Tab     Set  MyString   Replace  MyString CHAR 11           --Form Feed     Set  MyString   Replace  MyString CHAR 12           --Carriage Return     Set  MyString   Replace  MyString CHAR 13           --Column Break     Set  MyString   Replace  MyString CHAR 14           --Non-breaking space     Set  MyString   Replace  MyString CHAR 160            Set  MyString   LTRIM RTRIM  MyString        Return  MyString End Go   Which you could then use as follows   Select      dbo CleanAndTrimString ProductAlternateKey  As ProductAlternateKey from DimProducts

User · Answer

Remove new line characters with SQL column data  Update a set  a CityName Rtrim Ltrim REPLACE REPLACE a CityName CHAR 10       CHAR 13          a postalZone Rtrim Ltrim REPLACE REPLACE a postalZone CHAR 10       CHAR 13           From tAddress a  inner Join  tEmployees p  on a AddressId  p addressId  Where p MigratedID is not null and p AddressId is not null AND  REPLACE REPLACE a postalZone CHAR 10   Y   CHAR 13   X   Like  Y   OR REPLACE REPLACE a CityName CHAR 10   Y   CHAR 13   X   Like  Y

[sql] How to remove white space characters from a string in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2008-r2