CODEWITHSUNDEEP
sqlsql-serverstring
Volker
Volker

Reputation: 465

SQL correct Uppercase and Lowercase in mixed text

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

Answers (2)

Venkataraman R
Venkataraman R

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

DeadLock
DeadLock

Reputation: 468

You could do one of two things -

  1. 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 advantage of this would be that you will have greater flexibility and control on what you want to modify and how you wish to do it.
    • For this solution to work, you may need to maintain a dict/hash in the script, having the mapping of upper-case to lower-case keyword mapping.

  2. 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.

    • This might be slightly inefficient, depending on how you implement the function. But it takes away the dependency of writing another script for you.
    • For this solution to work, you may need to maintain another table having the mapping of upper-case to lower-case keyword mapping.

Whichever you are more comfortable with.

Upvotes: 1

Related Questions