CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Dylan Czenski
Dylan Czenski

Reputation: 1375

SQL correct the misspelled word without modifying the original value

When I SELECT something FROM sometable there is a field that contains a misspelled word from the something column (category). How do I correct it without modifying the database table itself? For example, for each thing in something, if the value of the thing equals/contains sophiscate, select it as sophisticate.

Upvotes: 2

Views: 5470

Answers (3)

Jrud
Jrud

Reputation: 1004

SELECT REPLACE(ColName, 'Sophiscate', 'Sophisticate') AS ColName FROM SomeTable

You could also use a CASE statement if for some reason REPLACE won't work for you.

SELECT CASE WHEN ColName = 'Sophiscate' THEN 'Sophisticate' ELSE ColName END AS ColName FROM SomeTable

Upvotes: 5

Marcus D
Marcus D

Reputation: 1144

What you may be after is an MDM (Master Data Management) system, where you can have one or more tables, which store mappings between one value and another. You would have (e.g.) "Conformed" values mapping to "unconformed" values for a given data type or variable. So keeping your original data, you create in your output layer, you surface the conformed values in the data either on a view or a table, depending data size.

Microsoft's implementation of this is Master Data Services which does far more than I described above.

We use a home grown MDM to map changes or errors in data that we want to be mapped together.

It depends a little on your application / data / data topology, if this is an appropriate solution.

Upvotes: 2

sagi
sagi

Reputation: 40491

You can use REPLACE() function like this:

SELECT REPLACE(somthing,'sophiscate','sophisticate') 
FROM sometable

Upvotes: 4

Related Questions