Post reply

Parse string and re-format at same time

  • January 24, 2011 at 7:15 am

    #230828

    Hi,

    Could anyone kindly help me with the following problem I am having (SQLServer 2008)

    I have a table of data (TableTest)

    with the following structure

    ID int,

    oldCoords varchgar(max),

    newCoords varchar(max),

    The oldCoords column has data which I need to parse putting the result into the newCoords column for all rows in the table.

    A sample of oldCoords data is as follows...

    123.89;1400;107;-1.98;124.9001;103.67;12;-40;

    What I need would be the above formatted to 123.89 1400, 107 -1.98, 124.9001 103.67, 12 -40

    (basically a series of coordinate pairs with a space between the X and Y and a comma at the end of each pair [no comma on the end of the last pair]).

    I need to run the script as update to populate the new column in the table for up to 100000 rows.

    Any suggestions?

    Many thanks and kind regards.

    Charlotte.

  • January 24, 2011 at 8:09 am

    This was removed by the editor as SPAM

  • January 24, 2011 at 8:31 am

    #1277241

    stewartc-708166 (1/24/2011)

    Would something like the following help?

    DECLARE @StrStart NVARCHAR(MAX), @StrFin NVARCHAR(MAX) = '', @RecordCount INT = 0

    SELECT @StrStart = '123.89;1400;107;-1.98;124.9001;103.67;12;-40;'

    WHILE DATALENGTH(@StrStart) > 0

    BEGIN

    SELECT @StrFin += LEFT(@StrStart, CHARINDEX(';',@StrStart)-1),

    @StrStart = RIGHT(@StrStart, LEN(@StrStart) - CHARINDEX(';',@StrStart)),

    @RecordCount += 1

    SELECT @StrFin += CASE WHEN @RecordCount%2 = 0 AND DATALENGTH(@StrStart) > 0 THEN ', ' ELSE ' ' END

    END

    SELECT @StrFin

    stewartc,

    That appears to do what I need - many thanks for taking the time.

Viewing 3 posts - 1 through 2 (of 2 total)

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