scanf and printf in SQL Server

There are two extended stored procedures in SQL Server that implement the scanf and printf features, and use them appropriately to simplify the SQL code significantly when extracting and stitching strings.

1, xp_sscanf, use it to decompose a relatively fixed string, which is good for tired of using a bunch of substring and charindex friends. For example, a few days ago a post on how to decompose the IP address, relatively concise and common code should be the following

Copy Code code 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%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, with which you can splice a string without worrying too many plus signs are difficult to control, such as a dynamic execution of SQL statements stored procedures

Copy Code code as follows:


if (object_id (' P_select ') is not null)


drop proc P_select


Go


CREATE proc P_select (@ tb varchar (m), @ cols varchar (MB), @ Wherecol varchar (MB), @ 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 2009-08-18 03:01:51.153


*/