Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2009
    Posts
    11

    Unanswered: Parsing A String

    I have a field in a table the contains entries such as:

    12345Job0O
    12345Job1R
    12345Job2R
    12345Job3R
    12345Job4R
    12345Job5R
    12345Job6R
    12346Job0O
    12346Job1R
    12346Job2R

    For each 5 number grouping, I have to return the single record representing the highest revision number. In example above it would be Job123456R and Job123462R

    I have this, which is sort of working kinda...
    Code:
    SELECT field1, field2, field3, field4, 
    FROM table_name A,       
           (SELECT SUBSTRING(field1,1,5) as job_code,
           MAX(SUBSTRING(field1,9,1)) as revision_number
           From table_name
           --GROUP BY field1, field2) AS B
           GROUP BY SUBSTRING(field1,1,5) AS B
    where SUBSTRING(A.field1,1,5) = B.job_code
    AND SUBSTRING(A.field1,9,1) = B.revision_number
    GROUP BY  field1, field2, field3, field4
    What I get in return is:
    12345Job0O
    12345Job1R
    12345Job3R
    12345Job6R
    and
    12346Job0O
    12346Job2R

    I have a feeling I'm taking the wrong approach, but I'm not sure how else to do it.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What version of SQL Server are you using? A Common Table Expression may be the neatest way to do this, but they were not available until version 2005.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2009
    Posts
    11
    I honestly thought I had included the version...thats what I get for thinking.

    SQL Server 2000

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    declare	@Test table (TestString varchar(50))
    
    insert into @Test (TestString)
    select	'12345Job0O'
    union select '12345Job1R'
    union select '12345Job2R'
    union select '12345Job3R'
    union select '12345Job4R'
    union select '12345Job5R'
    union select '12345Job6R'
    union select '12346Job0O'
    union select '12346Job1R'
    union select '12346Job2R'
    
    select	LEFT(TestString, 5),
    		MAX(SUBSTRING(TestString,9,1))
    from	@Test
    group by LEFT(TestString, 5)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2009
    Posts
    11
    Thank you for your suggestion blindman, but I have almost 3,000 rows worth of these numbers. Hard coding them into UNION SELECTS doesn't seem practical.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The union and hard-coding is just to put together an example. It is a local custom in this forum. Makes it easier to play around with the sample data.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by Moe1950 View Post
    Thank you for your suggestion blindman, but I have almost 3,000 rows worth of these numbers. Hard coding them into UNION SELECTS doesn't seem practical.
    Do you know a better way to get some sample data into the @Test table var?

    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •