Need Help removing leading tab

  • 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

  • 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.

  • 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

  • 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)))

  • 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

  • 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.

  • 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

  • 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

     

  • 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.

  • 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • "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)

  • >>

    "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 ).

     

  • 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)

  • 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.

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

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