Shulou(Shulou.com)06/01 Report--

In database development, it is sometimes encountered that strings are divided according to certain rules, such as "a string with a comma as a delimiter, such as" a memeb, pencycline, parenthetics, and so on, which need to be separated, and then separate into a line. The first thing that comes to mind is to use the substitution function to remove the delimiter. So there is:

IF (OBJECT_ID (Noble Tempdb.. destroy T1') IS NOT NULL) BEGIN DROP TABLE # # T1 politics EndGODECLARE @ string NVARCHAR (MAX); SET @ string=N'123,abc,456,AAA,DDD';SET @ string=N'SELECT * INTO # # T1 FROM (SELECT''+ REPLACE (@ string,''' AS result UNION ALL SELECT'') +'') a'; EXEC (@ string); SELECT * FROM # T1 + go

Code-1: method 1

This method is simple and ingenious in splicing SQL sentences, but it has some shortcomings. (1) splicing of the SQL is not intuitive enough, more difficult to prepare; (2) if the separator is half-width of the English single quotation marks, then need to be processed; (3) in some cases, if the string has Chinese and other non-English characters, it will show garbled; (4) due to the splicing of SQL, if the string to be split is very long, then the splicing SQL may be too long and cannot be executed. Therefore, this method can only be used as a simple replacement or a way of thinking.

Method 2 (recommended):

IF OBJECT_ID (Numbago splitters rowno') IS NOT NULLBEGIN DROP FUNCTION fn_split_rowno ENDGOCREATE FUNCTION fn_split_rowno (@ str NVARCHAR (MAX), @ split NVARCHAR (20) =',') RETURNS @ t TABLE (row_no INT, col NVARCHAR) ASBEGIN DECLARE @ i INT SET @ I = 0 WHILE (CHARINDEX (@ split, @ str) 0) BEGIN INSERT @ t (row_no,col) VALUES (@ I + 1 str, 1, CHARINDEX (@ split) @ str)-1)) SET @ str = STUFF (@ str, 1, CHARINDEX (@ split, @ str) + LEN (@ split)-1,') SET @ I = @ I + 1 END IF (@ str'') INSERT @ t (row_no,col) VALUES (@ I + 1, @ str) RETURNENDGO

Code-2: method 2 (recommended)

Encapsulated into a function, easy to call, and without the problems in method 1.

SELECT * FROM fn_split_rowno (Noble 123 people abcMagi 456, AA writ DDD pr 51CTOwi Jing Ji')

Code-3: calling function

Method 3 (from the network):

DECLARE @ string NVARCHAR (MAX) SET @ string = Noble 123 CHARINDEX 456, REVERSE SELECT REPLACE (LEFT (s, CHARINDEX (',', s),') AS resultFROM (SELECT rquence (LEFT (@ string, r)) +' 'AS s FROM (SELECT (SELECT COUNT (*) FROM sys.objects WHERE NAME)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.