There are two extended stored procedures to implement the Scanf and Printf. When a string were extracted and spliced, it would simplify significantly the SQL codes to use them appropriately.
1.xp_sscanf . It can be used to split the character string whose format is relatively fixed, which is a good idea for the guys tired of the substring and the charindex .There is a example for how to split the IP address.The relatively simple and common code is below:
if(object_id(’f_getip’) is not null)
drop function f_getip
go
create function dbo.f_getip(@ip varchar(100))
returns @t table(a int,b int,c int,d int)
as
begin
set @ip=replace(@ip,’.',’ ‘)
declare
@s1 varchar(3) , @s2 varchar(3),
@s3 varchar(3) , @s4 varchar(3)
exec xp_sscanf @ip,’%s %s %s %s’,@s1 output,@s2 output,@s3 output,@s4 output
insert into @t select @s1,@s2,@s3,@s4
return
end
go
select * from dbo.f_getip(’192.168.0.1′ )
go
/*
a b c d
———– ———– ———– ———–
192 168 0 1
*/
2. xp_sprintf .
The character string could be spliced without worry that the plus and quotation marks were too many to control .For example, a stored procedure for dynamic implementation of SQL statement:
if(object_id(’p_select’) is not null)
drop proc p_select
go
create proc p_select(@tb varchar(100),@cols varchar(100),@wherecol varchar(100),@value varchar(100))
as
begin
declare @s varchar(8000)
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’
/*