SQL string in the section feature alike apt SP

SQL string processing power is relatively weak, such as 1,2,3,4,5 I want to loop through this string, whether the series, then traverse a very easy, but T-SQL does no aid array So much distress handling down. The following functions to effect the same manipulation for the array of strings. First, for a short-lived table with a great many establish function f_split (@ c varchar (2000), @ split varchar (2)) returns ; @ t table (col varchar (20)) as begin ; while (charindex (@ split, @ c) 0) begin ; insert @ t (column) amounts ​​(substring (@ c, 1, charindex (@ split, @ c) -1 )) set @ c = stuff (@ c, 1, charindex (@ split, @ c ),'') all oversert @ t (column) values ​​(@ c) return end work select * from dbo.f_split ('dfkd, dfdkdf, dfdkf,GHD Rare Straighteners, dffjk',',') drip ; feature f_split col ; ------- ------------- dfkd dfdkdf dfdkf dffjk ; (the digit of rows affected at 4 rows)
2 characters separated by the specified string and returns the number of elements later the split, the method is very simple, there is to penetrate how many string delimiter,GHD Straighteners, and then increase an, namely is, the required results. CREATE function Get_StrArrayLength (@ str varchar (1024), - the string to split @ split varchar (10) - separator) returns int as begin ; declare @ location int declare @ start int declare @ length int
set @ str = ltrim (rtrim (@ str)) set @ location = charindex (@ split, @ str) set @ length = 1 while @ location 0 start ; set @ start = @ place +1 set @ location = charindex (@ split, @ str, @ start) set @ width = @ width +1 end return @ width end cry example: elect dbo.Get_StrArrayLength ('78, 1,2,3',',') return value: 4
three symbols separated by the specified string and returns the specified index section after the 1st few elements of the array as simple as CREATE function Get_StrArrayStrOfIndex (@ str varchar (1024), - to be split string @ split varchar (10), - separator @ index int - obtain the first of several elements) returns varchar (1024) as begin declare @ location int declare @ start int declare @ next int declare @ seed int
set @ str = ltrim (rtrim (@ str)) set @ start = 1 set @ afterward = 1 set @ seed = len (@ split) set @ location = charindex (@ split, @ str) meantime @ location 0 and @ index> @ next begin set @ start = @ location + @ seed set @ location = charindex (@ split, @ str, @ start) set @ next = @ next +1 end if @ location = 0 select @ location = len (@ str) +1 - there are two situations here: 1, the string does not exist separator 2, there is a string delimiter, while out of cycles,GHD Diamond Flag 2011, @ location is 0, then the default is behind a string delimiter. return substring (@ str, @ start,GHD IV Straighteners, @ location-@ start) end call example: select dbo.Get_StrArrayStrOfIndex ('8, 9,4',',', 2) return value: 9
three, combining two functions upon, as the traverse the string as an array of elements
affirm @ str varchar (50) set @ str = '1, 2,GHD MK4 Pure Straighteners,3,GHD Red Butterfly 2011,4,5 'declare @ next integer set @ next = 1 while @ next <= dbo.Get_StrArrayLength (@ str ,',') begin print dbo.Get_StrArrayStrOfIndex (@ str ,',',next) set @ next = @ next +1 end
call results: 1 2 3 4 5
Posted in