CODEWITHSUNDEEP
sqlsql-serverssisderived
Jenny Sandar
Jenny Sandar

Reputation: 15

Derived Column FindString Error

I have a dataset as below

S.No Column_A                         Column_B

1    San Diego Sewage Department      Hello
2    California Sewage Plant          Hi
3    Paulo Alto Sewage                YOLO

Now I need to find and replace Sewage Department, Sewage Plant, Sewage to SD

So final result should look like

S.No Column_A                         Column_B

1    San Diego SD                     Hello
2    California SD                    Hi
3    Paulo Alto SD                    YOLO

I have tried writing like this in derived column but not all of them are getting converted

FINDSTRING(Column_A,"Sewage%",1)>0 ? REPLACE(Column_A,"Sewage%","") + "SD" : Column_A

Upvotes: 1

Views: 104

Answers (2)

GarethD
GarethD

Reputation: 69759

I think the issue is that you are using the SQL wild card in your functions, and as far as I know they don't accept wild cards.

I think the following will be equivalent:

FINDSTRING(Column_A,"Sewage",1) > 0 ? 
    SUBSTRING(Column_A, 1, FINDSTRING(Column_A,"Sewage",1) - 1) + "SD" : 
    Column_A

Upvotes: 2

Linger
Linger

Reputation: 15058

How about running the following three queries. You would have to make sure the last one is ran last:

UPDATE MyTable
SET column_A = REPLACE(column_A, 'Sewage Department', 'SD')

UPDATE MyTable
SET column_A = REPLACE(column_A, 'Sewage Plant', 'SD')

UPDATE MyTable
SET column_A = REPLACE(column_A, 'Sewage', 'SD')

Upvotes: 0

Related Questions