Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    268

    Unanswered: String Manipulation

    Hi All,

    I am trying to break the string that looks like this


    2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation.
    2007-05-06 07:36:21.28 server All rights reserved.
    2007-05-06 07:36:21.28 server Server Process ID is 292.

    into three separate strings to look like this

    col1 col2 col3
    2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation.
    2007-05-06 07:36:21.28 server All rights reserved.
    2007-05-06 07:36:21.28 server Server Process ID is 292.


    I was able to separate the above string into two columns, but can't figure out how to put the rest of the string into the third column.

    Any help is appreciated.

    Thanks.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    it helps if yuo use code tags

    Code:
    col1                                     col2                 col3
    2007-05-06 07:36:21.28         server              Copyright (C) 1988-2002 Microsoft Corporation.
    2007-05-06 07:36:21.28         server              All rights reserved.
    2007-05-06 07:36:21.28         server              Server Process ID is 292.


    Quote Originally Posted by inka
    Hi All,

    I am trying to break the string that looks like this


    2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation.
    2007-05-06 07:36:21.28 server All rights reserved.
    2007-05-06 07:36:21.28 server Server Process ID is 292.

    into three separate strings to look like this

    col1 col2 col3
    2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation.
    2007-05-06 07:36:21.28 server All rights reserved.
    2007-05-06 07:36:21.28 server Server Process ID is 292.


    I was able to separate the above string into two columns, but can't figure out how to put the rest of the string into the third column.

    Any help is appreciated.

    Thanks.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'd like to see what you did to split the first two columns

    Here's a hack, but it simplifies things

    Code:
    CREATE TABLE #myTemp99(Col1 varchar(8000))
    GO
    
    INSERT INTO #myTemp99(Col1)
    SELECT '2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation.'   UNION ALL
    SELECT '2007-05-06 07:36:21.28 server All rights reserved.' 				UNION ALL
    SELECT '2007-05-06 07:36:21.28 server Server Process ID is 292.'
    GO
    
    SELECT  '"'+SUBSTRING(Col1,1,POS2-1)+'"'		AS Col1
          , '"'+SUBSTRING(Col1,POS2+1,POS3-POS2-1)+'"'	AS Col2
          , '"'+SUBSTRING(Col1,POS3+1,LEN(Col1)-POS3)+'"'	AS Col3
      FROM (
    	   SELECT Col1
        		, CHARINDEX(' ',Col1) AS POS1
    		, CHARINDEX(' ',Col1,CHARINDEX(' ',Col1)+1) AS POS2
    		, CHARINDEX(' ',Col1,CHARINDEX(' ',Col1,CHARINDEX(' ',Col1)+1)+1) AS POS3
      	     FROM #myTemp99) AS XXX
    GO
    
    DROP TABLE #myTemp99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What?

    You don't like it?

    I was gonna write a udf, but I figured this would be fatser...I should do a comparison...anyone got any thoughts on which would be fatser?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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