| |

VerySource

 Forgot password?
 Register
Search
Author: kittast

Find a SQL statement

[Copy link]

1

Threads

7

Posts

8

Credits

Newbie

Rank: 1

Credits
8

 China

Post time: 2020-4-2 10:30:01
| Show all posts
Wait, let me see it, but nothing seems wrong
Reply

Use magic Report

2

Threads

18

Posts

16

Credits

Newbie

Rank: 1

Credits
16

 China

 Author| Post time: 2020-4-2 23:45:01
| Show all posts
while (charindex (',', @ ch)> 0)
begin
set @ wz = charindex (',', @ ch)
SET @ SUB = substring (@ ch, 1, @ wz-1)
end
If ',' is always true here
Reply

Use magic Report

1

Threads

7

Posts

8

Credits

Newbie

Rank: 1

Credits
8

 China

Post time: 2020-4-3 15:45:01
| Show all posts
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
Reply

Use magic Report

0

Threads

91

Posts

44

Credits

Newbie

Rank: 1

Credits
44

 China

Post time: 2020-4-9 19:00:01
| Show all posts
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
Reply

Use magic Report

0

Threads

13

Posts

11

Credits

Newbie

Rank: 1

Credits
11

 China

Post time: 2020-5-7 19:15:01
| Show all posts
More typical topic, learning ~~~
Reply

Use magic Report

0

Threads

1

Posts

2

Credits

Newbie

Rank: 1

Credits
2

 China

Post time: 2020-5-11 22:30:01
| Show all posts
Do not understand! Collection learning, thanks to all comrades upstairs!
Reply

Use magic Report

0

Threads

1

Posts

2

Credits

Newbie

Rank: 1

Credits
2

 China

Post time: 2020-5-24 07:30:01
| Show all posts
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
Reply

Use magic Report

0

Threads

1

Posts

2

Credits

Newbie

Rank: 1

Credits
2

 China

Post time: 2020-6-3 18:15:01
| Show all posts
SELECT TOP 8000 id = identity(int,1,1)
INTO # FROM syscolumns a, syscolumns b
Can this sentence be explained in detail!
Reply

Use magic Report

0

Threads

2

Posts

3

Credits

Newbie

Rank: 1

Credits
3

 China

Post time: 2020-6-21 09:15:01
| Show all posts
Because VARCHAR is at most 8000 characters.
Reply

Use magic Report

0

Threads

3

Posts

4

Credits

Newbie

Rank: 1

Credits
4

 China

Post time: 2020-6-21 13:45:01
| Show all posts
Learned~
Reply

Use magic Report

You have to log in before you can reply Login | Register

Points Rules

Contact us|Archive|Mobile|CopyRight © 2008-2020|verysource.com ( 京ICP备17048824号-1 )

Quick Reply To Top Return to the list