Originally posted address: http://community.9cbs.net/expert/topic/3230/3230422.xml? Temp = .7884485
Such data
Field 1 Fields 2 2, 4, 23 3, 6, 345 23, 56, 4 3, 3, 67 Take the data in the query field 1 is 4 So the field 2 is taken in the field 2, the result is as follows: ==== ======== 4 64 67
-------------------------------------------------- -----------------------------
- Treatment example
- Test data CREATE TABLE TB (field 1 varchar (10), field 2 varchar (10)) Insert TB SELECT '2, 4, 23', '3, 6, 345'Union All Select' 23, 56, 4 ',' 3, 3, 67'go
- Write a custom function to handle Create Function F_Value (@a varchar (10), @ b varchar (10), @ c varchar (10)) Returns varchar (10) asbegin declare @i int, @ posient @ A = Left (@ a, charindex (',' @ c ", ',' @ a ',') - 1), @ POS = LEN (@a) -len (Replace (@a, ' , ',' ')) 1, @ i = charindex (', ', @ b)
While @i> 0 and @POS> 1 SELECT @ b = substring (@ b, @ i 1, 8000), @ i = charindex (',', @ b), @ POS = @ POS-1 Return (Case @Pos by 1 dam case when @i> 0 Then Left (@ b, @ i-1) else @B ELSE '' end) endGO
- Query Declare @a varchar (10) set @ a = '23 '- query parameters
- Query statement select a = @ a, b = dbo.f_value (field 1, field 2, @ a) from TBGO
- Delete Test DROP TABLE TBDROP FUNCTION F_VALUE
/*--Test Results
A b -------------------- 23 34523 3
(The number of rows affects is 2 lines) - * /
