Thanks for info...

okay thanks for suggestions... I'll do some benchmarks.

What other info can I provide?

thx.

There are around 35 fields defined in the table.. 13 non-clustered indexes, and triggers on the table to boot..

I was hoping I could get away with the alter....

Thanks

Thanks for ALL who replied ... everyone you are awesome many great solutions are always supplied and great detail

behind the solutions and examples.

Many Thanks to you!!!!!!

This is doing exactly what I was looking for but when I ran it there are 2 more columns that need added

select 'PartNbr|#@|Machine|#@|CCode|#@|Cost

How can those be introduced into current code...

thanks!!!...

If I do a len(body) it tells me 194245

Thats where I'm struggling based on the sample data I sent not sure how to invoke STRING_SPLIT()

Thanks.

could you send a sample of that code?

Thx.

Sample data in field:

PartNbr|#@|Machine|#@|  ttxx23|#@|plc1|#@|  ttxx24|#@|plc2

So it looks like PartNbr|#@|Machine|   -- these are the fields

Then |  ttxx23| -- when it sees a space data starts

partnbr  Machine

ttxx23     plc1

ttxx24     plc2

any...

• This reply was modified 2 years, 11 months ago by Bruin.

select cast(replace(cast(body as nvarchar(max)),'???','?') as ntext)

from lookuptable2

where

description like '???%'

I used this template and can see that it did indeed did do the replace, but how do I capture individual fields...

Read the NTEXT into an NVARCHAR(MAX). --> done

It appears that they used 3 characters to delineate the data  -->       |++

Use a Tally table function to read each character. ...

I was able to convert field.  I copied table to a 2016 box.

STRING_SPLIT function is available in 2016, but would require nvarchar instead of ntext.

Looks like they are using more...

Not a standard delimiter ...

If I can find the delimiter any code I can use to parse the data?

I looked thru a lot of those articles and didn't see backup success\failure, and run duration within a P/S script.

Thanks