Find a SQL statement

lancelover

Wait, let me see it, but nothing seems wrong

kittast

while (charindex (',', @ ch)> 0)
begin
set @ wz = charindex (',', @ ch)
SET @ SUB = substring (@ ch, 1, @ wz-1)
end
If ',' is always true here

lancelover

create function separate (@ch varchar (8000))
returns VARCHAR (1000)
as
begin
declare @wz int
declare @sub varchar (1000)
while (charindex (',', @ ch)> 0)
begin
set @ wz = charindex (',', @ ch)
SET @ sub = substring (@ ch, 1, @ wz-1)
set @ch = substring (@ ch, @ wz + 1, len (@ch)-@ wz + 1)
end
return (@sub)
end

恶魔右指

Methods as below:
create table ta (id int, name varchar (50))
insert ta
select 1, 'aa, bb'
union all select 2, 'aaa, bbb, ccc'
union all select 3, 'Aa, Bb, Cc, Dd'


Method 1 is realized by cursor:

declare @tb table (id int, name varchar (50))-Use table variables to display the effect
DECLARE @id int, @ name varchar (50)
DECLARE roy CURSOR
FOR SELECT * from ta
OPEN roy
FETCH next FROM roy
into @ id, @ name
WHILE @@ FETCH_STATUS = 0
BEGIN
    while CHARINDEX (',', @ name)> 0
        begin
            INSERT @tb select @ id, LEFT (@ name, CHARINDEX (',', @ name) -1)
            SET @ name = STUFF (@ name, 1, CHARINDEX (',', @ name), '')
        end
        insert @tb select @ id, @ name
        FETCH NEXT FROM roy into @ id, @ name
end
CLOSE roy
DEALLOCATE roy
select * from @tb

Method 2 uses table variables:
declare @ta table (id int)-Generate table variables with 1-50 increments
declare @i int, @ j int
select @ i = 1, @ j = 50--Defines the maximum number of characters
while @i!> @ j
    begin
        insert @ta select @i
        select @ i = @ i + 1
    end
select a.id,
      Display column = substring (a.name, b.id, charindex (',', a.name + ',', b.id) -b.id)
from ta a, @ ta b
where substring (',' + a.name, b.id, 1) = ','

The effect is as follows:


id name
----------- ------------------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
3 Aa
3 Bb
3 Cc
3 Dd

(The number of rows affected is 9)

--drop table ta

imapiggy

More typical topic, learning ~~~

tangxuan

Do not understand! Collection learning, thanks to all comrades upstairs!

jiming_88m

select a, substr (b, 1,6) from xxx
union
select a, substr (b, 8,14) from xxx where length (b)> 7
union
select a, substr (b, 16,22) from xxx where length (b)> 16
....
....
....
Add yourself

sting0626

SELECT TOP 8000 id = identity(int,1,1)
INTO # FROM syscolumns a, syscolumns b
Can this sentence be explained in detail!

mapleshill

Because VARCHAR is at most 8000 characters.

tlf1155

Learned~