CODEWITHSUNDEEP
mysql
Gulrej
Gulrej

Reputation: 999

Select part of a string in mysql

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

Upvotes: 5

Views: 19848

Answers (4)

joshweir
joshweir

Reputation: 5627

Adding to AlphaMale's answer (Do I propose code change edit, not sure SO etiquette here?), I changed the SPLIT_STR udf slightly as it breaks for multi-byte characters:

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

Example: 

select split_str("Håkansson[!!]Maria", "[!!]", 2) as test;

+------------+
| test       |
+------------+
| Maria      |
+------------+

Upvotes: 0

talha2k
talha2k

Reputation: 25493

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.

Upvotes: 16

knurdy
knurdy

Reputation: 496

EDIT: They work for ORACLE

I wrote the following queries and they seem to work...

SELECT 
SUBSTR( (SELECT SUBSTR('India_Project1_BA_Protex_123',      
INSTR('India_Project1_BA_Protex_123', '_')+1, instr('India_Project1_BA_Protex_123',    
'_', 1,2)) AS output
FROM DUAL),1, INSTR((SELECT SUBSTR('India_Project1_BA_Protex_123',   
INSTR('India_Project1_BA_Protex_123', '_')+1, instr('India_Project1_BA_Protex_123', 
'_', 1,2)) AS output
FROM DUAL),'_',1,2)-1) FROM DUAL

SELECT 
SUBSTR( (SELECT SUBSTR('Japan_ProQ_CXR_Tbxc_3456', INSTR('Japan_ProQ_CXR_Tbxc_3456',   
'_')+1, instr('Japan_ProQ_CXR_Tbxc_3456', '_', 1,2)) AS output
FROM DUAL),1, INSTR((SELECT SUBSTR('Japan_ProQ_CXR_Tbxc_3456', 
INSTR('Japan_ProQ_CXR_Tbxc_3456', '_')+1, instr('Japan_ProQ_CXR_Tbxc_3456', '_', 1,2)) 
AS output
FROM DUAL),'_',1,2)-1) FROM DUAL

Upvotes: 0

Jonas T
Jonas T

Reputation: 3077

Does this work for you?

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

Upvotes: 3

Related Questions