Replace multiple items

[egodette]

Using the Replace command with a set.
I have a single column that I want to return partial values from.
Example
000270 KS EQUITY
123456 US EQUITY
345612 CORP CUSIP

The select statment should return
000270 KS (notice how I cant look for the first space)
123456
345612

right now I'm using a complicated CASE and REPLACE statement. I would like to use a set statement such as REPLACE(<field>,'EQUTIY' 'US EQUITY' 'CORP CUSIP','') instead of a seperate line for each one.
REPLACE(<field>,'EQUTIY','')
REPLACE(<field>,'US EQUTIY','')
REPLACE(<field>,'CORP CUSIP','')

Any ideas?

 

[r937]

REPLACE(REPLACE(REPLACE(<field>,'EQUTIY',''),'US EQUTIY',''),'CORP CUSIP','')

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[egodette]

still looking for a "set" answer. You have just nested the replace.

 

[gmmastros]

Here's a purely set based method, but I suspect it will be slower than the nested replaces...

This method assumes you have a table with all of the replaces that you are interested in. The code I show below creates a table variable with your sample data and another table variable for the "Replace" items.

Declare @Sample Table(Data VarChar(100))

Insert Into @Sample Values('000270 KS EQUITY')
Insert Into @Sample Values('123456 US EQUITY')
Insert Into @Sample Values('345612 CORP CUSIP')

Declare @Replaces Table(OriginalValue VarChar(100), ReplaceWith VarChar(100))

Insert Into @Replaces Values('EQUITY', '')
Insert Into @Replaces Values('US EQUITY', '')
Insert Into @Replaces Values('CORP CUSIP','')
Insert Into @Replaces Values('EQUTIY','') 
Insert Into @Replaces Values('US EQUTIY','') 
Insert Into @Replaces Values('CORP CUSIP','')

Select  Data, NewData
From    (
        Select *, 
               Replace(Data, OriginalValue, ReplaceWith) As NewData ,
               Row_Number() Over (Partition By Data Order BY Len(Replace(Data, OriginalValue, ReplaceWith))) As RowId
        From   @Sample S
               inner join @Replaces R
               On S.Data Like '%' + R.OriginalValue + '%'
        ) As A
Where	A.RowId = 1

The problem with this method is that there is an inner join based on a like condition, which is similar to a cross join, but with some rows removed. Basically, SQL Server will need to match each row from the sample table with each row from the Replace table and then determine if it matches the like criteria. On large tables, I would expect the performance to be a bit slow. However, based on the size of the tables involved, this may be acceptable. The only way to know for sure would be to test this against actual data.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MegaSQLBI]

Hi Gmmastros

This is amazing solution! Hats off to you.

Cheers!


Meganathan