Post reply

Parsing question in T-SQL ---- PLEASE HELP!!

  • April 18, 2006 at 9:40 pm

    #114279

    Hi there,

    I need to parse a database field with following condition:

    i have a variable length field with values like "0001254", "0092534A", "049475AB" etc.

    I have to parse the field in a way that it will exclude all the leading "0" and take everything after the "0". Can anyone please help me on how to parse this in the SQL server 2000? Do you think "CharIndex" would be helpful here? Any help would be greatly appreciated.

    Thanks a lot.

    Sincerely,

    Syed 

  • April 18, 2006 at 9:52 pm

    #632996

    Why you need it?

    Where you gonna use it?

    _____________
    Code for TallyGenerator

  • April 19, 2006 at 2:08 am

    #633001

    Hi Syed,

    I had a similar problem when bringing together account information from three different sources and combining them into one report, without leading zeros - don't you love it when different parts of the same company have differing views on what a customer account id is?

    I used PATINDEX, as you can match using wild cards so something like the following should help:

     

    select RIGHT(mytext, Len(mytext) - patindex('%[^0]%', mytext)+1 from mytable

     

    basically it finds the first non-zero, so to include it in the output using RIGHT you have to add the one on to where you want RIGHT to start from... As LEN - PATINDEX will give you a starting point of the character after the first non-zero!

    This won't be quick on a large dataset but I am assuming you have to clean the data before using it, so speed may not be that big a concern.

    We have to run the import over night as it's on 4.2 million rows, with about 10 differing rules and 10 other tables feeding specific customer/ department information! Roll on the integration into one system!

     

    Hope this helps,

    Rodney.

  • April 19, 2006 at 8:59 am

    #633073

    Rodney,

     

    thank you so much for your reply. I will try using the "patindex" in my code.

    I really appreciate your reply.

     

    Syed

  • April 19, 2006 at 9:24 am

    #633079

    Syed,

     

    Just realised I missed a closing bracket, one after the +1, should read:

    select RIGHT(mytext, Len(mytext) - patindex('%[^0]%', mytext)+1) from mytable

    Obviously I should have said replace "mytext" with you column name, and "mytable" with your table...

     

    Rodney.

  • April 19, 2006 at 5:12 pm

    #633157

    This may be a little faster... replace the #30 with the defined width of the column (or more)...

    SELECT SUBSTRING(somevarcharcol,PATINDEX('%[^0]%',@String),30)

      FROM yourtable

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

  • April 20, 2006 at 3:13 am

    #633192

    Hi Jeff,

    I did think of using SUBSTRING, but I can't remember know why I used RIGHT... I think it may have been because the account numbers are variable length, so I settled on RIGHT and LEN. Although thinking about it now, that wouldn't make a blind bit of difference!

    The SUBSTRING reads better, as it were, I was always annoyed by the +1 on the end!

    I ran both ways this morning on the full 4.2 million rows together with the execution plan on and it showed 50% for both methods!

    But as I said the SUBSTRING scans/ reads better so I think I'll use that in future as I can see me in the future looking at the way I did it and thinking why, what is it doing... even with my comments!

    Many thanks,

    Rodney (finally after 5 years of using this site I am no longer a newbie! I think!)

  • April 20, 2006 at 12:11 pm

    #633344

    Rodney and Jeff,

    Thank you so much for your inputs. This is really helpfull. I appreciate your responses.

    by the way, congrate Rodney on your status from "newbee" to author..

    I got so tied up with another project but I'm going to try your code in my SQL.

    Thanks again.

    - Syed

     

  • April 20, 2006 at 8:18 pm

    #633398

    ...and thank you all for the feedback.

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

Viewing 9 posts - 1 through 8 (of 8 total)

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