2

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!

Improve this question
4
  • 4
    Is there a reason that this absolutely must be perl?
    – DopeGhoti
    Jul 5, 2017 at 16:36
  • I know Perl regexp much better than sed or awk, so I can debug Perl regex more easier.
    – klor
    Jul 5, 2017 at 16:38
  • And if for some reason this has to be Perl, why does it also have to use regex?
    – OrangeDog
    Jul 5, 2017 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, 2017 at 22:43

3 Answers 3

Reset to default
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_$' '_')"
Improve this answer
4
  • 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, 2017 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, 2017 at 16:56
  • No apologies necessary.
    – DopeGhoti
    Jul 5, 2017 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, 2017 at 23:51
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")
Improve this answer
3
  • 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, 2017 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, 2017 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, 2017 at 17:12
0

If you are in bash, and the value to edit is already in a variable, why do you need to use awk, perl, sed or anything if bash could do it by itself:

$ site="www.mysite.com"
$ mysql_db_name="${site//[^a-zA-Z0-9$]/_}"
$ echo "$mysql_db_name"
www_mysite_com

While the syntax for bash patterns (not regex) is different than Perl regexes, what is inside A bracket expression. is equivalent to both alternatives: Match one character from the list provided.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .