1
Answer

How to create dynamic sql substring based on field name func

ahmed elbarbary

ahmed elbarbary
4y
817
1
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
  1. create table #Donecode  
  2. (  
  3. FunctionId int,  
  4. FunctionName nvarchar(50)  
  5. )  
  6. insert into #Donecode  
  7. values  
  8. (1,'Lifecycle'),  
  9. (2,'Rohs'),  
  10. (3,'Reach'),  
  11. (4,'FMD'),  
  12. (5,'Parametric'),  
  13. (6,'Package'),  
  14. (7,'IntroductionDate'),  
  15. (8,'MFG'),  
  16. (9,'Qualification')  
  17.   
  18.   
  19. create table #filedetails  
  20. (  
  21. FileID  int,  
  22. DoneCode nvarchar(50)  
  23. )  
  24. insert into #filedetails (FileID,DoneCode)   
  25. values   
  26. (3301,'101011111110'),  
  27. (3301,'101101111111'),  
  28. (3301,'101001000011')  
  29. 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  
  30. into #FunctionsDiv  from #filedetails where DoneCode is not NULL and fileid=3301  
  31.   
  32. drop table #filedetails  
  33. drop table #Donecode  
  34. drop table #FunctionsDiv  
 
Answers (1)
Next Recommended Forum