Post reply

String Parse

  • January 19, 2006 at 12:45 pm

    #113164

    Hello, Im trying to parse out the weight from a string for each record in the table. I keeping running into a wall and can't get what i need.

    Sample data:

    CARNATION ICE CREAM CHOCOLATE REGULAR REGULAR 1PT 1790000314

    GODIVA ICE CREAM PECAN CARAMEL TRUFLE REGULAR 0.7813PT 4154825833

    What im looking to do is extract the "1" and the "0.7813" only. I can't seem to get the position of the space before the weight. My combination of charindex and substring has left me with the incorrect positions. I also had no luck using  "reverse". Thanks in advance.

    Ryan

  • January 19, 2006 at 12:59 pm

    #615912

    Try this :

    DECLARE @Msg VARCHAR(200)

    SET @Msg = 'GODIVA ICE CREAM PECAN CARAMEL TRUFLE REGULAR 0.7813PT 4154825833'

    SELECT RIGHT(SUBSTRING(@Msg, 0, CHARINDEX('PT ', @Msg)), CHARINDEX(' ',REVERSE(SUBSTRING(@Msg, 0, CHARINDEX('PT ', @Msg)))) -1)

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • January 19, 2006 at 1:23 pm

    #615917

    Jacek0,

    Thanks for the quick post. Im not having any luck with your script as of yet. I think you have one too many parameters set because it returns 2 separate outputs. I'm trying to work off of what you have.

    Thanks again

  • January 19, 2006 at 1:40 pm

    #615923

    This is strange. I just copied and pasted the code from the post into the query analyzer and it run fine. If you have trouble getting it I can break it up so you can see all the steps that made into the long SELECT.

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • January 19, 2006 at 1:48 pm

    #615928

    DECLARE @Msg VARCHAR(200)

    SET @Msg = 'GODIVA ICE CREAM PECAN CARAMEL TRUFLE REGULAR 0.7813PT 4154825833'

    SELECT CHARINDEX('PT ', @Msg) -- this gets you the position of the PT

    SELECT SUBSTRING(@Msg, 0, CHARINDEX('PT ', @Msg)) -- this gets rid of the reminder of the string

    SELECT REVERSE(SUBSTRING(@Msg, 0, CHARINDEX('PT ', @Msg))) -- this flips it

    SELECT CHARINDEX(' ',REVERSE(SUBSTRING(@Msg, 0, CHARINDEX('PT ', @Msg)))) -- This finds the position of the last space ( first in the flipped string)

    SELECT CHARINDEX(' ',REVERSE(SUBSTRING(@Msg, 0, CHARINDEX('PT ', @Msg)))) - 1 -- This gets the length of the string you need.

    SELECT SUBSTRING(@Msg, 0, CHARINDEX('PT ', @Msg) -- this is the same string we got in the second step

    SELECT RIGHT(SUBSTRING(@Msg, 0, CHARINDEX('PT ', @Msg)), CHARINDEX(' ',REVERSE(SUBSTRING(@Msg, 0, CHARINDEX('PT ', @Msg)))) -1) -- gets what you need. (I hope)

     

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • January 19, 2006 at 1:58 pm

    #615929

    >>SELECT CHARINDEX('PT ', @Msg) -- this gets you the position of the PT

    Careful

    Product Name= 'ICE CREAM FOR THE INEPT STRING PARSER FUNCTION 55PT 767565' 

    (That's tongue in cheek by the way, not suggesting any ineptitude on your part, it happened to be the only word I could think of ending in "PT")

    The problem here is that 2 examples aren't sufficient, we need full specs as to what can be in a product string. Is the 1st number in the string always the weight, or may other parts of the name contain a digit ?

    Is the Weight always the item after the 1st space if the string is reversed ?

     

  • January 19, 2006 at 2:11 pm

    #615932

    Of course I made some assumptions on the data. I could not come up with anything that ends in PT and you put in the ice cream.

    And you right, working from the other end of the string might be safer.

     

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • January 19, 2006 at 2:33 pm

    #615936

    The weight always ends in PT and is always before the UPC.

    so for 3 dummy examples:

    PTPTPTPTPTPT 5.5PT 1234566

    PT PT PTP P T 0.7PT 12345656

    PTPTP PPP TTT PTP 4PT 123456

  • January 19, 2006 at 2:35 pm

    #615938

    >>The weight always ends in PT and is always before the UPC.

    Is there ever anything after the UPC ? Is the UPC always a number with no spaces ?

     

  • January 19, 2006 at 2:49 pm

    #615943

    Nope, the upc is always last. And its always a number with no spaces.

  • January 19, 2006 at 3:37 pm

    #615950

    Try this; it seems to work with the following selected sample data:

    declare @sql varchar(1000), @sql2 varchar(1000)

    --select @sql = 'ICE CREAM FOR THE INEPT STRING PARSER FUNCTION 55PT 767565'

    --select @sql = 'CARNATION ICE CREAM CHOCOLATE REGULAR REGULAR 1PT 1790000314'

    select @sql = 'GODIVA ICE CREAM PECAN CARAMEL TRUFLE REGULAR 0.7813PT 4154825833'

    select @sql2 = left(@sql,PATINDEX('%[0-9]PT %', @sql))

    select right(@sql2,charindex(' ',reverse(@sql2))-1)

  • January 20, 2006 at 4:01 am

    #616011

    An alternative, which might be faster if you have a lot of data:

     

    declare @test-2 table (str varchar(100))

    insert @test-2 select 'ICE CREAM FOR THE INEPT STRING PARSER FUNCTION 55PT 767565'

    insert @test-2 select 'CARNATION ICE CREAM CHOCOLATE REGULAR REGULAR 1PT 1790000314'

    insert @test-2 select 'GODIVA ICE CREAM PECAN CARAMEL TRUFLE REGULAR 0.7813PT 4154825833'

    select substring(dt.str, dt.ind1 + 1, dt.ind2 - dt.ind1 - 3)

    from

    (

      select T.str, max(case when substring(T.str, N.n - 1, 2) = 'T ' then 0 else N.n end) as ind1, max(N.n) as ind2

      from @test-2 T cross join Numbers N

      where N.n <= len(T.str) and substring(T.str, N.n, 1) = ' '

      group by T.str

    )

    dt

     

    This uses a Numbers table, which can be created as follows:

    SELECT TOP 10000 n = IDENTITY(INT, 1, 1) INTO Numbers

    FROM

        sysobjects a1

      CROSS JOIN

        sysobjects a2

      CROSS JOIN

        sysobjects a3

    ALTER TABLE Numbers

            ADD CONSTRAINT Index_Numbers PRIMARY KEY CLUSTERED(n)

    GO

  • January 20, 2006 at 6:14 am

    #616037

    And here another solution, using REVERSE and SUBSTRING you mentione as problematic (solution with Numbers table is probably more efficient and I'd go for it, if it is possible)

    declare @text varchar(1024), @from int, @length int

    set @text = 'GODIVA ICE CREAM PECAN CARAMEL TRUFLE REGULAR 0.7813PT 4154825833'

    select @from = charindex(' TP', reverse(@text))+3

    select @length = charindex(' ', reverse(@text),charindex(' TP', reverse(@text))+3) - @from

    select reverse(substring(reverse(@text), @from, @length))

    cheers, Vladan

  • January 20, 2006 at 6:53 am

    #616047

    or

    STUFF(STUFF([string],PATINDEX('%[0-9]PT%',[string])+1,255,''),1,PATINDEX('% [.0-9]%PT%',[string]),'')

     

    Far away is close at hand in the images of elsewhere.
    Anon.

  • January 20, 2006 at 7:05 am

    #616055

    Paul Mu,

    Your solution works best for me. Thanks for everyone's input!

     

    Ryan

Viewing 15 posts - 1 through 15 (of 19 total)

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