sql - 我怎样才能编写 SQL 代码，所以我只能得到第一个名称？

Question

从此查询 SELECT NAME FROM OBJ_R

当前输出为

NAME
--------
Müller, Peter
Mettler, Hans
Casalugi, Maria

如何获得这个预期的输出：

Name
--------
Müller
Mettler
Casalugi

并且：

First_Name
----------
Peter
Hans
Maria

标签： sql

Answer 1

Oracle和MySql和SQLite

将 SUBSTR 与 INSTR 一起使用

select CASE WHEN INSTR(NAME,',') > 0 THEN SUBSTR(NAME,1,INSTR(NAME,',')-1) ELSE NAME END as Name from OBJ_R;
select CASE WHEN INSTR(NAME,',') > 0 THEN LTRIM(SUBSTR(NAME,INSTR(NAME,',')+1,LENGTH(NAME))) ELSE ' ' END as First_Name from OBJ_R;

PostgreSQL和 MySql

将 SUBSTRING 与 POSITION 一起使用

select CASE WHEN POSITION(',' IN NAME) > 0 THEN SUBSTRING(NAME FROM 1 FOR POSITION(',' IN NAME)-1) ELSE NAME END as Name FROM OBJ_R;
select CASE WHEN POSITION(',' IN NAME) > 0 THEN TRIM(LEADING ' ' FROM SUBSTRING(NAME FROM POSITION(',' IN NAME)+1 FOR LENGTH(NAME))) ELSE '' END as First_Name FROM OBJ_R;

微软 SQL 服务器

将 SUBSTRING 与CHARINDEX或PATINDEX一起使用。

select case when charindex(',',NAME) > 0 then substring(NAME,0,charindex(',',NAME)-1) else NAME end as Name from OBJ_R;
select ltrim(substring(NAME, charindex(',', NAME)+1, len(NAME))) as first_name from OBJ_R;

mysql

在 MySql 中还有一些其他的技巧可以使用。

使用SUBSTRING_INDEX

select SUBSTRING_INDEX(NAME,', ', 1) as Name FROM OBJ_R;
select SUBSTRING_INDEX(NAME,', ',-1) as First_Name FROM OBJ_R;

将 SUBSTRING 与LOCATE一起使用

select CASE WHEN LOCATE(',',NAME) > 0 THEN SUBSTRING(NAME FROM 1 FOR LOCATE(',',NAME)-1) ELSE NAME END as Name FROM OBJ_R;
select CASE WHEN LOCATE(',',NAME) > 0 THEN LTRIM(SUBSTRING(NAME FROM LOCATE(',',NAME)+1 FOR LENGTH(NAME))) ELSE '' END as First_Name FROM OBJ_R;

使用 LEFT 和 RIGHT

select LEFT(NAME, LOCATE(', ',NAME)-1) as Name FROM OBJ_R;
select RIGHT(NAME, LOCATE(' ,',REVERSE(NAME))-1) as First_Name FROM OBJ_R;