CODEWITHSUNDEEP
sql
anna
anna

Reputation: 9

How can I write the SQL-Code, so I only get the first Name?

From this query SELECT NAME FROM OBJ_R

The current output is

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

How to get this expected output:

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

And also:

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

Upvotes: 0

Views: 88

Answers (2)

LukStorms
LukStorms

Reputation: 29647

Oracle and MySql and SQLite

Using SUBSTR with 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 and MySql

Using SUBSTRING with 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;

Microsoft SQL Server

Using SUBSTRING with CHARINDEX or 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

There's a few other tricks to use in MySql.

Using SUBSTRING_INDEX 

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

Using SUBSTRING with 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;

Using LEFT and 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;

Upvotes: 1

kiran gadhe
kiran gadhe

Reputation: 743

Actually @jarlh suggested right option but still this query will give you expected result.

SELECT SUBSTRING(name, 1,CHARINDEX(',', name)) AS FirstName FROM OBJ_R

Upvotes: 0

Related Questions