Word replacement - best strategy? 1

[drlex]

Good day!

I've been tasked with formatting product descriptions from upper case to 'proper case' as part of a system transition. Whilst I can use the "proper" stored procedure (as cribbed from GMastros' post* on Lessthandot), it also changes part numbers (e.g. FGHR-12) or abbreviations (uPVC) which need to remain as-is.

Therefore, a dictionary approach appears more suitable, and to this extent, I have created a two-column word table from the descriptions of all words, being each word and its replacement.

On a Monday morning, I can't envisage a set approach, but was contemplating looping through the product table, and looping the dictionary table per entry. At 15K products and 17K words, this will be a 0.25G tests. Alternatively, only 4K of the 17K words need replacing, so that could be a mere 68M checks.

Any thoughts (or articles I should read) on how best to proceed?

thanks,
lex


*

http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/sql-server-proper-case-function)

soi là, soi carré

 

[bborissov]

One time job, or everyday one?


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[drlex]

One time job, bborissov, so time/efficiency not an issue.

soi là, soi carré

 

[bborissov]

That is why I asked

DECLARE @Word varchar(200) --or nvarchar depending of what type is word column in your dictionary file
DECLARE @ReplWord varchar(200) --the same as above

SELECT @Word     = MIN(WordField)   FROM YourDictionaryFile
SELECT @ReplWord = ReplaceWordField FROM YourDictionaryFile WHERE WordField = @Word
WHILE @Word IS NOT NULL
      BEGIN
          UPDATE YourTable SET Filed = REPLACE(Field, @Word,  @ReplWord)
          SET @Word        = SELECT MIN(WordField)  FROM YourDictionaryFile WHERE WordField > @Word 
          SELECT @ReplWord = ReplaceWordField       FROM YourDictionaryFile WHERE WordField = @Word
      END

Not tested!!!
Make sure you have a good backup first




Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[drlex]

Thanks, bborissov!
I think I'll need to add in some ' ' to ensure that words are not found within codes (e.g. 'The' in 'STHE45-1'), but looks good.
My set-based attempts are going nowhere...

soi là, soi carré