How to remove spaces from the line after a given word?

Question

Suppose I have a file.txt; the content of the file is:

insert into x values('a ','  b',' c ');
insert into x values('  m ','  n  ','  p   ');

I just want to remove spaces from each line after the word values.

Desired output:

insert into x values('a','b','c');
insert into x values('m','n','p');

Answer 1

sed -e ':a' -e "s/\('[^' ]*\)  */\1/g" -e ta  file.txt

---

insert into x values('a','b','c');
insert into x values('m','n','p');

We use a looping mechanism in tandem to progressively scrub the spaces inside single quote pairs '...' that a line might have. Note this is assuming no TABs, but which can be handled just as well

sed -e ':a' -e "s/\('[^'[:space:]]*\)[[:space:]]\{1,\}/\1/g" -e ta

Answer 2

Assuming you have already inserted these values into the table, you may trim them of their space characters.

Assuming also that the columns in the table are called c1, c2 and c3:

UPDATE x SET c1 = TRIM(c1), c2 = TRIM(c2), c3 = TRIM(c3);

The TRIM() function will remove both leading and trailing spaces.

If you want to test this on a temporary table first:

CREATE TEMPORARY TABLE t AS SELECT * FROM x;
UPDATE t SET c1 = TRIM(c1), c2 = TRIM(c2), c3 = TRIM(c3);
SELECT * from t;

... or just

SELECT TRIM(c1), TRIM(c2), TRIM(c3) FROM x;

which wouldn't change anything in the database at all.

---

An SQL tip: When inserting values, do mention the names of the columns:

INSERT INTO x (c1, c2, c3) VALUES ('a', 'b', 'c');

This both serves as documentation and allows you to change the schema of the table (inserting new columns or rearranging columns) without having to hunt down and change every INSERT statement in your code.

Answer 3

awk -F '(' '{ gsub(" ","",$2); print $1 FS $2; }' input.txt

Explanation

1. Divide the string by the '(' sign - have two fields now:
   $1 = insert into x values
$2 = 'a ',' b',' c ');
2. process $2 field, substituting all spaces to nothing.
3. assembling new string:
   $1 (not changed) + FS (left bracket sign) + $2 (processed, without spaces now).

Output:

insert into x values('a','b','c');
insert into x values('m','n','p');