0

I just stumbled across this gem in our code:

my $str_rep="lower(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(field,'-',''),'',''),'.',''),'_',''),'+',''),',',''),':',''),';',''),'/',''),'|',''),'\',''),'*',''),'~','')) like lower('%var%')";

I'm not really an expert in DB, but I have a hunch it can be rewritten in a more sane manner. Can it?

Dave Cross
  • 68,119
  • 3
  • 51
  • 97
ZzZombo
  • 1,082
  • 1
  • 11
  • 26
  • What language is this in? What database? etc. We need more information. – drekka Apr 29 '16 at 05:16
  • You might be able to use [`SIMILAR TO`](http://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-commons-predicates.html#fblangref25-commons-predsiimilarto) instead of all the replaces + like. – Mark Rotteveel Apr 29 '16 at 07:08

3 Answers3

1

Your flagged this as Perl, but it's probably not?

Here is a Perl solution anyway:

$var =~ s/[\-\.\_\+\,\:\;\/\|\\\*\~]+//g;
Sebastian
  • 2,472
  • 1
  • 18
  • 31
  • 1
    It's a part of a bigger SQL query, so your answer is incorrect. I'm Perl programmer, I would do something like that too, if it was the case. Thanks anyway. – ZzZombo Apr 29 '16 at 06:18
  • I though so, but you tagged it as Perl and that's why I wrote a Perl answer ;) – Sebastian Apr 29 '16 at 06:30
1

It depends on the DBMS you are using. I'll post some examples (feel free to edit this answer to add more).

MySQL

There is really not much to do; the only way to replace all the characters is nesting REPLACE functions as it has already been done in your code.

Oracle DB

Your clause can be rewritten by using the TRANSLATE function.

SQL Server

Like in MySQL there aren't any functions similar to Oracle's TRANSLATE. I have found some (much longer) alternatives in the answers to this question. In general, however, queries become very long. I don't see any real advantages of doing so, besides having a more structured query that can be easily extended.

Firebird

As suggested by Mark Rotteveel, you can use SIMILAR TO to rewrite the entire clause.

If you are allowed to build your query string via Perl you can also use a for loop against an array containing all the special characters.

EDIT: Sorry I did not see you indicated the DB in the tags. Consider only the last part of my answer.

Community
  • 1
  • 1
Luca Calabrese
  • 126
  • 1
  • 4
0

Sorry I don't know the languages concerned, but a couple of things come to mind.

Firstly you could look for a replace text function that does more that just a single character. Many languages have them. Some also do regular expression based find and replace.

Secondly the code looks like it is attempting to strip a specific list of characters. This list may not include all that is necessary which means a relatively high (pain in the butt) maintenance problem. A simpler solution might be to invert the problem and ask what characters do you want to keep? Inverting like this sometimes yields a simpler solution.

drekka
  • 20,957
  • 14
  • 79
  • 135