2

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');
1
  • 1
    if there is only one ( or values( in the line, awk would be good choice... or you can use perl to perform substitution on string that is matched...
    – Sundeep
    Jun 15, 2017 at 5:22

3 Answers 3

2
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
3
  • Why three -e, when it is possible writing without them? - sed ":a; s/\('[^' ]*\) */\1/g; ta" input.txt For readability?
    – MiniMax
    Jun 15, 2017 at 12:49
  • Yes, for readability. Note that what you've written will go into an infinite loop. We need at least one space for it to converge.
    – user218374
    Jun 15, 2017 at 14:54
  • I checked it before adding to the comment - it was working, now copy from comment - get infinite loop. Compared this two version and found, that 2 spaces after \) were converted to one. And it is cause the unworking command, yes. It is browser squeeze two spaces to one. Look, here 5 spaces between letters 'A': A A. But only one is displayed. Explanation here
    – MiniMax
    Jun 15, 2017 at 16:06
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.

1

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');
0

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.