Split String Value Behalf on Special Character in SQL Server

Hi everybody. I hope you re enjoying and loving coding. Developers often need to split data at a special character, like !,@,#,$ and so on. Many characters are available on the keyboard.
 
In the morning I want to upload Microsoft Excel data to SQL Server. I know this is not a difficult task but the problem is if any column value is too large or multiple data sets are in a single column like the following:
 
CDSO (India) approved, WHO prequalified, USFDA / USAID approved, Medicines and Healthcare products Regulatory Agency (UK) approved, Product sold to UN
 
What is the best way to check itself,  just look a bit at the following picture to help yu understand.
 
 

 
The user wants to enter data into a single column. I know this is difficult, but don't worry, SQL Server hs the ability to split the string value using special characters. Okay, to get to the main point, I created a function in SQL Server. This function splits the string and returns a value in table format.
 
How to Create a function in SQL Server
  1. create function [dbo].[fun_splitcolumnvalue_for_importProductName]  
  2. (@Firstdata varchar(max),@seconddata varchar(max),@delimeter varchar(50))  
  3. returns @tamptable table(First varchar(max), Second varchar(max))  
  4. as   
  5. begin   
  6.  -- Do stuff here  
  7. return  
  8. end 
The preceding function in @FirstData and @secondData is a function parameter.  It's a data parameter and @delimeter holds a special character like ,!@#$% and so on. You can split upon any special character.
 
First varchar(max) and Second varchar(max) is a column name of the @tempTable. 
 
How to work this function
  1. create function [dbo].[fun_splitcolumnvalue_for_importProductName]  
  2. (@dataone varchar(max),@seconddata varchar(max),@delimeter varchar(50))  
  3. returns @tamptable table(first varchar(max),second varchar(max))  
  4. as  
  5. begin  
  6. DECLARE @CINDEX INT=1  
  7. declare @Csindex varchar(max)  
  8. DECLARE @CSLICE varchar(max)  
  9. declare @Csslice varchar(max)  
  10. WHILE @CINDEX !=0  -- This loop continue until @cindex is value 0.  
  11. BEGIN  
  12. set @CINDEX = CHARINDEX(@delimeter,@dataone)  --CHARINDEX is a predefined function. This function return a int type of value like  5. Let's -- --example  select charindex(',','joginder,singh')  
  13. --select CHARINDEX('@','I love @ my india')  
  14.  -- output is charindex 9 and 8 number   
  15.   
  16. set @Csindex=CHARINDEX(@delimeter,@seconddata)  
  17. if(@CINDEX!=0)  
  18. begin  
  19. SELECT @CSLICE =LEFT(@dataone,@CINDEX - 1)  --left is a sql function it's a predefined.  this function returns a string type values like 'joginder'. Let's --example-- select left('joginder,singh',(charindex(',','joginder,singh')-1))  
  20.  --output is left function 'joginder' without single comma.  
  21.    
  22.   
  23. select @Csslice=LEFT(@seconddata,cast(@Csindex as int)-1)  
  24. end  
  25. else  
  26. begin  
  27. select @CSLICE=@dataone  
  28. select @Csslice=@seconddata  
  29. end  
  30. insert into @tamptable(first,secondvalues(ltrim(rtrim(@CSLICE)), ltrim(rtrim(@Csslice)))  
  31. set @dataone = RIGHT(@dataone,LEN(@dataone) - @CINDEX) --Right is a also sql predifined function. This function returns a string type values like 'singh'  
  32.   
  33. set @seconddata=right(@seconddata,LEN(@seconddata)-@Csindex)  
  34. IF LEN(@CINDEX) = 0  
  35. begin  
  36. BREAK  
  37. end  
  38. end  
  39. return  
  40. end 
  1. --select * from fun_splitcolumnvalue_for_importProduct('USA,india','usa code,inida code',',')  
  2. output :  
  3. first |second  
  4. USA | usa code  
  5. India| India Code  
A common error

I know that without errors there is no fun in a developer's life. I encountered an error when making this function so I will share it with you. The problem occurs when you make this type of function. 
  1. An infinite loop. 
I hope every thing is clear. Comments are most welcome.


Similar Articles