1

I had asked a similar question earlier today but realized I had some flaws in my logic since I had made some assumptions.

What I'm trying to do is eliminate certain words from appearing in a string... I've created a table with some examples for ease

create TABLE #BrokerNameT (BrokerName varchar(100))

INSERT INTO #BrokerNameT (BrokerName)
VALUES ('Morgan Stanley Co Cash'),
       ('Citi Group Algo Cash'),
       ('JP Morgan Algo'), ('JP Morgan Cash')


SELECT 
   CASE WHEN BrokerName LIKE '%CASH%' 
          THEN RTRIM(LTRIM(SUBSTRING(BrokerName, 1, CHARINDEX('Cash', BrokerName)-1)))
        WHEN BrokerName LIKE '%ALGO%' 
          THEN RTRIM(LTRIM(SUBSTRING(BrokerName, 1, CHARINDEX('algo', BrokerName)-1)))
        WHEN BrokerName LIKE '%Prog%' 
          THEN RTRIM(LTRIM(SUBSTRING(BrokerName, 1, CHARINDEX('prog', BrokerName)-1)))
        WHEN BrokerName LIKE '%BSE%' 
          THEN RTRIM(LTRIM(SUBSTRING(BrokerName, 1, CHARINDEX('bse', BrokerName)-1)))
        WHEN BrokerName LIKE '%FX%' 
          THEN RTRIM(LTRIM(SUBSTRING(BrokerName, 1, CHARINDEX('fx', BrokerName)-1)))
        WHEN BrokerName LIKE '%OTC%' 
          THEN RTRIM(LTRIM(SUBSTRING(BrokerName, 1, CHARINDEX('otc', BrokerName)-1)))
        ELSE '' 
    END
FROM #BrokerNameT

drop table #BrokerNameT

As you can see, there are a list of words that I do not want to show up in my column. The problem with this query logic is that if it has two words unallowed: Lets say 'Citi Group Algo Cash' ; it will read the CASE logic when it finds CASH and still keep 'algo' -- if you run that query you will see what I mean...

Any ideas?

Improve this question

1 Answer 1

Reset to default
1

Have you tried:

create TABLE #BrokerNameT (BrokerName varchar(100))
INSERT INTO #BrokerNameT (BrokerName) VALUES('Morgan Stanley Co Cash')
INSERT INTO #BrokerNameT (BrokerName) VALUES('Citi Group Algo Cash')
INSERT INTO #BrokerNameT (BrokerName) VALUES('JP Morgan Algo')
INSERT INTO #BrokerNameT (BrokerName) VALUES('JP Morgan Cash')

select
         replace(replace(replace(replace(replace(replace(BrokerName,'CASH',''),'ALGO',''),'Prog',''),'bse',''),'FX',''),'OTC','')
from #BrokerNameT

drop table #BrokerNameT

Add a new replace for each word you don't want to have included.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I'm wondering if doing this will have performance issues.. It's very likely mine will too...
I have not had issues with this but that totally depends on the size of your record set. It does seem to give the exact results you are looking for from what I understood the question to be. Any string function like this has a performance cost.
Nested replace is surprisingly fast.
Yeah, I've never run into a problem with the nested replace.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.