1

I have a string like "www.mysite.com" in $site variable. In MySQL the Permitted characters in unquoted identifiers are (more info: https://dev.mysql.com/doc/refman/5.7/en/identifiers.html):

  • ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore)
  • Extended: U+0080 .. U+FFFF

However for me now would be enough to do this regular expression: 's/[^0-9a-zA-Z\$]//g'

I would like to replace invalid characters of $site to make valid Schema Object Name (like database name) with underscore. Replace should be done with Perl regex. In this example the . should be replaced with _

In Bash:

site="www.mysite.com"
mysql_db_name= ???

My problem is, that I don't know:

  • How to input $site to Perl regexp to do the replacements, then assign result to $mysql_db_name variable?

Thanks!

  • 4
    Is there a reason that this absolutely must be perl? – DopeGhoti Jul 5 '17 at 16:36
  • I know Perl regexp much better than sed or awk, so I can debug Perl regex more easier. – klor Jul 5 '17 at 16:38
  • And if for some reason this has to be Perl, why does it also have to use regex? – OrangeDog Jul 5 '17 at 22:42
  • 1
    Also, Perl is based on sed and awk, and has the most complicated and hard to debug replacement system of all the alternatives being offered. – OrangeDog Jul 5 '17 at 22:43
6
mysql_db_name=$(printf %s\\n "$site" | perl -lpe 'y/0-9a-zA-Z$_/_/c')

Now since you know Perl well, no need for any explanations.

mysql_db_name=${site//[!a-zA-Z_$0-9]/_}

mysql_db_name=$(perl -se 'print y/0-9a-zA-Z$/_/cr' -- -_="$site")
  • Almost good, but result is bad: "www_mysite_com_". Has one more _ that it would need. If this is fixed, will accept as solution. – klor Jul 5 '17 at 16:43
  • Yes, it is fixed now. Your second solution is a simple bash replacement, but AFAIK, the regex used in bash replacements are different from Perl regex. Anyway your fixed first answer works fine, so accepting as working solution. – klor Jul 5 '17 at 16:53
  • 1
    The bash method is exactly what Perl is doing, albeit taking the less circuitous route of first having to print the variable -> then modify -> then print again. Note that Perl y/// operator DOES NOT take regexes. They are literal chars except for -. – user218374 Jul 5 '17 at 17:12
8

If you don't have to use perl, tr makes this dead simple:

mysql_db_name="$(echo -n "$site" | tr -C '0-9a-zA-Z_$' '_')"
  • Thank you very much for your solution! If there would be no Perl based solution, I would accept your answer, but in this case I have closer solution to the asked one. – klor Jul 5 '17 at 16:46
  • Sorry, but your working solution did not meet the specification (Perl regex based solution). If there would be possible, I would split the accepted answer between both of you. – klor Jul 5 '17 at 16:56
  • No apologies necessary. – DopeGhoti Jul 5 '17 at 17:58
  • 1
    Can pass the variable directly to tr with a here-string: tr -C '0-9a-zA-Z_$' <<<"$site" – gardenhead Jul 5 '17 at 23:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.