SQL string analyzer Functions
-- ===================================================== ====== -- Create function fun_splitstr, this function converts a string separated by a separator into a table -- @ strsource used to store the string to be analyzed -- @ strseprate used to store the delimiter -- @ temp used to return the analysis result (table) -- for example, @ strsource = '20140901'; @ strseprate = ',', a table is returned: first behavior 123 and second row 456 -- ========================================== ============== if exists (select * From sysobjects where name = n' fun _ splitstr ') drop function fun_splitstrgocreate function fun_splitstr (@ strsource varchar (8000), @ strseprate varchar (10) returns @ temp table (F1 varchar (100 )) as begindeclare @ I intset @ strsource = rtrim (ltrim (@ strsource) set @ I = charindex (@ strseprate, @ strsource) while @ I> = 1 begininsert @ temp values (left (@ strsource, @ i-1) set @ strsource = substring (@ strsource, @ I + 1, Len (@ strsource) -@ I) set @ I = charindex (@ strseprate, @ strsource) endif @ strsource <> ''in in insert @ temp values (@ strsource) endreturn end go
InstanceCode:
-- ===================================================== =======-- Example to execute function -- ============================== ======================== select * From DBO. fun_splitstr ('1, 2', ',') Go
Result: