我正在处理一个示例查询,这是唯一的 outcomes
表:
**ship**
Bismarck
California
California
Duke of York
Fuso
Hood
King George V
Kirishima
Prince of Wales
Rodney
Schamhorst
South Dakota
Tennessee
Washington
West Virginia
Yamashiro
我正在尝试将字符串中第一个和最后一个空格之间的字符替换为*
。我已经得到了下面的代码,它是正确的:
select
left(ship, charindex(' ', ship) - 1) + ' ' +
replicate('*', charindex(' ', substring(ship, charindex(' ', ship) + 1, len(ship))) + 1 -2) + ' ' +
reverse(left(reverse(ship), charindex(' ', reverse(ship)) - 1))
from outcomes
where charindex(' ', substring(ship, charindex(' ', ship) + 1, len(ship))) > 1;
代码可以运行,但我希望在用户定义的函数中创建一个表变量,以便于重复使用而不会遇到太多困难。我用来声明表变量的代码如下,并且是正确的:
declare @ship_outcome table
( final_work nvarchar(30)
)
insert into @ship_outcome (final_work)
select
left(ship, charindex(' ', ship) - 1) + ' ' +
replicate('*', charindex(' ', substring(ship, charindex(' ', ship) + 1, len(ship))) + 1 -2) + ' ' +
reverse(left(reverse(ship), charindex(' ', reverse(ship)) - 1))
from outcomes
where charindex(' ', substring(ship, charindex(' ', ship) + 1, len(ship))) > 1;
select * from @ship_outcome
问题在于,当我使用以下代码将其变为用户定义函数时:
CREATE FUNCTION dbo.shippad (@tbl nvarchar(30))
RETURNS TABLE
AS
RETURN
declare @ship_outcome table
(
final_work nvarchar(30)
)
insert into @ship_outcome
select
left(ship, charindex(' ', ship) - 1) + ' ' +
replicate('*', charindex(' ', substring(ship, charindex(' ', ship) + 1, len(ship))) + 1 -2) + ' ' +
reverse(left(reverse(ship), charindex(' ', reverse(ship)) - 1))
from outcomes
where charindex(' ', substring(ship, charindex(' ', ship) + 1, len(ship))) > 1
select * from @ship_outcome
;
系统显示“在关键字 'declare' 附近有语法错误。”
我无法弄清楚我错在了哪里,请帮忙解决。