Division of data in a text message
/ * - Question Description According to the custom information prefix table (each information prefix can be seen as a information separator), the string split processing is made, in the split processing function, first define a pixel table @SPLITS, the table contains four information prefixes (actual use, you can add additional information in the function, or you can define a formal table to save the information prefix data, so you can implement the user Customize the processing of information prefix data). Its spin-off handling process is this: (1) In the SMS string, each message is starting with the specified information prefix, which is tight with the prefix contains information, so first get the SMS string, first Information prefix string and its length, if the SMS string does not begin with a certain information prefix, then it is not a correct SMS string to return to the empty result set. (2) Get the first information prefix position after the first information prefix. (3) If the information prefix does not exist, it is indicated that the last period of information is inserted into the result set and exits the function. (4) If the information prefix exists, the current information segment is intercepted by this position, and the intercepted information segment is saved to the result set, then remove the splitted information segment from the SMS string, and the loop starts processing from (1). All information is divided until all the information is split.
If EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[dbo]. [f_splitstr]') and xtype in (n'fn ', n'if', n't ') DROP FUNCTION [DBO] [f_splitstr] GO / * - String String Function Depending on the custom separator list, split the string of the specified separator, return the result table - Zou Jian 2005.04 (please keep this information) - * // * - Call example selection * from f_splitstr (N'AC China BC898CC Americans Japanese DC 8C Hung Run ') - * / CREATE FUNCTION F_SPLITSTR (@s varchar (8000)) Returns @re table (split varchar (10) , Value Varchar (100)) asbegin declare @SPLITS TABLE (split varchar (10) SELECT 'DC' DECLARE @ POS1 INT, @ POS2 INT, @ split var, @ POS1 = 1, @ split = split, @ splitlen = splitlen from @Splits where @s like split '% 'While @ POS1> 0 Begin Select Top 1 @ POS2 = Charindex (split, @ s, @ splitlen 1) from @Splits Where Charindex (split, @ s, @ splitlen 1)> 0 Order by charIndex (split, @ s, @ splitlen 1) if @@ rowcount = 0 Begin INSERT @RE VALUES (@ Split, Stuff (@ s, 1, @ splitlen, '')) Return End else begin insert @re value (@ Split, Substring (@ s, @ splitlen 1, @ POS2- @ splitlen- 1)) SELECT TOP 1 @ POS1 = 1, @ split = split, @ splitlen = splitlen, @ s = stuff (@ s, 1, @ POS2-1, ') from @Splits where stuff (@ s, 1, @ POS2-1, '') Like Split '%'
End end returnendgo original address
