选择字符串中的所有字符，直到指定的字符MySQL

I have a record in an SQL database that looks like this:

我在SQL数据库中有一条记录如下:

'839af0d3-9301-33d0-abce-e16a673980fd', 'Aforic', '7:74324:35:4:1:1203:348:27:415:158:2:0:0:0D-5H-17M-26S', 'Tactician', ' '

The table is defined as follows:

该表的定义如下:

`T1` (`player_uuid`, `player_name`, `stats`, `inventory`, `selected`)

How do I write a query to select only the name and the first entry from "stats". Basically, I only want to select the first entry in "stats" that is before ":". In this case, it would be 7.

如何编写查询，只从“stats”中选择名称和第一个条目。基本上，我只想选择“stats”中在“:”之前的第一个条目。这里是7。

Thank you!

谢谢你！

2 个解决方案

#1

Use SUBSTRING_INDEX.

使用SUBSTRING_INDEX。

select player_name,substring_index(stats,':',1) 
from T1

Edit: Splitting the values between the first 3 : delimiters,

编辑:分割前3个值:分隔符，

select player_name
,substring_index(stats,':',1) as col_1
,substring_index(substring_index(substring_index(stats,':',3),':',-2),':',1) as col_2
,substring_index(substring_index(stats,':',3),':',-1) as col_3
from T1

#2

The alternative solution using LEFT and LOCATE functions:

使用左和定位函数的替代解决方案:

SELECT
    player_name, LEFT(stats, LOCATE(':', stats) - 1)
FROM 
    T1

Note:

注意:

For functions that operate on string positions, the first position is numbered 1.

对于操作字符串位置的函数，第一个位置被编号为1。

#1