Trimming a custom character from the end of a T-Sql string
I recently had a situation where I needed to trim a custom character from the right of a string in Sql Server. Although Sql Server provides a RTRIM function, it can only trim spaces from a string.
A quick search over web threw up some interesting results, but all of them were buggy in one way or another. The major problem was that most of them assumed that spaces would not occur in the string itself.
So, I came up with the following solution as a UDF:
CREATE FUNCTION [dbo].[RTrimChar] ( @input nvarchar(MAX), @trimChar nchar(1) ) RETURNS nvarchar(MAX) AS BEGIN DECLARE @len int, @index int; set @len = LEN(@input); set @input = REVERSE(@input); set @index = PATINDEX('%[^' + @trimChar + ']%', @input); IF @index = 0 set @input = '' ELSE set @input = REVERSE(SUBSTRING(@input, @index, @len)); RETURN @input; END GO SELECT [CRC1].[dbo].[RTrimChar] ( 'Sample // String //', '/'); GO
I would like to know if anyone faces any issues with the above solution for trimming any custom charcater from the right of an Sql string. It can be modified easily to do the same from the left of a string.
Comments
Nisse (not verified)
September 6, 2016 - 11:01pm
Permalink
Hi, i tried to find what i am
Hi, i tried to find what i am looking for.
I have all kind of values 10522, 7310, 2430 normal 5 or 4 digits. I want to remove the thrid (3) digit from the right only. So it will be 1022, 710, 230.
How to do that i am using sql and visual basic
rahul
September 19, 2016 - 9:05pm
Permalink
1) Find string length, n
1) Find string length, n (standard methods/properties available in practically all platforms).
2) If string length, n > 3 (and assuming 0-based indexing), do a substring as follows:
substring(0, n - 3) + substring(n-2)
That's the high-level pseudo-code, you can translate to the language of your choice.
vju (not verified)
October 11, 2016 - 4:08pm
Permalink
Thank you
Thanks extremely handy. Will certainly share website with my pals