Replace Part Of String With Capitalised Equivalent From Cross Ref Temp Table - Tsql

We have an issue with instrument names loaded into our data warehouse where they either loaded with the string all in upper case or in normal case (capital at the beginning of each

Solution 1:

If you have your values that you want to use as a replacement in a table able you are going to perform this action repeatedly, then you could create a function to perform the replacement:

createfunction replacement(@stringvarchar(max))
returnsvarchar(max)
asbeginwith ReplaceWord(InternalWord, ExternalWord) as
    (
        select InternalValue, ExternalValue
        from capital
    )
    select@string=  REPLACE(@string, r.InternalWord, r.ExternalWord)
    from ReplaceWord r
    where CHARINDEX(r.InternalWord, @string) >0return@stringend

Then to query the data you can use:

SELECT dbo.replacement(i.Instrument) NewValue
FROM instrument AS i

See SQL Fiddle with Demo

Which will return:

|                                     NEWVALUE |
------------------------------------------------
|            Merck & Co INC Common Stock USD.5 |
| Newmont Mining CORP Common Stock USD INC 1.6 |

Note: I found the base code here from @SQL Kiwi and altered it to use a function if this is something you will have to do on a consistent basis

Solution 2:

I think I read all possible capitalizations wrong. Originally I read it as all posibilities of a single word. When you mean all rows in capital.

On my SQL 2008R2 the from is case insensitive

SELECT REPLACE('abCdefghicDE','cde','CDE');
returns
abCDEfghiCDE

I think your problem is going to be the reverse. How not to find all combinations.

SELECT REPLACE('corpo tunnel','Corp','CORP');
CORPo tunnel

My experience is the select / find side is always case insensitive in TSQL. I think you are going to need CLR to get the level of case sensitivity you need. If speed is an issue I would use .NET and read Capital into and Dictionary and process with Regex.