SQL replace for given pattern

Question

I have a text like following on a database field (SQL Server 2000)

"Led sledding leding led go led"

I want SQL Command to replace the word led to LED, But it shouldn't change words like "sledding" / "leding"

There are 15,000 records with similar text. Need to apply this for all of them.

I have tried following but it takes more than 24 hours. (With in a cursor)

update rprd 
set dsc = replace(dsc, 'led ', 'LED ')
where dsc not like 'LED %' collate sql_latin1_general_cp1_cs_as
and dsc like 'led %'

update rprd 
set dsc = replace(dsc, ' led ', ' LED ')
where dsc not like '% LED %' collate sql_latin1_general_cp1_cs_as
and dsc like '% led %'

update rprd 
set dsc = replace(dsc, ' led', ' LED')
where dsc not like '% LED' collate sql_latin1_general_cp1_cs_as
and dsc like '% led'

Please suggest me a faster and simple way of doing this.

Which database server are you using? The syntax tends to vary by vendor. — Craig Tullis
– Craig Tullis, Commented Jul 15, 2015 at 18:04

Craig Tullis · Accepted Answer · 2015-07-15 19:12:59Z

2

You didn't (edit: didn't initially) specify which database you are using.

Most database vendors have a function with general syntax like, or very close to REPLACE( source-string, from-string, to-string ), but the syntax will vary in terms of what kinds of wildcards you can use or whether you can use regular expressions, and case-sensitivity differs among vendors with regard to object names and string lookups. However, replacing a string with a string of a specific case will work with every vendor.

For your first pass, you might try something as simple as replacing ' led ' (led with a space on either side of it), like this:

REPLACE( somefield, ' led ', ' LED ' )

TSQL does support some modestly advanced wildcard searches: https://msdn.microsoft.com/en-us/library/ms179859.aspx

edited Jul 15, 2015 at 19:12

answered Jul 15, 2015 at 18:15

Craig Tullis

10.7k2 gold badges23 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

RoosIT · Accepted Answer · 2015-07-15 18:15:15Z

0

I take it, you just need to correct some existing data once, and you dont need a solution to use constantly.

so why dont you run a few queries that solve your problem easily and quickly,

UPDATE table SET field=regexp_replace(field, '^led ', 'LED ');
UPDATE table SET field=regexp_replace(field, ' led$', ' LED');
UPDATE table SET field=regexp_replace(field, ' led ', ' LED ');

make sure to check your db docs on correct syntax of your functions

answered Jul 15, 2015 at 18:15

RoosIT

1

1 Comment

Craig Tullis Over a year ago

SQL Server 2005 didn't support regular expressions.

Collectives™ on Stack Overflow

SQL replace for given pattern

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related