Reputation: 465
I have a database with lots of text that are all in capital letters and now should be converted into normal lower/upper case writing. This applies to technical stuff, for example
CIRCUIT BREAKER(D)480Y/277VAC,1.5A,2POL
This should be written circuit breaker(D)480Y/277VAC,1.5A,2POL
I think the only Approach is to have a list of word with the correct spelling and then do something like a Search & Replace.
Does anybody can give me a clue to Approach this in MS SQL?
Upvotes: 0
Views: 1158
Reputation: 13009
Create a mapping table for dictionary. It could be temporary table within a session. Load the table with values and use this temporary table for your update. If you want this to be permanant solution to handle new rows coming in, then have permanent table.
CREATE TABLE #dictionaryMap(sourceValue NVARCHAR(4000), targetValue NVARCHAR(4000));
CREATE TABLE #tableForUpdate(Term NVARCHAR(4000));
INSERT INTO #dictionaryMap
VALUES ('%BREAKER%','breaker'),('%CIRCUIT%','circuit');
INSERT INTO #tableForUpdate
VALUES ('CIRCUIT BREAKER(D)480Y/277VAC,1.5A,2POL');
Perform the UPDATE to #tableForUpdate
using WhileLoop in TSQL, using #dictionaryMap
.
Upvotes: 0
Reputation: 468
You could do one of two things -
Write a simple script (in whichever language you are comfortable - eg: php, perl, python etc.,) that reads the columns from the DB, does the case-conversion and updates the values back into the DB.
The second probable solution, if you do not wish to write a separate script, would be to create a SQL function instead, that reads the corresponding rows/columns that need to be updated, performs the case-conversion and writes/updates it back into the DB.
Whichever you are more comfortable with.
Upvotes: 1