SQL While 循环插入数据 游标 字符串截取 left right substring

sql字符串截取

SQL While 循环插入数据

DECLARE @num int
DECLARE @id int
DECLARE @dt datetime = '2000-01-01 00:00:00'
SET @num=1
WHILE (@num <= 30)
	BEGIN
        PRINT DATEADD(day,@num,@dt)
        --PRINT CONVERT(VARCHAR(10),DATEADD(day,@num,@dt),120)

        INSERT INTO [dbo].[Category] 
		(LanguageId,Title,CreatedDate) 
		VALUES 
		(1,'test' + CONVERT(varchar(100),@num),GETDATE() + @num)

        SET @id = @@IDENTITY			
        SET @num = @num + 1
	END
GO

SQL While 循环修改数据 + WAITFOR DELAY 延迟

DECLARE @num int
DECLARE @id int
DECLARE @dt datetime = '2000-01-01 00:00:00'
SET @num=1
WHILE (@num <= 10)
	BEGIN
	    PRINT DATEADD(day,@num,@dt)
	    --PRINT CONVERT(VARCHAR(10),DATEADD(day,@num,@dt),120)

		UPDATE [User] SET UserName = UserName + CONVERT(varchar(100),Id) WHERE Id = @num			
		SET @num = @num + 1
	END
GO

--延迟案例
DECLARE @num int
DECLARE @id int
SET @num=1
WHILE (@num <= 8)
	BEGIN
	    WAITFOR DELAY '00:00:01' -- 在1秒后延迟执行
		UPDATE trans_queue SET sort = replace(replace(replace(replace(convert(varchar(100), getdate(), 20), '-', ''), ' ', ''), ':', ''),'.','') WHERE queue_code = @num
		SET @num = @num + 1
	END

SQL  Cursor(游标) While 遍历结果集

DECLARE @id INT
DECLARE @name VARCHAR(50)
DECLARE @dt datetime = '2000-01-01 00:00:00'
DECLARE cursor1 CURSOR FOR    --定义游标cursor1
SELECT Id,[Name] FROM [User] WHERE UserName LIKE '%tian%'
OPEN cursor1                  --打开游标
FETCH NEXT FROM cursor1 INTO @id,@name
WHILE @@FETCH_STATUS=0        --判断是否成功获取数据
        BEGIN
	        PRINT DATEADD(day,@num,@dt)
	        --PRINT CONVERT(VARCHAR(10),DATEADD(day,@num,@dt),120)

			UPDATE [User] SET UserName = UserName + CONVERT(varchar(100),@id) WHERE Id = @id
            FETCH NEXT FROM cursor1 INTO @id,@name
        END
CLOSE cursor1                 --关闭游标
DEALLOCATE cursor1            --删除游标

*
insert into select 插入数据，将一张表的所有数据插入另一张表 

--将 trans_debug_log 表中的所有地址都插入到 trans_debug_log 表中
insert into trans_debug_log (msg,created_time)
select msg,created_time from trans_debug_log
go

--将 sys_user 表中的所有地址都插入到 trans_debug_log 表中
insert into trans_debug_log (hospital_code,msg,created_time) 
select valid,user_name,created_time from sys_user

*
SQL IF 判断

DECLARE @num int
DECLARE @id int
SET @num = 1
WHILE (@num <= 61)
	BEGIN
		SET DATEFIRST 1
		IF(DATEPART(weekday, GETDATE() + @num) > 5)
			BEGIN
				INSERT [dbo].[Operate]
				([Title],[Strategy],[Answer],[WeekDay],[CreatedDate])
				VALUES 
				('','','',DATENAME(weekday, GETDATE() + @num),GETDATE() + @num)
			END
		ELSE
			BEGIN
				INSERT [dbo].[Operate]
				([Title],[Strategy],[Answer],[WeekDay],[CreatedDate])
				VALUES 
				('','','',DATENAME(weekday, GETDATE() + @num),GETDATE() + @num)
				
				INSERT [dbo].[Operate]
				([Title],[Strategy],[Answer],[WeekDay],[CreatedDate])
				VALUES 
				('','','',DATENAME(weekday, GETDATE() + @num),GETDATE() + @num)
			END
		SET @id = @@IDENTITY			
		SET @num = @num + 1
	END
GO

*
LEFT

--输出：Welcome
select left('Welcome to China',7)

RIGHT

--输出：China
select right('Welcome to China',5)

SUBSTRING

--输出：Welcome
select substring('Welcome to China',1,7)

--输出：China
select substring('Welcome to China',12,len('Welcome'))

--真实案例【追加0】 begin
参考字符串：3000000q20201010090559

declare @priority varchar(1)
declare @time_stamp nvarchar(200)

select @priority = left(sort,1),@time_stamp = reverse(substring(reverse(sort),1,charindex('q',reverse(sort)) - 1)) from trans_queue where queue_code = 17

select @priority,@time_stamp
--真实案例【追加0】 end

--真实案例【去除0】 begin
declare @covering nvarchar(200)
declare @time_stamp nvarchar(200)

select 
@covering = reverse(substring(reverse(sort),charindex('q',reverse(sort)) + 1,500)),
@time_stamp = reverse(substring(reverse(sort),1,charindex('q',reverse(sort)) - 1))
from trans_queue where queue_code = 17

set @covering = right(@covering,LEN(@covering)-1) + '0'

select @covering
--真实案例【去除0】 end

--输出：月光疑是
select substring(a.a, charindex('明', a.a) + 1, charindex('地', a.a) - charindex('明', a.a) - 1)
from(select '床前明月光疑是地上bai霜' as a )a
GO

--输出：月光疑是
declare @str nvarchar(100)
set @str = '床前明月光疑是地上bai霜'
select substring(@str, charindex('明', @str) + 1, charindex('地', @str) - charindex('明', @str) - 1)
GO

--输出：000000
declare @str nvarchar(100)
set @str = '3s000000e2020654897522'
select substring(@str, charindex('s', @str) + 1, charindex('e', @str) - charindex('s', @str) - 1)
GO

--输出：100
select substring(a.a, 0, charindex('/', a.a))
from(select '100/200' as a )a

--输出：200
select substring(a.a, charindex('/', a.a) + 1, 5)
from(select '100/200' as a )a

CHARINDEX
*
*
*
*
*