I have a name field in Students table which is a comma separated string in format "LastName, FirstName, Middle Name".While doing a select statement in SQL query I need to break this up into separate fields.How can I achieve this in SQL?.Some times Middle intial won't be available.
SUBSTRING(Name,CHARINDEX(',',Name,1)+2,LEN(Name)) AS FirstName,
SUBSTRING(Name,1,CHARINDEX(',',Name,1)-1) AS LastName,
Above code works fine when there is no Middle name.
This should give you what you need:
declare @tmp table (fullname varchar(100));
insert @tmp values('James, Billy, L'), ('John, Snow');
select
fullname
, [Last Name]
, case
when charindex(',', Remainder, 0) > 0
then ltrim(substring(Remainder, 0, charindex(',', Remainder, 0)))
else ltrim(Remainder)
end [First Name]
, case
when charindex(',', Remainder, 0) = 0
then NULL
else ltrim(substring(Remainder, charindex(',', Remainder, 0) + 1, len(Remainder)))
end [Middle Name]
from
(select
fullname
, substring(fullname, 0, charindex(',', fullname, 0)) [Last Name]
, substring(fullname, charindex(',', fullname, 0) + 1, len(fullname)) [Remainder]
from @tmp) result;
First just find the occurrences of comma(,) in the string. Then use CASE expression to get the number of comma. If there is 2 comma then we can assume that middle name is also there. If 1 then only first name and last name. Then use the combinations of LEFT, RIGHT, SUBSTRING, CHARINDEX string functions.
Query
select t.name,
left(
t.name,
charindex(',', t.name, 1) - 1
) last_name,
case t.comma_num
when 2
then substring(
t.name,
charindex(',', t.name, 1) + 1,
len(name) -
(charindex(',', t.name, 1) + 1) - charindex(',', reverse(t.name), 1) + 1
)
when 1
then right(
t.name,
charindex(',', reverse(t.name), 1) - 1
)
else null end as first_name,
case t.comma_num
when 2
then right(
t.name, charindex(',', reverse(t.name), 1) - 1
)
else null end as middle_name
from (
select name,
len(name) - len(replace(name, ',', '')) comma_num
from [your_table_name]
)t;
Use CTE and SUBSTRING AND CHARINDEX funntions
DECLARE @Name VARCHAR(100) = 'James, Billy, L'
--DECLARE @Name VARCHAR(100) = 'James, '', L'
;WITH _CTE ( SplitedNames ,RemainStr) AS
(
SELECT SUBSTRING(@Name,0,CHARINDEX(',',@Name)),
SUBSTRING(@Name,CHARINDEX(',',@Name)+1,LEN(@Name))
UNION ALL
SELECT CASE WHEN CHARINDEX(',',RemainStr) = 0 THEN RemainStr ELSE
SUBSTRING(RemainStr,0,CHARINDEX(',',RemainStr)) END,
CASE WHEN CHARINDEX(',',RemainStr) = 0 THEN '' ELSE
SUBSTRING(RemainStr,CHARINDEX(',',RemainStr)+1,LEN(RemainStr))
END
FROM _CTE
WHERE RemainStr <> ''
)
SELECT SplitedNames FROM _CTE
