Hi @EmadKhan
Following query can fullfill your requirement but only when every record has comma
Select Substring(textfield, 1,Charindex(',', textfield)-1) as BusinessName,
Substring(textfield, Charindex(',', textfield)+1, LEN(textfield)) as SubCatagory
from v_bindInstitutionNameCombo_DailyLog
But as you mentioned in your question that when there is no comma, SubCatagory should be null, So you have to create a cursor for it
Refer this..
declare @textfield nvarchar(50)
create table #Temp_Table (BusinessName nvarchar(50), SubCatagory nvarchar(50))
declare cur cursor for select textfield from v_bindInstitutionNameCombo_DailyLog
open cur
fetch next from cur into @textfield
while @@FETCH_STATUS = 0
begin
if CHARINDEX(',',@textfield) > 0
begin
insert into #Temp_Table values(Substring(@textfield, 1,Charindex(',', @textfield)-1), Substring(@textfield, Charindex(',', @textfield)+1, LEN(@textfield)))
end
else
begin
insert into #Temp_Table values(@textfield, null)
end
fetch next from cur into @textfield
end
close cur
deallocate cur
select * from #Temp_Table
drop table #Temp_Table
This will fullfill your requirement
Happy Coding :)
