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
  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.
  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,second) values(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'
  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.