Geek asks and answers

How to get middle part of string in mysql

I am trying to get the middle of a line. My table is as follows:

id   machine   model
1    lathe     ISO-678-1567

I want to get a middle part, such as 678 for a car model.

Desred output:

id   machine   model             midcode
1    lathe     ISO-678-1567      678

I am currently doing the same with PHP codwe. But I think that it will be better to do Mysql in the query. Please help me how to do this?

+3
source share
5 answers

use MID(str,pos,len)the mysql function that will do this.

SELECT id,machine,model,MID(model,4,3) as midcode FROM tablename
+6
source

If it modelalways has a fixed length, you can use SUBSTRING(), for example:

SELECT SUBSTRING(model, 5, 3) AS 'midcode' FROM Tablename
+4
source

sql , :

SELECT id,machine,model,
       left(substring(model,(instr(model,'-')+1)),instr(model,'-')-1) AS midcode
FROM tbl_machine;
+1

SUBSTR/SUBSTRING: http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_substr

SUBSTRING (, _, )

SELECT SUBSTRING(model, 5, 3) as 'midcode' from Tablename
0

MID () - returns a substring starting at the specified position
SUBSTRING () Returns a substring as specified

Link to the site: DOCS OFFICIAL

0
source

More articles:


All Articles