SQLServer中有兩個擴展存儲過程實現Scanf和Printf功能恰當的使用它們可以在提取和拼接字符串時大幅度簡化SQL代碼
xp_sscanf用它可以分解格式相對固定的字符串這對於厭倦使用一堆substring和charindex的朋友來說不錯比如前幾天的一個帖子中提出的如何分解ip地址相對簡練且通用的代碼應該是下面這樣
復制代碼 代碼如下:
if (object_id (
f_getip
) is not null )
drop function f_getip
go
create function dbo
f_getip (@ ip varchar (
))
returns @ t table (a int
b int
c int
d int )
as
begin
set @ ip = replace (@ ip
)
declare
@ s
varchar (
)
@ s
varchar (
)
@ s
varchar (
)
@ s
varchar (
)
exec xp_sscanf @ ip
%s %s %s %s
@ s
output
@ s
output
@ s
output
@ s
output
insert into @ t select @ s
@ s
@ s
@ s
return
end
go
select * from dbo
f_getip (
)
go
/*
a b c d
*/
xp_sprintf用它可以拼接出一個字符串而不用擔心過多的加號很引號難以控制比如一個動態執行sql語句的存儲過程
復制代碼 代碼如下:
if (object_id (
p_select
) is not null )
drop proc p_select
go
create proc p_select (@ tb varchar (
)
@ cols varchar (
)
@ wherecol varchar (
)
@ value varchar (
))
as
begin
declare @ s varchar (
)
exec xp_sprintf @ s output
select %s from %s where %s=
%s
@ cols
@ tb
@ wherecol
@ value
exec (@ s)
end
go
exec p_select
sysobjects
id
xtype
crdate
name
p_select
/*
id xtype crdate
P
:
:
*/
From:http://tw.wingwit.com/Article/program/MySQL/201311/29560.html
