Remove the last character in a string in T-SQL

Question

How do I remove the last character in a string in T-SQL   For example    TEST STRING    to return    TEST STRIN

User · Answer

you can create function  CREATE FUNCTION  dbo   TRUNCRIGHT    string NVARCHAR max    len int   1  RETURNS NVARCHAR max  AS BEGIN     IF LEN  string  lt  len         RETURN        RETURN LEFT  string  LEN  string  -  len  END

User · Answer

I can suggest this -hack-      select      left txt  abs len txt        - 2   from      t    SQL Server Fiddle Demo

User · Answer

If for some reason your column logic is complex  case when     then     else     end   then the above solutions causes you to have to repeat the same logic in the len   function   Duplicating the same logic becomes a mess   If this is the case then this is a solution worth noting   This example gets rid of the last unwanted comma   I finally found a use for the REVERSE function   select reverse stuff reverse  a b c d     1  1

User · Answer

If your string is empty    DECLARE  String VARCHAR 100  SET  String      SELECT LEFT  String  LEN  String  - 1    then this code will cause error message  Invalid length parameter passed to the substring function    You can handle it this way   SELECT LEFT  String  NULLIF LEN  String -1 -1     It will always return result  and NULL in case of empty string

User · Answer

declare  string varchar 20    TEST STRING  Select left  string  len  string -1  as Tada   output   Tada -------------------- TEST STRIN

User · Answer

Try this   select substring  test string   1   len  test string   - 1

User · Answer

If you want to do this in two steps  rather than the three of REVERSE-STUFF-REVERSE  you can have your list separator be one or two spaces  Then use RTRIM to trim the trailing spaces  and REPLACE to replace the double spaces with      select REPLACE RTRIM  a  b  c  d                   However  this is not a good idea if your original string can contain internal spaces   Not sure about performance  Each REVERSE creates a new copy of the string  but STUFF is a third faster than REPLACE   also see this

User · Answer

result   substring  result  1   LEN  result -1

User · Answer

If your coloumn is text and not varchar  then you can use this   SELECT SUBSTRING  String  1  NULLIF DATALENGTH  String -1 -1

User · Answer

Try this  DECLARE  String VARCHAR 100  SET  String    TEST STRING  SELECT LEFT  String  LEN  String  - 1  AS MyTrimmedColumn

User · Answer

e g   DECLARE  String VARCHAR 100  SET  String    TEST STRING   -- Chop off the end character SET  String         CASE  String WHEN null THEN null       ELSE            CASE LEN  String  WHEN 0 THEN  String              ELSE LEFT  String  LEN  String  - 1            END         END   SELECT  String

User · Answer

I love  bill-hoenig  s answer  however  I was using a subquery and I got caught up because the REVERSE function needed two sets of parentheses   Took me a while to figure that one out   SELECT    -- Return comma delimited list of all payment reasons for this Visit    REVERSE STUFF REVERSE           SELECT DISTINCT                CAST CONVERT varchar  r1 CodeID      -     c Name        AS VARCHAR MAX             FROM VisitReason r1           LEFT JOIN ReasonCode c        ON c ID   r1 ReasonCodeID          WHERE p ID   r1 PaymentID          FOR XML PATH                       1  2                              ReasonCode   FROM Payments p

User · Answer

select left  TEST STRING   len  TEST STRING  -1

User · Answer

This will work even when source text var is null or empty  SELECT REVERSE SUBSTRING REVERSE  a   2  9999

User · Answer

To update the record by trimming the last N characters of a particular column   UPDATE tablename SET columnName   LEFT columnName   LEN columnName  -N  where clause

User · Answer

Get the last character  Right  string  len  String  -  len  String  - 1

User · Answer

This is quite late  but interestingly never mentioned yet  select stuff x len x  1      ie  take a string x go to its last character remove one character add nothing

User · Answer

Try this   DECLARE  name NVARCHAR MAX  SET  name  xxxxTHAMIZHMANI     SELECT Substring  name  5   len  name -8   as UserNames   And the output will be like  THAMIZHMANI

User · Answer

Try It        DECLARE  String NVARCHAR 100      SET  String    12354851      SELECT LEFT  String  NULLIF LEN  String -1 -1

User · Answer

My answer is similar to the accepted answer  but it also check for Null and Empty String   DECLARE  String VARCHAR 100   SET  String    asdfsdf1   -- If string is null return null  else if string is empty return as it is  else chop off the end character SET  String   Case  String when null then null else  case LEN  String  when 0 then  String else LEFT  String  LEN  String  - 1  end   end  SELECT  String

User · Answer

declare  x varchar 20   y varchar 20  select  x  sam  select  case when  x is null then  y       when  y is null then  x       else  x      y end   go  declare  x varchar 20   y varchar 20  select  x  sam  --  y  john  DECLARE  listStr VARCHAR MAX      SELECT  listStr   COALESCE  x              coalesce  y         SELECT left  listStr len  listStr -1

[sql] Remove the last character in a string in T-SQL?

Examples related to sql

Examples related to sql-server

Examples related to tsql