Shulou(Shulou.com)05/31 Report--

How to analyze Scanf and Printf in SQLServer? I believe that many inexperienced people are at a loss about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

There are two extended stored procedures in SQLServer that implement Scanf and Printf functions, and their proper use can greatly simplify SQL code when extracting and concatenating strings. 1. Xp_sscanf, which can be used to decompose strings with relatively fixed formats, which is good for friends who are tired of using a bunch of substring and charindex. For example, in a post a few days ago, I proposed how to decompose ip addresses. The relatively concise and general code should be as follows

The code is as follows:

If (object_id ('fancigetip') is not null) drop function f_getip go create function dbo. F_getip (@ ip varchar) returns @ t table (an 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, which can be used to concatenate a string without worrying about too many plus marks and difficult to control in quotation marks, such as a stored procedure that dynamically executes sql statements

The code is as follows:

If (object_id ('paired 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 (8000) exec xp_sprintf @ s output

'select% s from% s where% slots.% slots.

@ cols, @ tb, @ wherecol, @ value exec (@ s) end go exec paired select`sysobjects', 'id,xtype,crdate',' name', `paired select` / * id xtype crdate

-898102240 P 2009-08-18 03 purl 01purl 51.153 * /

After reading the above, do you know how to analyze Scanf and Printf in SQLServer? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.