CODEWITHSUNDEEP
sql-server
OnurBulbul
OnurBulbul

Reputation: 431

What is the best way of removing last character of string in sql?

I use the following, but is there any easy way for doing this ?

DECLARE @x VARCHAR(32) = 'uk,usa,germany,poland,';
SELECT SUBSTRING(@x,0, LEN(@x) - CHARINDEX(',', REVERSE(@x)) + 1);

the output is :

uk,usa,germany,poland

Thanks a lot.

Upvotes: 0

Views: 84

Answers (3)

Pranav Bilurkar
Pranav Bilurkar

Reputation: 965

Try this using STUFF:

DECLARE @x VARCHAR(32) = 'uk,usa,germany,poland,';
select STUFF(@x,LEN(@x),1,' ')

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44336

In order to avoid errors on empty strings, I suggest using SUBSTRING starting from character position 0 instead of LEFT. Using left with my test data would fail.

DECLARE @t table(col1 varchar(20))
INSERT @t values('abc,'),(''), ('Denmark,')

SELECT 
  SUBSTRING(col1, 0, len(col1)) 
FROM @t

Result:

abc
<empty row>
Denmark

Upvotes: 0

Rhys Jones
Rhys Jones

Reputation: 5518

select left(@x, len(@x) - 1)

Note that LEN does not include trailing spaces.

Upvotes: 2

Related Questions