CODEWITHSUNDEEP
sqlsql-serversql-server-2005
user3876801
user3876801

Reputation: 243

How to Trim leading and trailing tab spaces in MSSQL query

I have data which has leading and trailing spaces in the string. when storing that data in database I want to trim the space in query itself before storing into DB.

Normal spaces are trimming properly with RTRIM and LTRIM function but if a string contains tab space,its not trimming the tab space from the input string.

Can anyone help me to get the string with trimmed with tab space from leading and trailing.

Upvotes: 16

Views: 23842

Answers (2)

Leo
Leo

Reputation: 64

Try this:

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 refactor it as a function. Note, it may works only above Sql Server 2008. You can replace CHAR(9) to any character you like to trim.

Upvotes: -1

morgb
morgb

Reputation: 2312

Replace the ASCII code for tab (9):

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

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

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

Upvotes: 21

Related Questions