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
*
*
*
*
*