asked on

MS SQL Server update on datacolumn type text

I have a table with a columns datatype TEXT

I am trying to run an update ...
If I do the select on it the data comes up

It just won't update.

UPDATE dbo.cmsEmailTemplates
SET Qualifications = replace(CAST(Qualifications AS VARCHAR(MAX)), '[NOTCOSTART]', '[QUALNOTCOSTART]')
WHERE Qualifications LIKE '%[NOTCOSTART]%'

ASKER CERTIFIED SOLUTION

Christopher Gordon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Deepak Chauhan

Do you see any result from below query ?

Select replace(CAST(Qualifications AS VARCHAR(MAX)), '[NOTCOSTART]', '[QUALNOTCOSTART]')
from dbo.cmsEmailTemplates
WHERE Qualifications LIKE '%[NOTCOSTART]%'

Scott Pletcher

Brackets are special chars in a LIKE pattern, so you need to escape them to find an actual bracket. Try this instead:

WHERE Qualifications LIKE '%\[NOTCOSTART\]%' ESCAPE '\'

The original code should match any text that has any 'A' or 'C' or 'N' or 'O' or 'R' or 'S' or 'T' in it, which is probably just about every line. It would likely take much, much longer to run.

Larry Brister

ASKER

Taking a look shortly guys

Vitor Montalvão

lrbrister, do you still need help with this question?

Larry Brister

ASKER

Hey guys...
So sorry for the late get back... was out of country.
Was out of town....
It appears that Christopher Gordon's answer meets my needs.

I tried to break it several ways but it continues to work fine..

Without objection... I will award him the points

Larry Brister

ASKER

Sure enough...
I was barely unpacked and they sent me ... not out of town...
But out of country
Sorry for late award.
No more traveling this year.