More difficult SQL issues in the forums: 22 (string split, string merge, interval range of noncontiguous digits, random return string)

See a post in the forum, the post has some SQL aspects of the question, I think these questions are very representative.

The connection of the original posts is: http://bbs.csdn.net/topics/390884161?page=1#post-398177057

Here is my solution, for your reference:

1. Splitting strings

CREATE TABLE test1 (number varchar) INSERT into test1 values (' 1,2,3,4,5,6 ') desired result: number------123456 (6 rows affected)

My Solution:

--1. Split string CREATE TABLE test1 (number varchar) INSERT into test1 values (' 1,2,3,4,5,6 ') select--t.number,       SUBSTRING (T.number, S.number, CHARINDEX (', ', t.number+ ', ', S.number)-s.number) as Numberfrom test1 T,master. Spt_values swhere s.number >=1and s.type = ' P ' and SUBSTRING (', ' +t.number,s.number,1 ') = ', '/*number123456*/

2, the range of non-contiguous numbers

CREATE table test2 (number int) insert into test2 values (1), (2), (3), (4), (5), (7), (8), (10), (11), (13), (15) Implementation effect Number---------------------1~57~810~111315

My Solution:

--2. Interval of discontinuous numbers CREATE TABLE test2 (number int) insert into test2 values (1), (2), (3), (4), (5), (7), (8), (10), (11), (13), (15); With TAS (SELECT *,       row_number ()-Over (order by number) Rnfrom test2), the Select Case is min (number) =max (number) then cast (  MIN (number) as varchar)             Else cast (min (number) as varchar) + ' ~ ' +cast (max (number) as varchar) end as Numberfrom Tgroup by number-rn/*number1~57~810~111315*/

3. Merging strings

CREATE TABLE TEST4 (name varchar (ten), MyType varchar (TEN), CJ Int) insert INTO test4 values (' Zhang San ', ' language ', 83), (' Zhang San ', ' math ', 65), (' Zhang San ', ' physics ', 85, (' John Doe ', ' language ', 73), (' John Doe ', ' mathematics ', 69), (' John Doe ', ' physics ', 93) achieve effects Namemytype            CJ Zhang San language, mathematics, Physics 83,65,85 John Doe language, Mathematics, Physics 73, 69,93

My Solution:

--3. Merge string CREATE table test4 (name varchar (83), MyType varchar (TEN), CJ Int) insert INTO test4 values (' Zhang San ', ' language ',), (' Zhang San ', ' Math ', 65 ', (' Zhang San ', ' physical ', 85), (' John Doe ', ' language ', 73), (' John Doe ', ' math ', 69), (' John Doe ', ' physical ', ' ") select name,       stuff ((select ', ' +mytype from test4 t4 where t4.name = test4.name FOR XML Path (")), as MyType,   stuff ((select ', ' +cast (CJ as varchar) from TE st4 t4 where t4.name = test4.name FOR XML Path ("))," as Cjfrom Test4group by NAME/*NAMEMYTYPECJ John Doe language, Mathematics, physics 73,69,93 Zhang San Chinese, mathematics, physics 83,65,85*/

4.Write a function, enter the 2 varchar parameter @str1, @str2, return the string @str1 the number of occurrences in the @str2, @int.

such as (@str2 = ' ABCSAAC ', @str1 = ' A ') returns 3.

My solution:

--4. String 1 in String 2 occurrences declare @str2 varchar = ' ABCSAAC ' declare @str1 varchar = ' A ' Select (Len (@str2)-Len (replace (@str2, @str1, '))/Len (@str1)  as T--this needs to be divided by the length of the string 1/*t3*/

5.Query the size of all table spaces and sort from large to small.

My solution:

6. Randomly returns the specified number of characters

Write a stored procedure. Input parameter @int, return a random @int letter. If you enter 5. Returns a random 5 letters.

My solution:

--6. Randomly returns the specified number of characters declare @int int DECLARE @str varchar set @int = 5set @str = "Select @str = @str + char (ASCII (' A ') + ABS (Checksum (NEWID ()))%) from master: Spt_values where type= ' P ' and number between 1 and @intselect @str/*epozq*/

More difficult SQL issues in the forums: 22 (string split, string merge, interval range of noncontiguous digits, random return string)