Select part of a string in mysql

Question

I have a string in one column like this

India_Project1_BA_Protex_123

Japan_ProQ_CXR_Tbxc_3456

I need to select Project1_BA or ProQ_CXR like this in mySQL

Answer 1

There are two functions for extracting some part of string, they are SUBSTRING & SPLITSTRING but SUBSTRING can not be used in this case and SPLITSTRING is not present in MySql. So you have to write your own function:

MySQL does not include a function to split a string. However, it’s very easy to create your own function.

Create function syntax

A user-defined function is a way to extend MySQL with a new function that works like a native MySQL function.

CREATE [AGGREGATE] FUNCTION function_name
RETURNS {STRING|INTEGER|REAL|DECIMAL}

To create a function, you must have the INSERT privilege for the database.

Split strings

The following example function takes 3 parameters, performs an operation using an SQL function, and returns the result.

Function

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

Usage

SELECT SPLIT_STR(string, delimiter, position)

Example

SELECT SPLIT_STR('India_Project1_BA_Protex_123', '_', 2) as second;
SELECT SPLIT_STR('India_Project1_BA_Protex_123', '_', 3) as third;

+------------++-------+
| second     || third |
+------------++-------+
| Project1   || BA    |
+------------++-------+
| ProQ       || CXR   |
+------------++-------+

Now you can concatenate the two results. to get your final result.

Full tutorial here: http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/

Hope this helps.

Answer 2

Does this work for you?

SELECT * FROM table WHERE colum LIKE '%Project1_BA%' OR column LIKE '%ProQ_CXR%'