CODEWITHSUNDEEP
mysqlregexbashsed
usr-local-ΕΨΗΕΛΩΝ
usr-local-ΕΨΗΕΛΩΝ

Reputation: 26874

a MYSQL script to convert the column names to uppercase

This is the opposite of this question.

I am migrating from a lowercase_names db server to a case sensitive setup in which I require UPPER CASE names for tables and column (they are stored uppercase in code).

I have read in this answer that using sed is a good way to replace names to lower case, which is not what I want.

So starting from the regex

s/`\(\w\+\)`/\L&/g

I changed it to 

s/`\(\w\+\)`/\U&/g

Thinking that it would convert to upper case. Then I tried to run this one-liner after having imported the uppercase DDL

mysqldump --skip-triggers --compact --no-create-info -hxx -uxx -pxx source | sed s/`\(\w\+\)`/\U&/g | mysql -hxx -uxx -pxx target

But that failed

-bash: /g: No such file or directory
-bash: (w+): command not found
sed: -e expression #1, char 4: unterminated `s' command

How can I run this uppercase-converter in Bash?

Upvotes: 0

Views: 211

Answers (1)

Tom Fenech
Tom Fenech

Reputation: 74645

You need to use single quotes around your sed command, otherwise some of the parts will be interpreted by the shell: 

mysqldump ... | sed 's/`\(\w\+\)`/\U&/g'

In case you're interested, the & is interpreted as an instruction to run the command in the background (so /g is processed as a separate command) and the backticks create a subshell. Escaping the parentheses means that they are interpreted literally. Escaping \w and \+ has no effect in this context, so the command (w+) is run.

As you're using & in the replacement string to get the full matched patttern rather than a specific capture group (e.g. \1), you can also remove the parentheses as pointed out by Avinash:

mysqldump ... | sed 's/`\w\+`/\U&/g'

In response to your concerns about other data being affected, it's a pretty broad regex (captures any word characters within backticks) so could apply to a lot of things. Depending on the size of your data, you might like to use sed -n 's/`\w\+`/\U&/gp' which will show you the parts that are being affected by the substitution. Using that information you could possibly change your regex. However, it's likely that there are better ways to do what you want in SQL itself.

Upvotes: 2

Related Questions