I wanted to print only one part of string in column and ignore the remaining stuff including special characters, how can i acheive it
| A | B |
| -------- | -------------- |
| abc | zet=kadala; |
| def | zet=kade; None |
| de | zet=kad; None:81 |
Tried SUBSTRING_INDEX(B,’=’, -1) AS B
expected output
| A | B |
| -------- | -------------- |
| abc | kadala |
| def | kade |
| de | kad |
>Solution :
You can use the SUBSTRING_INDEX() function to extract the required substring from column B. The SUBSTRING_INDEX() function returns a substring from a string before or after a specified delimiter.
Here’s an example SQL query that should give you the expected output:
SELECT A, SUBSTRING_INDEX(SUBSTRING_INDEX(B, '=', -1), ';', 1) AS B
FROM your_table_name;
In the above query, we are using two nested SUBSTRING_INDEX() functions to extract the required substring from column B.
The inner SUBSTRING_INDEX() function returns the substring after the last occurrence of the ‘=’ character, which gives us ‘kadala’ in the first row, ‘kade’ in the second row, and ‘kad’ in the third row.
The outer SUBSTRING_INDEX() function returns the substring before the first occurrence of the ‘;’ character, which removes the remaining characters after ‘kadala’, ‘kade’, and ‘kad’.
Note: Replace ‘your_table_name’ with the actual name of your table.