Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Search Replace Specific Column using RegEx

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Search Replace Specific Column using RegEx
# 1  
Old 04-12-2015
Search Replace Specific Column using RegEx
Have Pipe Delimited File:
Code:
> BRYAN BAKER|4/4/2015|518 VIRGINIA AVE|TEST
> JOE BAXTER|3/30/2015|2233 MockingBird RD|ROW2

On 3rd column where the address is located, I want to add a space after every numeric value - basically doing a "s/[0-9]/&\ / ":
Code:
> BRYAN BAKER|4/4/2015|5 1 8  VIRGINIA AVE|TEST
> JOE BAXTER|3/30/2015|2 2 3 3  MockingBird RD|ROW2

I know you can do a:
>
Code:
 awk '$3="XYX"' FS=, OFS='|' filename.txt

to replace column 3 w/ "XYZ" but how do i do a search replacing using RegEx?

I am also fine w/ a sed solution... just wish i was better w/ awk. I only want to do a regex search/replace on a specific column.

Thanks!
Moderator's Comments:
Mod Comment code tags please
Last edited by Don Cragun; 04-12-2015 at 11:42 PM.. Reason: code tags & icode tags
# 2  
Old 04-13-2015
You could do this:
Code:
awk '{gsub(/[0-9]/,"& ",$3)}1' FS=\| OFS=\| file

Which would will append a space to any digit in field 3..

A bit more accurate (if needed) maybe would be to use the split() function first
Code:
awk '{split ($3, F, " "); s=F[1]; gsub(/[0-9]/,"& ",s); sub(F[1],s,$3)}1' FS=\| OFS=\|

Which would only append a space to digits at the beginning of the field..
Last edited by Scrutinizer; 04-13-2015 at 12:59 AM.. Reason: typo
This User Gave Thanks to Scrutinizer For This Post:
svn
# 3  
Old 04-13-2015
In addition to the suggestion Scrutinizer provided, note that the command in post #1 in this thread:
Code:
 awk '$3="XYX"' FS=, OFS='|' filename.txt

produces the output:
Code:
> BRYAN BAKER|4/4/2015|518 VIRGINIA AVE|TEST||XYX
> JOE BAXTER|3/30/2015|2233 MockingBird RD|ROW2||XYX

Your input field separator is |; not ,. So, setting FS=, probably isn't what you want for this input file. Since there are no commas in your input file, setting field 3 creates an empty field and adds another field with the contents you specified.

Setting $3 to the string XYX will never replace column 3 with XYZ.
This User Gave Thanks to Don Cragun For This Post:
svn
# 4  
Old 04-13-2015
Don - thanks for the correction in my original post. You are correct, I didn't have my delimiters set correct and wouldn't have worked. I was going back an d forth between pipe and csv, thus the confusion.

Scrutinizer - thanks soooooo much! Works Great! Now i know how to do regex on any specific column using your example... will come in very handy!
# 5  
Old 04-14-2015
If i can tap you guys one more time.

What if the address is:
Code:
BRYAN BAKER|4/4/2015|5178 16th Street|TEST

What I want is:
Code:
BRYAN BAKER|4/4/2015|5 1 7 8 16th Street|TEST

Right now, when I use "awk '{gsub(/[0-9]/,"& ",$3)}1' FS=\| OFS=\| file", I get:
Code:
BRYAN BAKER|4/4/2015|5 1 7 8 1 6 th Street|TEST

