what might be the query to fetch all the records seperated by comma in SQL ?

Question

In SQL, suppose i have a table employee and have a column as Name containing all the names in the format FirstName,LastName. Ex:Nayeem,Khan . I want to fetch all the last names only ie Khan.

Answer 1

You've not mentioned the DB. You can try something like in MySQL:

SELECT SUBSTRING(name,LOCATE(",",name)+1)
FROM TABLE_NAME

Answer 2

ORACLE or MySQL solution

select DISTINCT SUBSTR(name,INSTR(name,',')+1)
  from employee

EDIT

For SQL Server, try

select SUBSTRING(name,CHARINDEX(',',name)+1,999) 
  from employee 

assumes that name will always contain a ,

Answer 3

In oracle, that would be

select substr(name, 1, instr(name. ',') - 1) as first_name,
       substr(name, instr(name. ',') + 1) as last_name
from TABLE_NAME

Answer 4

Here a postgreSQL solution :

SELECT SUBSTRING(name from ',(.+)$') FROM table;


SELECT SUBSTRING('foo,bar' from ',(.+)$') ;
 substring
-----------
 bar
(1 row)