Question 20394 in Mysql Question posted in
The official MySQL documentation can be found here.

print only part of string in a column – Mysql

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            |
Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    If you want to go the SUBSTRING_INDEX() route, then you will need to call it twice:

    SELECT A, SUBSTRING_INDEX(SUBSTRING_INDEX(B, ';', 1), '=', -1) AS B
FROM yourTable;

    On MySQL 8+, you could also use a regex replacement:

    SELECT A, REGEXP_REPLACE(B, '^.*?=|;.*$', '') AS B
FROM yourTable;
    Login or Signup to reply.
  3. 0

    To extract the substring after the ‘=’ character in column B and remove any additional characters after the semicolon (;), you can use the SUBSTRING_INDEX function twice.

    Here’s an example SQL query that should achieve the expected output:

    SELECT A, TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(B, '=', -1), ';', 1)) AS B
FROM table_name;

    This query will select column A and a substring of column B that starts after the last occurrence of the ‘=’ character and ends at the first occurrence of the ‘;’ character. The TRIM function is used to remove any leading or trailing spaces from the resulting string.

    The output of this query should be a new table with only the desired substring in column B.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search