How to truncate string using SQL server

Question

i have large string in SQL Server  I want to truncate that string to 10 or 15 character  Original string  this is test string  this is test string  this is test string  this is test string    Desired string  this is test string  this is

User · Answer

You can use  LEFT column  length    or  SUBSTRING column  start index  length

User · Answer

You can also use the Cast   operation     Declare  name varchar 100   set  name         Select Cast  name as varchar 10   as new name

User · Answer

If you only want to return a few characters of your long string  you can use   select    left col  15          col from yourtable   See SQL Fiddle with Demo   This will return the first 15 characters of the string and then concatenates the     to the end of it   If you want to to make sure than strings less than 15 do not get the     then you can use   select    case      when len col  gt  15     then left col  15               else col end col from yourtable   See SQL Fiddle with Demo

User · Answer

CASE      WHEN col IS NULL         THEN         ELSE SUBSTRING col 1 15               END AS Col

User · Answer

You could also use the below  the iif avoids the case statement and only adds ellipses when required  only good in SQL Server 2012 and later  and the case statement is more ANSI compliant  but more verbose   SELECT    col  LEN col      col2  LEN col2      col3  LEN col3  FROM     SELECT      col       LEFT x col  15     IIF len x col   gt  15              AS col2       LEFT x col  15     CASE WHEN len x col   gt  15 THEN       ELSE    END  AS col3    from         select  this is a long string  One that is longer than 15 characters  as col       UNION        SELECT  short string  AS col       UNION        SELECT  string  15 char  AS col       UNION        SELECT NULL AS col       UNION        SELECT    AS col   x   y

User · Answer

I think the answers here are great  but I would like to add a scenario   Several times I ve wanted to take a certain amount of characters off the front of a string  without worrying about it s length  There are several ways of doing this with RIGHT   and SUBSTRING    but they all need to know the length of the string which can sometimes slow things down   I ve use the STUFF   function instead   SET  Result   STUFF  Result  1   LengthToRemove        This replaces the length of unneeded string with an empty string

[sql-server] How to truncate string using SQL server

Examples related to sql-server

Examples related to tsql