Removing unwanted data in the cell of the QGIS Attribute table

Question

How can I remove remove data after any special character in the cell using QGIS?

E.g.: Valli Amman Mandir, Chettiyakotai

I want only Valli Amman Mandir in the cell. The rest should be removed.

Answer 1

Use this expression based on regular expressions to:

1. find the first special character - special character understood as everything else than letters from a to z (upper and lower case), number 0 to 9 and white spaces \\s. You could adapt the definition of "special character" by adding or removing characters.
2. delete everything starting from this special character until the end of the input string, returning just everything before it.

regexp_replace ("fieldname", '[^A-Za-z0-9\\s].*','')

---

Explanation: regexp_replace () uses regular expressions: for an input string ("fieldname"), it matches everything that corresponds to the regex defined (see below) and replaces it with an empty string ''.

The regex is defined as [^A-Za-z0-9\\s].*. This works like this:

• square brackets [] to define a group of characters;
• inside the brackets, several groups are defined: letters A to Z (uppercase): A-Z, a to z (lowercase): a-z, digits 0 to 9: 0-9 as well as any white space \\s;
• These groups are treated as "normal" characters - so to find special characters, we have to exclude these ones. To exclude specific characters, use the ^ (hat). With this, we match any character except letters A-z, a-z, digits 0-9 and white spaces. So this finds periods ., commas ,, double points :, semicolons ; etc.
• We want to get everything inside the input string starting with these special characters, so we add .* that matches any number (using quantifier *, meaning: zero or more) of characters of any kind (using the dot . - a wildcard that stands for any character) - so in fact everything inside the string after the special character.

Answer 2

A solution inspired by @BenW's previous answer. I was going to comment the suggested change, but the post was taken down.

left("field", regexp_match("field", '[^A-Za-z0-9\\s]') -1)

regexp_match finds the position of a specified pattern, in this case the first special character* in the string (as thoroughly explained by @Babel). The left() function is used to keep only the characters up to the returned position. -1 is used to shift the position over one to omit the special character from the returned string.

* or more specifically, the first non-alphanumeric character that is not a space

Answer 3

The example given doesn't appear to be using English names. Google Translate guesses they are romanized Tamil words. I point this out because none of the existing answers create a regular expression that recognizes a broader world than US-ASCII, well, maybe Windows-1252 or ISO/IEC 8859-15 if giving the benefit of the doubt.

According to this answer for What regular expression engine does QGIS use? - Geographic Information Systems Stack Exchange, "QGIS, uses QT's QRegularExpression. which is itself just PCRE."

QRegularExpression implements Perl-compatible regular expressions. It fully supports Unicode.

This is great news. QGIS uses PCRE, which is a very robust regular expression engine that fully supports Unicode, so why not write Unicode regular expressions that work for the Roman alphabet as well as other alphabets, like the Tamil alphabet?

If one is going to work with PCRE regular expressions, such as in QGIS, it is good to bookmark pcre2pattern specification - pcre.org. The Unicode character properties section covers most of what I use below.

As I mentioned in my comment to the question, it is important to be specific with regular expressions. Phrases like "any special character" don't really mean anything because they can mean so many different things to so many different people. For my expression below, I am assuming "any special character" means the Unicode "Other Punctuation" character category, and I am using \N instead of \X so to mimic the newline behavior of the . metacharacter.

regexp_replace("fieldname", '\\p{Zs}*\\p{Po}+\\N*', '')

Breaking down the regular expression:

\p{Zs} Unicode category:  Matches any character in the 'Space separator' Unicode category
  * Quantifier:  Match 0 or more of the preceding token
\p{Po} Unicode category:  Matches any character in the 'Other punctuation' Unicode category
  + Quantifier:  Match 1 or more of the preceding token
\N Not line break.  Matches any character that is not a line break
  * Quantifier.  Match 0 or more of the preceding token

Not only does the expression convert Valli Amman Mandir, Chettiyakotai to Valli Amman Mandir, but it also works with the Tamil alphabet to convert வள்ளி அம்மன் மந்திர், செட்டியகோட்டை to வள்ளி அம்மன் மந்திர்.

Answer 4

In addition to the previous brilliant answers, you can use the regex group to capture a specific part of the string. Using the following expression, you can capture the first part of the string before the comma , as follows:

regexp_replace("Fieldname",'(\\w.+)(,)(\\s{0,1}\\w+)','\\1')

Where:

Fieldname is the name of the field from which you want to extract the text.

(\\w.+) is the 1st Capturing Group. the parentheses is for capturing groups (). The \\w matches any word character (equivalent to [a-zA-Z0-9_]), the . matches any character, and the + matches the previous token between one and unlimited times.

(,) is the 2nd Capturing Group

(\\s{0,1}\\w+) is the 3rd Capturing Group. The \\s matches any whitespace character, and {0,1} matches the previous token between zero and one time.

\\1 replaces the text in the new field with the first captured group only, which is everything before the comma ,. In other words, select everything before the comma.

Example:

The name field has the strings that we want to extract the text from, and the selected field is a new field that we want to have the final selected text.

Applying the above expression to update the selected field we got the desired output:

Update based on @Babel's comment

The above approach removes anything after a comma, however, to remove anything after a special character, the following expression can be used:

regexp_replace ("name", '[^\\w.\\s+].+|[\\.\\+].+','')

Where:

[^\\w.\\s+] match any character not present in the list one and unlimited times, as many times as possible.

\s matches any whitespace character.

\w matches any word character

| means Or

[\.\+] Match a single character present in the list below where \. matches the character . and + matches the character + escaped by a backslash as they are special characters in regex.

then remove the selected texts using the regex_replace function.

Example (2)