How to remove characters in a string after the specified character?
This article will guide you on how to remove all characters after the first occurrence of the specified character. Here we have removed the characters after the first “_” in a column using an expression. This method can be applied to various databases, such as Microsoft SQL Server, SQL Server Analysis services, PostgreSQL, and MySQL.
Steps to Remove Extra Characters
- Use the following expression to remove all characters after the first “_” in the column:
IF(INSTR([ColumnName],'_') > 0, LEFT ([ColumnName], INSTR([ColumnName],'_')-1),[ColumnName])
This expression checks if there is an underscore in the specified column. If an underscore is found, it removes all characters after the first underscore. If no underscore is found, the original column value is returned.
For example, if you have a column named “AccountEmail” with the following data:
john_doe@example.com
jane_smith@example.com
After applying the expression, the modified column will look like this:
john
jane
Similarly you can remove characters based on the required delimiters.
Additional References
