Diy SQL string decomposition function Split

Source: Internet
Author: User

Some time ago, we encountered this problem when performing a batch data review or deletion function:

Because the review or deletion operation is performed in the stored procedure, we plan to splice the primary key of the selected data into a string and upload it to the stored procedure for separation before processing.

In C # and JavaScript, there is a Split function that separates strings. It is assumed that there are similar functions in SQL. The results are not found in the MSSQL help document for half a day. No way, I had to write it myself. I searched the relevant articles on the Internet and read one or two articles about the User-Defined Functions of SQL separator strings. The result was a bit disappointing. It may be my own poor level, or because someone else's code didn't write comments. In short, it was very difficult to look at it. Before reading it, I decided to write it myself...

The idea is simple: in the string to be decomposed, if a specified separator exists, the string before the first separator is taken out and saved to the table, delete the extracted string and the first Separator in the string to be decomposed, and then continue the next decomposition. If the specified separator still exists, it will be decomposed)

The following are SQL statements:

  1. -- ===================================================== ======
  2. -- Author: Henson
  3. -- Create date: 2011-04-20
  4. -- Description: String separation function
  5. -- ===================================================== ======
  6. Alter function [dbo]. [Split]
  7. (
  8. @ StrText varchar (3000), -- the original string to be separated
  9. @ StrSplit varchar (100) -- delimiter
  10. )
  11. RETURNS @ temp Table
  12. (
  13. ID int identity primary key,
  14. SingleVal varchar (1000)
  15. )
  16. AS
  17. BEGIN
  18. Declare @ intLen int -- used to store the length of the original string to be detached
  19. Declare @ intSplitLen int -- delimiter Length
  20. Declare @ intIndex int -- used to store the position of the separator string in the original string
  21. Declare @ strVal varchar (1000) -- used to store the separated string
  22. -- Get the length of the original string
  23. Set @ intLen = LEN (RTRIM (LTRIM (@ strText )))
  24. Set @ intSplitLen = LEN (RTRIM (LTRIM (@ strSplit )))
  25. -- The original string is not empty before further separation.
  26. If (@ intLen> 0)
  27. Begin
  28. -- Loop the original string until the original string is separated
  29. While CHARINDEX (@ strSplit, @ strText)> 0
  30. Begin
  31. -- Obtain the position of the separator string in the original string
  32. Set @ intIndex = CHARINDEX (@ strSplit, @ strText)
  33. -- Obtain the separated string and insert it into the table
  34. Set @ strVal = RTRIM (LTRIM (LEFT (@ strText, @ intIndex-1 )))
  35. If (LEN (@ strVal)> 0)
  36. Begin
  37. Insert Into @ temp (SingleVal) values (@ strVal)
  38. End
  39. -- Remove the separated string from the original string, including the separator.
  40. Set @ strText = Substring (@ strText, @ intIndex + @ intSplitLen, @ intLen-@ intIndex)
  41. -- Reset the length of the original string
  42. Set @ intLen = LEN (@ strText)
  43. End
  44. -- If the original string after separation is still not empty, the table should also be inserted.
  45. If (LEN (RTRIM (LTRIM (@ strText)> 0)
  46. Begin
  47. Insert Into @ temp (SingleVal) values (@ strText)
  48. End
  49. End
  50. Return
  51. END

The delimiter can be multiple characters)

This is generally the case when multiple strings are organized: strKeys + = strSingleKey + ",";

Therefore, the string may end with a comma. If the end of the string is removed after the organization is complete, it will not end with a comma. Therefore, after the loop decomposition is complete, if left and right spaces are removed from the remaining original string), if there is still content, it should also be stored in the table.

Maybe I am not the best in writing this kind of code, and there may be problems. If you really need to improve it, please point it out. Thank you very much !!!

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.