A replacement instance for special requirements of SQL Server-[Leaf]

Demand Stickers:

Http://topic.csdn.net/u/20120204/10/8b902fd2-8909-4ed9-b534-4a1a72454eff.html#r_77443331

Requirement Description:

For example, if yyyyn is used, the result is: 1 person, 2 person, 3 person, and 4 person.

Analysis:

It seems to be a string replacement, if 'y' is replaced with the number of the location where 'people' + Y is located.

Solution:

Declare @ t table (ID int, Col varchar (5) insert into @ tselect 1, 'yyyn' Union allselect 2, 'nnny 'Union allselect 3, 'nnnyn' Union allselect 4, 'yynyn' -- 1. method 1: case when -- the most intuitive processing method: Select ID, Col = left (COL, Len (COL)-1) from (select ID, col = case when left (COL, 1) = 'y' then' people 1, 'else' 'end + case when substring (COL, 2, 1) = 'y' then' 2, 'else' end + case when substring (COL,) = 'y' then' 3, 'else' 'end + case when substring (COL,) = 'y' then' 4, 'else' 'end + case when substring (COL) = 'y' then' people 5, 'else' end from @ t) A/* ID Col ----------- ---------------------- 1 person 1 person 2 person 3 person 42 person 1 person, 53 persons 44 persons 1, 2 persons 4 */-- 2. use a custom function -- create function fn_getpersoncount (@ STR varchar (5) returns varchar (20) asbegin declare @ result varchar (20) set @ result = ''clare @ I int set @ I = 0 while (charindex ('y', @ Str)> 0) begin set @ result = @ result + 'people' + ltrim (charindex ('y', @ Str) + @ I) + ', 'set @ STR = right (@ STR, len (@ Str)-charindex ('y', @ Str) set @ I = @ I + 1 end set @ result = left (@ result, Len (@ result) -1) return @ resultenddeclare @ t table (ID int, Col varchar (5) insert into @ tselect 1, 'yyyn' Union allselect 2, 'ynnny 'Union allselect 3, 'nnnyn' Union allselect 4, 'yynyn' select ID, Col = DBO. fn_getpersoncount (COL) from @ t/* ID Col ----------- ------------------ 1 person 1 person 2 person 3 person 42 person 1 person 53 person 44 person 1 person 2 person, person 4 */-- 3. merge column values declare @ t table (ID int, Col varchar (5) insert into @ tselect 1, 'yyyn' Union allselect 2, 'nnny 'Union allselect 3, 'nnnyn' Union allselect 4, 'yynyn'; with MACO as (select. *, 'people' + ltrim (B. number + 1) as C1 from @ t a, Master .. spt_values B where B. type = 'p' and B. number <5 and substring (COL, 0 + right (number + 1, 1), 1) = 'y') Select ID, Col = stuff (select ', '+ C1 from MACO t where id = MACO. ID for XML Path (''), 1, 1,'') from MACO group by ID/* ID Col ----------- -------------------- 1 person 1 person 2 person 3, persons: 42 persons 1, 53 persons 44 persons 1, 2 persons 4 */