SQL string in the section feature alike apt SP

SQL string processing power is relatively weak, such as 1,2,3,4,5 I absence apt circulate through this string, if the order,GHD IV Styling Set, then traverse a quite uncomplicated, merely T-SQL does no patronize order So many difficulty treatment down. The emulating functions apt attain the same dispose as the array of strings. First, as a transient chart with a crowd of establish function f_split (@ c varchar (2000), @ split varchar (2)) returns ; @ t table (column varchar (20)) as start ; while (charindex (@ split, @ c) 0) begin ; insert @ t (col) amounts ​​(substring (@ c, 1, charindex (@ split, @ c) -1 )) set @ c = stuff (@ c, 1, charindex (@ split, @ c ),'') bring an end to ...sert @ t (col) merits ​​(@ c) return end go select * from dbo.f_split ('dfkd, dfdkdf, dfdkf, dffjk',GHD Pink Limited Edition,',GHD Midnight Gift Set 2011,') drop ; function f_split col ; ------- ------------- dfkd dfdkdf dfdkf dffjk ; (the digit of rows affected by 4 rows)
two symbols separated by the specified string and returns the number of elements after the split, the usage is very simple, there is to penetrate how numerous string delimiter, and then multiplication one, 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 begin ; set @ start = @ location +1 set @ location = charindex (@ split, @ str, @ start) set @ length = @ length +1 end return @ length end call example: select dbo.Get_StrArrayLength ('78, 1,2,3',GHD Hair Straightener,',') return value: 4
three characters separated along the specified string and returns the specified concordance partition after the 1st few elements of the array as cozy as CREATE function Get_StrArrayStrOfIndex (@ str varchar (1024), - to be split string @ split varchar (10), - separator @ index int - obtain the 1st of several elements) returns varchar (1024) as begin affirm @ location int declare @ start int declare @ next int declare @ seed int
set @ str = ltrim (rtrim (@ str)) set @ start = 1 set @ next = 1 set @ seed = len (@ split) set @ place = charindex (@ split, @ str) when @ situation 0 and @ index> @ next begin set @ start = @ location + @ seed set @ location = charindex (@ split, @ str, @ start) set @ next = @ next +1 end if @ location = 0 choose @ location = len (@ str) +1 - there are two situations here: 1, the string does not exist separator 2, there is a string delimiter,GHD Leopard Grain, while out of cycles, @ location is 0, then the default is after a string delimiter. return substring (@ str, @ start, @ location-@ start) end cry example: choose dbo.Get_StrArrayStrOfIndex ('8, 9,4',GHD IV Mini Straighteners,',', 2) return value: 9
3, combining two functions on, as the traverse the string as a cloud of elements
declare @ str varchar (50) set @ str = '1, 2,3,4,5 'declare @ next int 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