How to trim start and end tabs in an MSSQL query

Question

I have data that has leading and trailing spaces in a string. when storing this data in the database, I want to trim the space in the query itself before storing it in the database.

Normal spaces are correctly trimmed using the RTRIM and LTRIM functions, but if the line contains a tab space, then it does not trim the tab space from the input line.

Can someone help me get a cropped string with a tab between leading and trailing.

Answer 1

Replace the ASCII code for the tab (9):

replace(@str, char(9), '') 

To remove only external tabs, first change them to something that will not exist in your data (in this example, I use a series of four spaces), then rtrim / ltrim, and then convert the same sequence back to tabs:

 replace(ltrim(rtrim(replace(@str, char(9), ' '))),' ', char(9)); 

Answer 2

Try the following:

 DECLARE @InputString nvarchar(50) = CHAR(9) + CHAR(9) + ' 123'+ 'abc ' + CHAR(9); SELECT @InputString AS InputString ,REVERSE(RIGHT(REVERSE(RIGHT(@InputString, LEN(@InputString) - PATINDEX('%[^'+CHAR(9)+']%', @InputString) + 1)), LEN(REVERSE(RIGHT(@InputString, LEN(@InputString) - PATINDEX('%[^'+CHAR(9)+']%', @InputString) + 1))) - PATINDEX('%[^'+CHAR(9)+']%', REVERSE(RIGHT(@InputString, LEN(@InputString) - PATINDEX('%[^'+CHAR(9)+']%', @InputString) + 1))) + 1)) AS OutputString ; 

Maybe you should reorganize it as a function. Please note that it can only work above Sql Server 2008. You can replace CHAR (9) with any character you like to trim.