MSSQL SQL Server below shares a novel method of string interception

Original address: http://www.maomao365.com/?p=7307

Summary:previously, when splitting a string, it was handled in a way similar to the Split function, and the following shared a way of separating a regular string.that is: 1. Use the Replace function to turn the string into a compliant XML format string2. Get the specified string information using the Xml.value methodis as follows:Lab Environment: SQL Server R2

--Example:-----Get the contents of the brackets in the following string-----We can replace the following rule's string with replace with the XML-formatted string, and then get the relevant information using the XML selectDECLARE @stringInfo   VARCHAR(MAX)='1:[Cat Hut],2:[string split],3:[sql Tutorial Blog]'      , @q VARCHAR(3)=3;---Get information from the third separator Declare @xmlInfoXML;Set @stringInfo = '<xmlinfo keyId = "'+@stringInfo;Set @stringInfo = REPLACE(@stringInfo,'[','"Value="');Set @stringInfo = REPLACE(@stringInfo,'],','"/><xmlinfo keyId ="');Set @stringInfo = REPLACE(@stringInfo,']','"/>');Set @stringInfo = REPLACE(@stringInfo,':',"'); Set @xmlInfo = CONVERT(XML,@stringInfo); SELECT @xmlInfo  as [Xmlinfo],@xmlInfo. Value ('(/xmlinfo[@keyId =sql:variable ("@q")]/@value) [1]', 'varchar ($)');

MSSQL SQL Server below shares a novel method of string interception