replacing [XFO=BM] in a column

Hi,
I have a table that contains text columns with data containing some tags
like [XFO=BM] in it.
I'd like to remove these and am using a view that contains calls to a
function to do this, but it doesn't work if the tags have square brackets
surrounding them.
i.e. set @.working = replace (@.working, '[XFO=DN]', '')
doesn't work.
Does anyone know how I can do this, or how I need to delimit the []s to make
the tags vanish?
Thanks in advance,
IanYou need to escape the opening bracket:
set @.working = replace (@.working, '[[]XFO=DN]', '')
ML
http://milambda.blogspot.com/|||Not sure i've understood correctly as the following works for me on SQL2k
DECLARE @.WORKING VARCHAR(50)
SET @.wORKING = '[XFO=DN]'
SELECT replace (@.working, '[XFO=DN]', '')
Does the problem lie in the function ...? Are you using LIKE anywhere ...?
From Books Online
Symbol
Meaning
LIKE '[ [ ]' [
HTH. Ryan
"Ian Jagger" <IanJagger@.discussions.microsoft.com> wrote in message
news:C3850ACE-88F8-437E-94C5-D5FCD83DAEE9@.microsoft.com...
> Hi,
> I have a table that contains text columns with data containing some tags
> like [XFO=BM] in it.
> I'd like to remove these and am using a view that contains calls to a
> function to do this, but it doesn't work if the tags have square brackets
> surrounding them.
> i.e. set @.working = replace (@.working, '[XFO=DN]', '')
> doesn't work.
> Does anyone know how I can do this, or how I need to delimit the []s to
> make
> the tags vanish?
> Thanks in advance,
> Ian|||
"ML" wrote:

> You need to escape the opening bracket:
> set @.working = replace (@.working, '[[]XFO=DN]', '')
Thanks for that, only unfortunately that didn't work. Delimiting both sets
of brackets didn't work either.
Any other ideas?
Ian|||Sorry, my bad. Ryan has a better answer. What was I thinking? No one knows.
ML
http://milambda.blogspot.com/|||
> Not sure i've understood correctly as the following works for me on SQL2k
> DECLARE @.WORKING VARCHAR(50)
> SET @.wORKING = '[XFO=DN]'
> SELECT replace (@.working, '[XFO=DN]', '')
> Does the problem lie in the function ...?
Hmmm, well the function although long is relatively straightforward...
ALTER FUNCTION convertPropertyTitle (@.inp text)
returns varchar (8000)
as
begin
if @.inp is null
return ' '
declare @.working varchar (8000)
set @.working = replace (cast (@.inp as varchar (8000)),
'{\rtf1\ansi\ansicpg1252\deff0\deflang10
33', '')
set @.working = replace (@.working, '{\rtf1\ansi\deff0{\fonttbl{\f0\fnil MS
Sans Serif;}}', '')
set @.working = replace (@.working,
'\rtf1\ansi\deff0{\fonttbl{\f0\fnil\fprq
2\fcharset0 Times New Roman;', '')
set @.working = replace (@.working, '{\fonttbl{\f0\fnil MS Sans Serif;}}', '')
set @.working = replace (@.working, '{\fonttbl{\f0\fnil MS Sans Serif;', '')
set @.working = replace (@.working, '{\f1\fnil\fcharset0 MS Sans Serif;}}', ''
)
set @.working = replace (@.working, '\fonttbl{\f0\fnil\fcharset0 MS Sans
Serif;}{\f1\fnil MS Sans Serif;', '')
set @.working = replace (@.working, '\fonttbl{\f0\fnil\fprq2\fcharset0 Times
New Roman;', '')
set @.working = replace (@.working, '\f1\fnil MS Sans Serif;', '')
set @.working = replace (@.working, '\viewkind4\uc1\pard\f0\fs16', '')
set @.working = replace (@.working, '\viewkind4\uc1\pard\b\f0\fs16', '<b>')
set @.working = replace (@.working, '\viewkind4\uc1\pard\lang1033\b\f0\fs16'
,
'<b>')
set @.working = replace (@.working, '\viewkind4\uc1\pard\ul\b\f0\fs16',
'<u><b>')
set @.working = replace (@.working, '\viewkind4\uc1\pard\ul\f0\fs16', '<u>')
set @.working = replace (@.working, '\viewkind4\uc1 d\i\fs16', '')
set @.working = replace (@.working, '\viewkind4\uc1\pard\i\f0\fs16', '<i>')
set @.working = replace (@.working, '\viewkind4\uc1\pard\ul\b\i\f0\fs16',
'<u><b><i>')
set @.working = replace (@.working, '\viewkind4\uc1\pard\b\i\f0\fs16', '<b><i>
')
set @.working = replace (@.working, '\viewkind4\uc1\pard\f0\fs24', '')
set @.working = replace (@.working, '\viewkind4\uc1\pard\lang2057\f0\fs20', ''
)
set @.working = replace (@.working, '\viewkind4\uc1\pard\f0\fs20', '')
set @.working = replace (@.working, '\viewkind4\uc1\pard\lang2057\b\f0\fs16'
,
'<b>')
set @.working = replace (@.working, '}', '')
set @.working = replace (@.working, '{', '')
set @.working = replace (@.working, ' ', '')
set @.working = replace (@.working, '\ulnone', '</u>')
set @.working = replace (@.working, '\ul', '<u>')
set @.working = replace (@.working, '\lang2057\b\f1', '<b>')
set @.working = replace (@.working, '\lang1033\b\f0', '<b>')
set @.working = replace (@.working, '\lang1033', '')
set @.working = replace (@.working, '\lang2057', '')
set @.working = replace (@.working, '\lang2057\fs20', '')
set @.working = replace (@.working, '\b0', '</b>')
set @.working = replace (@.working, '\b', '<b>')
set @.working = replace (@.working, '\i0', '</i>')
set @.working = replace (@.working, '\i', '<i>')
set @.working = replace (@.working, 'SIGNED
\f1''85''85''85''85''85''85''85'
'85''85''85''85''85''85''85''
85''85', '')
set @.working = replace (@.working,
'DATE''85''85''85''85''85''85''85
''85''85''85''85''85''85''85\
''85''85''85', '')
set @.working = replace (@.working, '\f0', '')
set @.working = replace (@.working, '\f1', '')
set @.working = replace (@.working, '\fs20', '')
set @.working = replace (@.working, '\fs16', '')
set @.working = replace (@.working, char (10), '')
set @.working = replace (@.working, char (13), '')
set @.working = replace (@.working, '\tab', '')
set @.working = replace (@.working, '\super', '')
set @.working = replace (@.working, '\nosupersub', '')
set @.working = replace (@.working, '[WAITPHOTO]', '')
set @.working = replace (@.working, '''a3', '￡')
set @.working = replace (@.working, '\par', '<BR>')
set @.working = replace (@.working, '[[]XFO=DN]', '')
set @.working = replace (@.working, '[[]XFO=BN]', '')
set @.working = replace (@.working, '[[]XFO=CN]', '')
set @.working = replace (@.working, '[[]XFO=BM]', '')
set @.working = replace (@.working, '[[]XFO=CM]', '')
return @.working
end

> Are you using LIKE anywhere ...?
I then do a
ALTER view propertiesProperty
as
select reference, dbo. convertPropertyTitle(PropertyAccommodati
on)
PropertyAccommodation, dbo. convertPropertyTitle(propertyfulldetails
)
propertyfulldetails, dbo.convertPropertyTitle(propertysituation)
propertysituation, dbo.convertPropertyTitle(propertytitle) propertytitle
from properties
then reference it as
select * from propertiesproperty
where reference = 'xxx3333'
So no likes anywhere.
Thanks,
Ian

> From Books Online
> Symbol
> Meaning
> LIKE '[ [ ]' [
>
> --
> HTH. Ryan
> "Ian Jagger" <IanJagger@.discussions.microsoft.com> wrote in message
> news:C3850ACE-88F8-437E-94C5-D5FCD83DAEE9@.microsoft.com...
>
>