How to trim a string in SQL Server before 2017

Question

In SQL Server 2017  you can use this syntax  but not in earlier versions   SELECT Name   TRIM Name  FROM dbo Customer

User · Answer

To Trim on the right  use   SELECT RTRIM Names  FROM Customer   To Trim on the left  use   SELECT LTRIM Names  FROM Customer   To Trim on the both sides  use   SELECT LTRIM RTRIM Names   FROM Customer

User · Answer

I assume this is a one-off data scrubbing exercise  Once done  ensure you add database constraints to prevent bad data in the future e g    ALTER TABLE Customer ADD    CONSTRAINT customer names  whitespace       CHECK                Names NOT LIKE                   AND Names NOT LIKE                   AND Names NOT LIKE                         Also consider disallowing other characters  tab  carriage return  line feed  etc  that may cause problems    It may also be a good time to split those Names into family name  first name  etc

User · Answer

To trim any set of characters from the beginning and end of a string  you can do the following code where  TrimPattern defines the characters to be trimmed  In this example  Space  tab  LF and CR characters are being trimmed  Declare  Test nvarchar 50    Concat       char 9   char 13   char 10         TEST        char 9   char 10   char 13        Test        char 9        char 9   char 13        DECLARE  TrimPattern nvarchar max             char 9    char 13    char 10        SELECT SUBSTRING  Test  PATINDEX  TrimPattern   Test   LEN  Test  - PATINDEX  TrimPattern   Test  - PATINDEX  TrimPattern  LTRIM REVERSE  Test      2

User · Answer

To trim any set of characters from the beginning and end of a string  you can do the following code where  TrimPattern defines the characters to be trimmed  In this example  Space  tab  LF and CR characters are being trimmed  Declare  Test nvarchar 50    Concat       char 9   char 13   char 10         TEST        char 9   char 10   char 13        Test        char 9        char 9   char 13        DECLARE  TrimPattern nvarchar max             char 9    char 13    char 10        SELECT SUBSTRING  Test  PATINDEX  TrimPattern   Test   LEN  Test  - PATINDEX  TrimPattern   Test  - PATINDEX  TrimPattern  LTRIM REVERSE  Test      2

User · Answer

SELECT LTRIM RTRIM Names   AS Names FROM Customer

User · Answer

I assume this is a one-off data scrubbing exercise  Once done  ensure you add database constraints to prevent bad data in the future e g    ALTER TABLE Customer ADD    CONSTRAINT customer names  whitespace       CHECK                Names NOT LIKE                   AND Names NOT LIKE                   AND Names NOT LIKE                         Also consider disallowing other characters  tab  carriage return  line feed  etc  that may cause problems    It may also be a good time to split those Names into family name  first name  etc

User · Answer

To Trim on the right  use   SELECT RTRIM Names  FROM Customer   To Trim on the left  use   SELECT LTRIM Names  FROM Customer   To Trim on the both sides  use   SELECT LTRIM RTRIM Names   FROM Customer

User · Answer

Extended version of  REPLACE    REPLACE REPLACE REPLACE REPLACE REPLACE RTRIM LTRIM REPLACE  Put in your Field name                        CHAR 9        CHAR 10        CHAR 13        CHAR 160        CorrValue

User · Answer

To Trim on the right  use   SELECT RTRIM Names  FROM Customer   To Trim on the left  use   SELECT LTRIM Names  FROM Customer   To Trim on the both sides  use   SELECT LTRIM RTRIM Names   FROM Customer

User · Answer

Extended version of  REPLACE    REPLACE REPLACE REPLACE REPLACE REPLACE RTRIM LTRIM REPLACE  Put in your Field name                        CHAR 9        CHAR 10        CHAR 13        CHAR 160        CorrValue

User · Answer

SELECT LTRIM RTRIM Names   AS Names FROM Customer

User · Answer

SELECT LTRIM RTRIM Replace Replace Replace name            CHAR 13        char 10          from author

User · Answer

in sql server 2008 r2 with ssis expression we have the trim function    SQL Server Integration Services  SSIS  is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks   you can find the complete description on this link  http   msdn microsoft com en-us library ms139947 aspx  but this function have some limitation in itself  which are also mentioned by msdn on that page  but this is in  sql server 2008 r2   TRIM     New York       The return result is  New York

User · Answer

SELECT LTRIM RTRIM Names   AS Names FROM Customer

User · Answer

SELECT LTRIM RTRIM Names   AS Names FROM Customer

User · Answer

SELECT LTRIM RTRIM Replace Replace Replace name            CHAR 13        char 10          from author

User · Answer

I assume this is a one-off data scrubbing exercise  Once done  ensure you add database constraints to prevent bad data in the future e g    ALTER TABLE Customer ADD    CONSTRAINT customer names  whitespace       CHECK                Names NOT LIKE                   AND Names NOT LIKE                   AND Names NOT LIKE                         Also consider disallowing other characters  tab  carriage return  line feed  etc  that may cause problems    It may also be a good time to split those Names into family name  first name  etc

User · Answer

To Trim on the right  use   SELECT RTRIM Names  FROM Customer   To Trim on the left  use   SELECT LTRIM Names  FROM Customer   To Trim on the both sides  use   SELECT LTRIM RTRIM Names   FROM Customer

User · Answer

in sql server 2008 r2 with ssis expression we have the trim function    SQL Server Integration Services  SSIS  is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks   you can find the complete description on this link  http   msdn microsoft com en-us library ms139947 aspx  but this function have some limitation in itself  which are also mentioned by msdn on that page  but this is in  sql server 2008 r2   TRIM     New York       The return result is  New York

User · Answer

I assume this is a one-off data scrubbing exercise  Once done  ensure you add database constraints to prevent bad data in the future e g    ALTER TABLE Customer ADD    CONSTRAINT customer names  whitespace       CHECK                Names NOT LIKE                   AND Names NOT LIKE                   AND Names NOT LIKE                         Also consider disallowing other characters  tab  carriage return  line feed  etc  that may cause problems    It may also be a good time to split those Names into family name  first name  etc

[sql] How to trim a string in SQL Server before 2017?

Examples related to sql

Examples related to sql-server

Examples related to trim