Community

    • Login
    • Search
    • Recent
    • Tags
    • Popular
    • Users
    • Groups
    • Search

    Delete all rows with a specific content occuring in a specific column

    General Discussion
    3
    4
    2026
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • Markus Ruhsam
      Markus Ruhsam last edited by

      Hello,

      I can’t work out how to delete rows which have a specific content in a specific column. In the example below I’d like to delete all rows which have a ‘.’ in column 5, so that after the deletion I would only have the rows with ‘T’ and ‘A’ in it. Can anybody help?

      Thank you

      Markus

      32 18 . G T 13 PASS NS=4;DP=41 GT:CATG 0/0:0,0,0,10 1/1:0,0,12,0 ./.:0,0,0,0 0/0:0,0,0,13 0/0:0,0,0,6 ./.:0,0,0,0 ./.:0,0,0,0 ./.:0,0,0,0
      32 19 . G . 13 PASS NS=4;DP=41 GT:CATG 0/0:0,0,0,10 0/0:0,0,0,12 ./.:0,0,0,0 0/0:0,0,0,13 0/0:0,0,0,6 ./.:0,0,0,0 ./.:0,0,0,0 ./.:0,0,0,0
      32 20 . C . 13 PASS NS=4;DP=41 GT:CATG 0/0:10,0,0,0 0/0:12,0,0,0 ./.:0,0,0,0 0/0:13,0,0,0 0/0:6,0,0,0 ./.:0,0,0,0 ./.:0,0,0,0 ./.:0,0,0,0
      32 21 . C . 13 PASS NS=4;DP=41 GT:CATG 0/0:10,0,0,0 0/0:12,0,0,0 ./.:0,0,0,0 0/0:13,0,0,0 0/0:6,0,0,0 ./.:0,0,0,0 ./.:0,0,0,0 ./.:0,0,0,0
      32 22 . A . 13 PASS NS=4;DP=41 GT:CATG 0/0:0,10,0,0 0/0:0,12,0,0 ./.:0,0,0,0 0/0:0,13,0,0 0/0:0,6,0,0 ./.:0,0,0,0 ./.:0,0,0,0 ./.:0,0,0,0
      32 23 . T . 13 PASS NS=4;DP=41 GT:CATG 0/0:0,0,10,0 0/0:0,0,12,0 ./.:0,0,0,0 0/0:0,0,13,0 0/0:0,0,6,0 ./.:0,0,0,0 ./.:0,0,0,0 ./.:0,0,0,0
      32 24 . G . 13 PASS NS=4;DP=41 GT:CATG 0/0:0,0,0,10 0/0:0,0,0,12 ./.:0,0,0,0 0/0:0,0,0,13 0/0:0,0,0,6 ./.:0,0,0,0 ./.:0,0,0,0 ./.:0,0,0,0
      32 25 . C . 13 PASS NS=4;DP=41 GT:CATG 0/0:10,0,0,0 0/0:12,0,0,0 ./.:0,0,0,0 0/0:13,0,0,0 0/0:6,0,0,0 ./.:0,0,0,0 ./.:0,0,0,0 ./.:0,0,0,0
      32 26 . G A 13 PASS NS=4;DP=41 GT:CATG 0/0:0,0,0,10 0/0:0,0,0,12 ./.:0,0,0,0 0/1:0,5,0,8 0/0:0,0,0,6 ./.:0,0,0,0 ./.:0,0,0,0 ./.:0,0,0,0
      32 27 . G . 13 PASS NS=4;DP=41 GT:CATG 0/0:0,0,0,10 0/0:0,0,0,12 ./.:0,0,0,0 0/0:0,0,0,13 0/0:0,0,0,6 ./.:0,0,0,0 ./.:0,0,0,0 ./.:0,0,0,0
      32 28 . G A 13 PASS NS=4;DP=41 GT:CATG 0/1:0,5,0,5 0/0:0,0,0,12 ./.:0,0,0,0 0/0:0,0,0,13 0/0:0,0,0,6 ./.:0,0,0,0 ./.:0,0,0,0 ./.:0,0,0,0
      32 29 . T . 13 PASS NS=4;DP=41 GT:CATG 0/0:0,0,10,0 0/0:0,0,12,0 ./.:0,0,0,0 0/0:0,0,13,0 0/0:0,0,6,0 ./.:0,0,0,0 ./.:0,0,0,0 ./.:0,0,0,0

      1 Reply Last reply Reply Quote 0
      • Jim Dailey
        Jim Dailey last edited by Jim Dailey

        @Markus-Ruhsam

        Perhaps you could search for something like this:

        ^([^ ]+ ){4}\. .*\R 
        

        and replace it with nothing?

        The search begins at the start of a line (^) and is for 4 occurrences ({4}) of 1 or more non-blanks ([^ ]+) followed by a blank ( ) followed by a period and space (\. ) followed by any other characters (.*) up to the end of the line, including the newline character(s) (\R).

        If the replace box is empty, the lines matching the search will be deleted.

        1 Reply Last reply Reply Quote 0
        • Markus Ruhsam
          Markus Ruhsam last edited by

          This did not quite work but adjusting the search string to (^\d+\t\d+\t.\t\w\t…*\n) did. Thanks for that!

          1 Reply Last reply Reply Quote 0
          • guy038
            guy038 last edited by guy038

            Hello, Markus-Ruhsam,

            Of course, it’s a bit late, by now, but here is a shorter regex, that works perfectly well !

            SEARCH (?-s)^(?=.{10}\.).+\R

            REPLACE EMPTY

            Et voilà !

            Notes :

            • The first part (?-s) is an in-line modifier, which ensures that the dot meta-character will match only standard characters, even if you previously checked the . matches newline option

            • From the beginning of line ^, the form (?=.{10}\.), also called a positive look-ahead, verifies if the condition Does exist a DOT character, in column 11, of the current line, is true.

            • The important thing to remember about look-arounds is that, as soon as the condition is evaluated, the regex engine position is reset to the position before evaluating the look-around condition. So, in our case, to the position just before the first character of each line

            • Consequently, if this condition is true, the following part of the regex .+ matches all the standard characters of the current line

            • Finally, the \R syntax matches any kind of EOL characters ( \r\n ) in Windows files, ( \n ) in Unix files or ( \r ) in Mac files

            • Now, as the replacement string is empty, that means that any complete line ( with a dot character at column 11 ) will be, automatically, deleted !

            Best Regards,

            guy038

            1 Reply Last reply Reply Quote 0
            • First post
              Last post
            Copyright © 2014 NodeBB Forums | Contributors