how perform replace on string w/ reserved words

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how perform replace on string w/ reserved words

Author

Topic

rhino1130
Starting Member

1 Post

Posted - 2007-11-27 : 11:58:11

I have a table that contains 20K+ records that contain the text "[see below]" or similiar text that contains the reserved bracket characters. How can I do a mass replace removing this section from my string?

I have tried different approaches using Char(), variable replacement, contactinating the string but nothing works. I still get all records in the select test.

Doesn't work:
LIKE '%' + Char(91) + 'see below' + char(93) + '%'

Here is my ideal structure for a replacement:

UPDATE Table1
SET MessageFld = LEFT(MessageFld, CHARINDEX('[', MessageFld) -1) + RIGHT(MessageFld, LEN(MessageFld) - CHARINDEX(']' ,MessageFld) -1)
WHERE MessageFld LIKE '%[%see below%]%'

Thanks.

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-11-27 : 12:40:22

It's covered in books online:
Using Wildcard Characters As Literals

You can use the wildcard pattern matching characters as literal characters. To use a wildcard character as a literal character, enclose the wildcard character in brackets. The following table shows several examples of using the LIKE keyword and the [ ] wildcard characters.
--------------------------------------

For example:


UPDATE Table1
SET MessageFld = LEFT(MessageFld, CHARINDEX('[', MessageFld) -1) + RIGHT(MessageFld, LEN(MessageFld) - CHARINDEX(']' ,MessageFld) -1)
WHERE MessageFld LIKE '%[[]%see below%]%'

Future guru in the making.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2007-11-27 : 13:17:27

Does your database column contain multiple occurrances of these 'reserved words'? If so, you will also need some recursion. I wrote a function some time in the past to do this (back in our SQL 2000 days):


/****** Object:  UserDefinedFunction [dbo].[StripPattern]    Script Date: 11/27/2007 13:14:17 ******/
/*
This procedure takes in a pattern match parameter (see syntax for PATINDEX)
and remove any characters that do not adhere to that pattern.
*/
CREATE FUNCTION [dbo].[StripPattern]
(
    @Input VARCHAR(8000),
    @Pattern VARCHAR(100)
)
RETURNS VARCHAR(8000)
AS
BEGIN
    WHILE PATINDEX(@Pattern, @Input) != 0
        BEGIN
    	    SET @Input = REPLACE(@Input, SUBSTRING(@Input, PATINDEX(@Pattern, @Input), 1), '')
        END

    RETURN @Input
END

This utilized the pattern matching functionality of the PATINDEX function and can easily be enhanced to accept another input parameter to represent the replacement string (I hardcoded '' since the intent was to strip out bad data).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources