Need Help removing leading tab

Question

Post reply

Need Help removing leading tab

demicoq

Old Hand

Points: 329
More actions
May 5, 2004 at 5:55 pm

#111225

Need some help on what to use to remove leading tabs from a string

declare @tabremove

set @tabremove = ' I dont want this tab'

Thanks in advance

demicoq

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

gljjr SSCrazy Eights Points: 9970 More actions · Answer 1

Take a look at the Right() function.

SELECT @tabremove = RIGHT(@stringWithTab, LEN(@strWithTab) - 1)

OR

SELECT @tabremove = RIGHT(@stringWithTab, LEN(@strWithTab) - LEN(chr(9)))

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

demicoq Old Hand Points: 329 More actions · Answer 2

Ended up finding out the solution. Use Ltrim, which works only for leading spaces but by using replace with an empty string. Ltrim is able to kick into action by finding the leading spaces.

ltrim( replace( <field>, chr(9), ' '))

Credit goes to

Michael Krolewski posting:

From: Mike Krolewski (mkrolewski@rii.com)

Subject: Re: Deleting Tabs From a string with PLSQL

View: Complete Thread (3 articles)

Original Format

Newsgroups: comp.databases.oracle.server

Date: 2001-01-29 15:53:43 PST

demicoq Old Hand Points: 329 More actions · Answer 3

demicoq

Old Hand

Points: 329

May 5, 2004 at 8:38 pm

#505391

This method works as well but I can't get it to work within a cursor. Any ideas on placement?

I get an error stating:

Invalid length parameter passed to the substring function.

SELECT @tabremove = RIGHT(@stringWithTab, LEN(@strWithTab) - LEN(chr(9)))

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

Kenneth Wilhelmsson

SSC-Dedicated

Points: 30043

May 6, 2004 at 4:35 am

#505441

You don't need either LTRIM nor RTRIM nor SUBSTRING.

All it takes is:

SELECT @stringWithTab = REPLACE(@stringWithTab, CHAR(9), '')

.. now @stringWithTab contains 'stringWithOutTab'

PS.

Invalid msg about substring is when the parameters gets negative.

DS.

/Kenneth

gljjr SSCrazy Eights Points: 9970 More actions · Answer 5

I chose not to use replace as there may be other places that have valid tabs. Replace is certainly good if you know the data only has the tab as the first character though!

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

Kenneth Wilhelmsson

SSC-Dedicated

Points: 30043

May 7, 2004 at 1:06 am

#505598

Ah, I see...

Though I can't for the life of me figure out why any tab would be valid in a datacolumn.

I never ever store unprintable chars as part of the data - gives me the chills, and tends to screw up practically everything big time..

/Kenneth

vadba SSChampion Points: 11133 More actions · Answer 7

Try:

SELECT @tabremove = RIGHT(@stringWithTab, LEN(@stringWithTab) - LEN(CHAR(9)))

To handle blank or null @stringWithTab values, try

SELECT @tabremove = CASE WHEN @stringWithTab = '' OR @stringWithTab IS NULL THEN @stringWithTab ELSE RIGHT(@stringWithTab, LEN(@stringWithTab) - LEN(char(9))) END

or this:

IF @stringWithTab <> '' AND @stringWithTab IS NULL

SELECT @tabremove = RIGHT(@stringWithTab, LEN(@stringWithTab) - LEN(char(9)))

Mike

gljjr SSCrazy Eights Points: 9970 More actions · Answer 8

We actually have a field where we store formatted XML. This usually has quite a few tabs in it. I was handed the db design when I got here. But overall it works pretty well.

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

ScottPletcher SSC Guru Points: 100262 More actions · Answer 9

I think you can remove leading tab(s) directly within a SELECT, without any CASE or loop, for example:

DECLARE @tabPattern VARCHAR(8)

SET @tabPattern = '%[^' + CHAR(9) + ']%'

DECLARE @tabRemove VARCHAR(200)

SET @tabremove = CHAR(9) + CHAR(9) + CHAR(9) + 'I dont want those tabs!'

SELECT @tabRemove

SELECT SUBSTRING(@tabRemove, PATINDEX(@tabPattern, @tabRemove), LEN(@tabRemove))

Naturally the same would work with a table column, just declare and set the tabPattern once before the SELECT

HTH

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

MacroTrenz Consulting SSC-Addicted Points: 405 More actions · Answer 10

"SELECT @tabremove = RIGHT(@stringWithTab, LEN(@strWithTab) - LEN(chr(9)))" . . . doesn't work because the "chr" function on the end should be "char".

you don't need to consider nulls and blanks because those same values will be returned.

i assume you want to remove all leading blanking and only the leading blanks. try this (when you cut-n-past, make sure the string does, in fact, have a tab):

DECLARE @tabremove NVARCHAR(50)

SET @tabremove = ' I dont want this tab'

WHILE CHARINDEX(CHAR(9),@tabremove) = 1

BEGIN

SET @tabremove = RIGHT(@tabremove,LEN(@tabremove)-1)

END

SELECT ISNULL(@tabremove,'IT''S NULL'),LEN(@tabremove)

vadba SSChampion Points: 11133 More actions · Answer 11

>>

"SELECT @tabremove = RIGHT(@stringWithTab, LEN(@strWithTab) - LEN(chr(9)))" . . . doesn't work because the "chr" function on the end should be "char".

>>

I showed the Char() correction in my previous example, as well as the other error, in which LEN(@strWithTab) should be LEN(@stringWithTab).

If @stringWithTab is blank or null, then Len(@stringWithTab) - Len(Char(9) will be -1. This will generate an error from the RIGHT() function. Even if the correct value is returned, it would poor programming practice not to check for these values and allow the error to be raised.

Server: Msg 536, Level 16, State 1, Line xxxx

Invalid length parameter passed to the substring function.

The WHILE as shown implicitly makes this check, since the loop body will not run if the string is null or blank ( CharIndex() will return 0 ).

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 12

Demicog,

To remove the first character of a column, no matter what the character is, try this...

SELECT SubString(columname,2,999) --Sub the "999" with the max width of the column

To remove ALL tabs from a column, try this...

SELECT REPLACE(columname,CHAR(9),'')

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

MacroTrenz Consulting SSC-Addicted Points: 405 More actions · Answer 13

mkeast,

the original issue was removing leading tabs. nulls and blanks have none. hence, like any other string without leading tabs, they are simply returned as is. my post was in response to demicoq and my comments related to the problem in general. your code is correct . . . my apologies if i seemed to imply otherwise.