Scanf and Printf in SQLServer

SQLServer has two extended stored procedures to implement Scanf and Printf functions. They can be used properly to simplify the SQL code while extracting and splicing strings.

1. xp_sscanf can be used to break down strings with relatively fixed formats, which is good for friends who are tired of using a bunch of substrings and charindex. For example, how to break down IP addresses in a post a few days ago, the relatively concise and common code should be as follows:

Copy codeThe Code is as follows:
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', @ 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 can be used to concatenate a string without worrying about the difficulty of controlling too many plus signs. For example, a stored procedure for dynamically executing SQL statements

Copy codeThe Code is as follows:
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'
/*
Id xtype crdate
---------------------------------------
898102240 P 03:01:51. 153
*/