I don't want to do a search and replace for all numeric fields .. only for everything up until the first space. Is that possible?
# 6  
Old 04-14-2015
Look at Scrutinizer's post #2 in this thread again. He told you exactly how to do that...
Code:
awk '{split ($3, F, " "); s=F[1]; gsub(/[0-9]/,"& ",s); sub(F[1],s,$3)}1' FS=\| OFS=\|

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Need help to use regex to do search and replace. Don't know how to and can't figure out how :(

Hi, Below is an excerpt from a 20000+ lines and I want to do a search and replace of a specific string but I don't know how and I can't figure out how to. Can't find an example from Google or anywhere to do what I am wanting to do. A 2018-11-21 08:42:17 TEST_TEST 2018-11-21... (9 Replies)
Discussion started by: newbie_01
9 Replies

2. Shell Programming and Scripting

Search and replace specific positions of specific lines

Hi, I have a file with hundreds of lines. I want to search for particular lines starting with 4000, search and replace the 137-139 position characters; which will be '000', with '036'. Can all of this be done without opening a temp file and then moving that temp file to the original file name. ... (7 Replies)
Discussion started by: dsid
7 Replies

3. Shell Programming and Scripting

Multi line regex for search and replace

I have text file like below: a.txt Server=abc Run=1 Time=120.123 Tables=10 Sessions=16 Time=380.123 Version=1.1 Jobs=5 Server=abc Run=2 Time=160.123 Tables=15 Sessions=16 Time=400.258 Version=2.0 (1 Reply)
Discussion started by: sol_nov
1 Replies

4. Shell Programming and Scripting

awk to search for specific line and replace nth column

I need to be able to search for a string in the first column and if that string exists than replace the nth column with "-9.99". AW12000012012 2.38 1.51 3.01 1.66 0.90 0.91 1.22 0.82 0.57 1.67 2.31 3.63 0.00 AW12000012013 1.52 0.90 1.20 1.34 1.21 0.67 ... (14 Replies)
Discussion started by: ncwxpanther
14 Replies

5. Shell Programming and Scripting

Regex - search and replace

I have file which contains data in the following format all in a single line: BDW_PUBLN_ID DECIMAL(18:0) NOT NULL PRIMARY INDEX ARGO_ACCT_DEP_PI ( OFC_ID ,CSHBX_ID ,TRXN_SEQ_NUM ,PROCG_DT ) PARTITION BY RANGE_N(PROCG_DT BETWEEN DATE '2012-03-01' AND DATE '2014-12-31' EACH INTERVAL '1' MONTH );... (4 Replies)
Discussion started by: ysvsr1
4 Replies

6. Shell Programming and Scripting

Perl:Regex for Search and Replace that has a flexible match

Hi, I'm trying to match the front and back of a sequence. It works when there is an exact match (obviously), but I need the regex to be more flexible. When we get strings of nucleotides sometimes their prefixes and suffixes aren't exact matches. Sometimes there will be an extra letter and... (2 Replies)
Discussion started by: jdilts
2 Replies

7. Shell Programming and Scripting

Regex:search/replace but not for escaped character

Hi Input: - -- --- ---- aa-bb-cc aa--bb--cc aa---bb---cc aa----bb----cc Output: . - -. -- aa.bb.cc (7 Replies)
Discussion started by: chitech
7 Replies

8. Shell Programming and Scripting

Replace column that matches specific pattern, with column data from another file

Can anyone please help with this? I have 2 files as given below. If 2nd column of file1 has pattern foo1@a, find the matching 1st column in file2 & replace 2nd column of file1 with file2's value. file1 abc_1 foo1@a .... abc_1 soo2@a ... def_2 soo2@a .... def_2 foo1@a ........ (7 Replies)
Discussion started by: prashali
7 Replies

9. Emergency UNIX and Linux Support

search replace regex question

Hi, I need to run a search and replace on a large database, what I need to change is all instances of #### (eg. 1764 or 1964) to (####) (eg. (1764) or (1964)) But there might be other numbers in there such as (1764) and I do not need those changed to ((1764)) How can I... (7 Replies)
Discussion started by: lawstudent
7 Replies

10. Shell Programming and Scripting

Replace if regex on specific column matches expression?

I am attempting to convert rewrite rules to Nginx, and since due to the mass amount of rewrites we must convert, I've been trying to write a script to help me on a specific part, easily. So far I have this: rewrite ^action/static/(+)/$ staticPage.php?pg=$1&%$query_string; What I want done... (5 Replies)
Discussion started by: EXT3FSCK
5 Replies
Login or Register to Ask a Question