How to create dynamic sql substring based on field name func

How to create dynamic SQL sub string based on field name functionid without writing static ?

I have table name DoneCode i need when add new function on table Done code

automatically without rewrite or modify my code

so that i need to do

substring (DoneCode,@FunctionId,1)

but i done know how to do that

so suppose tommorrow add new Function as

10,PCN

so no need to add new function in code as substring(DoneCode,10,1) as PCN

I need to use substring(DoneCode,FunctionId,1) as FunctionId

but i dont know how to make within loop

so can you help me

create table #Donecode
(
FunctionId int,
FunctionName nvarchar(50)
)
insert into #Donecode
values
(1,'Lifecycle'),
(2,'Rohs'),
(3,'Reach'),
(4,'FMD'),
(5,'Parametric'),
(6,'Package'),
(7,'IntroductionDate'),
(8,'MFG'),
(9,'Qualification')
create table #filedetails
(
FileID int,
DoneCode nvarchar(50)
)
insert into #filedetails (FileID,DoneCode)
values
(3301,'101011111110'),
(3301,'101101111111'),
(3301,'101001000011')
select substring (Donecode,1,1)as Lifecycle,substring (Donecode,2,1)as Rohs,substring (Donecode,3,1)as Reach,substring (Donecode,4,1)as FMD,substring (Donecode,5,1)as Parametric,substring (Donecode,6,1)as Package,substring (Donecode,7,1)as IntroductionDate,substring (Donecode,8,1)as MFG,substring (Donecode,9,1)as Qualification
into #FunctionsDiv from #filedetails where DoneCode is not NULL and fileid=3301
drop table #filedetails
drop table #Donecode
drop table #FunctionsDiv

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Vinay Singh6y ago
Hi Check the link
https://www.sqlservertutorial.net/sql-server-string-functions/sql-server-substring-function/
https://www.edureka.co/blog/substring-in-sql/
https://www.sqlshack.com/sql-substring-function-overview/