CODEWITHSUNDEEP
sqlsql-server
Irwin
Irwin

Reputation: 12829

Is there a more efficient way to handle these replace calls

I'm querying across two dbs separated by a legacy application. When the app encounters characters like, 'ü', '’', 'ó' they are replaced by a '?'.
So to match messages, I've been using a bunch of 'replace' calls like so:

(replace(replace(replace(replace(replace(replace(lower(substring([Content],1,153)) , '’', '?'),'ü','?'),'ó','?'), 'é','?'),'á','?'), 'ñ','?'))

Over a couple thousand records, this can (as you expect) is very slow. There is probably a better way to do this. Thanks for telling me what it is.

Upvotes: 0

Views: 180

Answers (4)

Pent Ploompuu
Pent Ploompuu

Reputation: 5414

You could create a persisted computed column on the same table where the [Content] column is. Alternatively, you can probably speed up the replace by creating a user defined function in C# using a StringBuilder. And you can even combine both of these solutions.

[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlString LegacyReplace(SqlString value)
{
    if(value.IsNull) return value;
    string s = value.Value;
    int l = Math.Min(s.Length, 153);
    var sb = new StringBuilder(s, 0, l, l);
    sb.Replace('’', '?');
    sb.Replace('ü', '?');
    // etc...
    return new SqlString(sb.ToString());
}

Upvotes: 0

Sylvia
Sylvia

Reputation: 2616

Could you convert the strings to varbinary before comparing? Something like the below:

declare 
   @Test varbinary (100)
   ,@Test2 varbinary (100)
select 
   @Test = convert(varbinary(100),'abcu')
   ,@Test2 = convert(varbinary(100),'abcü')

select 
   case
      when @Test <> @Test2 then 'NO MATCH'
      else 'MATCH'
   end

Upvotes: 0

Codewerks
Codewerks

Reputation: 5972

One thing you can do is implement a RegEx Replace function as a SQL assembly and call is as a user-defined function on your column instead of the Replace() calls. Could be faster. You also want to probably to the same RegEx Replace on your passed in query values. TSQL Regular Expression

Upvotes: 1

DVK
DVK

Reputation: 129481

Why not first do the same replace (chars to "?") on the string you are searching for in the app side using regular expressions? E.g. your SQL server query that was passed a raw string to search for and used these nested replace() calls will instead be passed a search string already containing "?"s by your app code.

Upvotes: 0

Related Questions