djangoproject.com | python.org | nginx.org
version seven.
  http://demongin.org
demongin.org - String Replacement in BASH

String Replacement in BASH

A short how-to about string replacement in BASH scripts; examples include string replacement in a SQL command and using awk to modify a variable on the fly.


Wednesday, 2010-01-13 | Careerism, Programming

Most of us, when all is said and done, like what we like and make up reasons for it afterwards.
Soren F. Petersen

Sometimes you find yourself on a weird server where there's no PERL, no python and, basically no real reason to stick around or build anything permanent; in situations like that, where you've got to get work done in a hurry and don't have the time or authority to make improvements to the environment, BASH-fu is an essential skill.

Odds are, however, if you're a.) working in a hurry and b.) working with strings in BASH, you're going to run into problems (sooner, rather than later) with spaces, escape characters, etc. What follows are two examples for how to do some intermediate-level scripting operations that involve string substitution in BASH.

MySQL Scripting in a Hurry

Suppose you've got to run a query in an iterative fashion and you've only got BASH to work with. Did you know that you can write SQL statements that return nothing but the results of your queries? No pipes, no pluses, no warnings, no stats, no extra spaces: just the stuff you wanted. Take a look:
mysql -s -r -N -u$MYSQLUSER -p$MYSQLPASS -h$HOST -P$PORT -e "select data from table where other_data=1" database_name
The -s gets you "silent" (i.e. reduced verbosity), the -r gets you "raw" (i.e. no pipes/spaces) and the -N skips the whole bit about printing column names.

If you're clever about punctuation, you can use this to create variables dynamically. You can even, if you're really punctuation-savvy, use this to loop over dynamically created variables:
for VARIABLE in `mysql -s -r -N -u$USER -p$PASS -h$HOST -P$PORT -e "select data from table where other_data=$WHATEVER" database_name`;
    do echo -e "$VARIABLE FROM DB";
done
And so we return to the main subject of this main topic of this post: if you execute the following SQL and your results include blank spaces, you'll get something like this:
My FROM DB
query FROM DB
results FROM DB
have FROM DB
spaces FROM DB
Fortunately, there's a quick way to fix this in your SQL statement. You simply use the replace() function in your SQL query:
select replace(data,' ','_') from table where other_data=$WHATEVER" database_name
This gets you results like this:
My_query_results_have_spaces FROM DB
Excellent.

Ye Olde awk One-liner

Suppose, now, that you've got this variable with underscores and you need them to be spaces again. If you were using the variable in another query, I suppose you could just use the replace() function in reverse, but let's say that you need to pass this variable along to some other program, and you need the original spaces back.

This time, awk's your buddy:
toconnell@esme:~$ VARIABLE="My query results have spaces"
toconnell@esme:~$ FIXED_VARIABLE=`echo $VARIABLE |awk '{gsub("_", " ");print}'`
toconnell@esme:~$ echo $FIXED_VARIABLE
My query results have spaces
And voila: you've got your spaces back.

In the final estimation, this type of scripting is definitely unpleasant. But knowing how to do this kind of stuff, I have found, is kind of like being able to strip wires with your teeth: it's exactly the sort of ability that, when demonstrated, is practically guaranteed to impress girls, inspire respect/fear in the hearts of your enemies and give your friends the general impression that you've got a "rough-and-ready", "can-do" attitude that makes you a good guy to have around when things go bad.

And, just like being able to strip wires with your teeth, being able to do this kind of BASH scripting will help you get the job done in a hurry with a minimum amount of complaining about not having the right tools for the job (even if being able to do both doesn't have a sno-cone's chance in Hell of doing any of that other stuff I mentioned).