在一个表存在，另一个表不存在的数据
SQL查询~ 存在一个表而不在另一个表中的数据 - jameshappy - 博客园
实例
检验身份证是否合法(数据库函数)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <GAOFEI>
-- Create date: <2022.03.08>
-- Description: <校验身份证是否合法>
-- =============================================
create function [dbo].[udf_IsvaildIDCard](
@IDCardNo varchar(50) = ''
)
returns bit
as
/*********************************************************************************************************
函数名称：udf_IsvaildIDCard()
参数：@@IDCardNo string 身份证号码
返回值： bit 是否有效（0：无效，1：有效）
功能描述：判断身份证号码是否合法
备注：目前中国的身份证号码有18位和15位。
1，18位身份证号码的组成：6位地区编码 + 8位出生日期 + 3位编号(奇男偶女) + 1位校验码
2，15位身份证号码的组成：6位地区编码 + 6位出生日期 + 3位编号(奇男偶女)
*********************************************************************************************************/
BEGIN
declare @Lenght int,@Loop int,@Sum int,@SingleChar char
set @Sum = 0
if @IDCardNo is null or @IDCardNo = null or LTRIM(RTRIM(@IdCardNo)) = '' begin
return 0
end
set @Lenght = LEN(@IDCardNo)
-- 判断位数
if @Lenght <> 18 and @Lenght <> 15 begin
return 0
end
-- 1.校验18位
if @Lenght = 18 begin
if ISNUMERIC(LEFT(@IDCardNo,17)) = 0 begin
return 0
end
if ISDATE(SUBSTRING(@IDCardNo,7,4) + '-'+ SUBSTRING(@IDCardNo,11,2) + '-' + SUBSTRING(@IDCardNo,13,2)) = 0 begin
return 0
end
set @Loop = 17
while (@Loop >= 1) begin
set @Sum = @Sum + CONVERT(int,SUBSTRING(@IDCardNo,@Loop,1)) * (POWER(2,(18 - @Loop))% 11)
set @Loop = @Loop - 1
end
set @Loop = @Sum % 11
if @Loop = 0 begin
set @SingleChar = '1'
end
else if @Loop = 1 begin
set @SingleChar = '0'
end
else if @Loop = 2 begin
set @SingleChar = 'X'
end
else begin
set @SingleChar = CONVERT(varchar(2),(12 - @Loop))
end
if LOWER(RIGHT(@IDCardNo,1)) <> LOWER(@SingleChar) begin
return 0
end
end
-- 2.校验15位
else if @Lenght = 15 begin
if ISNUMERIC(@IDCardNo) = 0 begin
return 0
end
if ISDATE('19' + SUBSTRING(@IDCardNo,7,2) + '-' + SUBSTRING(@IDCardNo,9,2) + '-' + SUBSTRING(@IDCardNo,11,2)) = 0 begin
return 0
end
end
return 1 -- 合法
END
GO
数字转换varchar，并可以保留小数位
注意：是直接舍弃，比如12.356 得到结果是 12.35
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_NumberFormat]
(
@number decimal(18,4) ,
@points INT
)
RETURNS VARCHAR(8000)
AS
/*
函数名称：fn_NumberFormat()
参数：@number decimal(18,4) 需要转换的数字
@points INT 保留小数位数
返回值：varchar 返回转换位varchar并保留小数位数之后的数据
功能：返回转换为varchar并保留小数位数之后的数据
*/
BEGIN
set @number = ISnull(@number,0.00) --若是null，返回0.00
RETURN LEFT(CONVERT(VARCHAR(MAX),@number), CHARINDEX('.', CONVERT(VARCHAR(MAX),@number))+@points)
END
GO汉字URL编码
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[UrlCode](@url varchar(max))
RETURNS varchar(max)
AS
BEGIN
DECLARE @count int, @c char(1), @cenc char(2), @i int, @urlReturn varchar(max)
,@s varchar(50), @code1 bigint ,@code2 bigint,@h bigint,@m bigint,@l bigint;
SET @count = Len(@url)
SET @i = 1
SET @urlReturn = ''
WHILE (@i <= @count)
BEGIN
SET @s = substring(@url, @i, 1)
if @s like '[0-9]' or @s like '[a-z]' or @s like '[A-Z]'
begin
SET @s = @s
end
else if UniCode(@s)<128
begin
select @code1=UniCode(@s)
select @s=[dbo].[varbin2hexstr](@code1)
set @s='%'+right(@s,2)
end else begin
select @code1=UniCode(@s),@l=UniCode(@s) &0x3F ,@m=UniCode(@s) &0xFC0 ,@h=UniCode(@s) &0xF000
select @code2=@l+ 0x80 + @m*4 + 0x8000 + @h*16 + 0xE00000
select @s=[dbo].[varbin2hexstr](@code2)
set @s='%'+left(right(@s,6),2)+'%'+left(right(@s,4),2)+'%'+right(@s,2)
end
select @urlReturn=@urlReturn+@s
SET @i = @i +1
END
RETURN @urlReturn
END
GO
汉字URL解码
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[UrlDecode](@url varchar(max))
RETURNS varchar(max)
AS
BEGIN
DECLARE @count int, @c char(1), @cenc char(2), @i int, @urlReturn varchar(max)
,@s varchar(50), @code1 bigint ,@code2 bigint,@h bigint,@m bigint,@l bigint;
SET @count = Len(@url)
SET @i = 1
SET @urlReturn = ''
WHILE (@i <= @count)
BEGIN
SET @c = substring(@url, @i, 1)
if @c='%'
begin
if (substring(@url, @i+1, 1) LIKE '[0-9]') and (@i+2<=@count)
begin
set @s = substring(@url, @i, 3)
select @code1=CONVERT(bigint, CONVERT(varbinary,CAST('0x'+replace(@s,'%','') AS char ) , 1))
select @urlReturn=@urlReturn+nchar(@code1)
SET @i = @i + 3
end else if (@i+8<=@count)
begin
set @s = substring(@url, @i, 9)
select @code1=CONVERT(bigint, CONVERT(varbinary,CAST('0x'+replace(@s,'%','') AS char ) , 1))
select @h=@code1&0xF0000,@m=@code1&0x3F00,@l=@code1&0x3F
select @code2=(@h/16) + (@m/4) + @l
select @urlReturn=@urlReturn+nchar(@code2)
SET @i = @i + 9
end else
break
end else begin
SET @urlReturn=@urlReturn+@c
SET @i = @i +1
end
END
RETURN @urlReturn
END
GO
解析JSON
-- 标量值函数
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[parseJSON_DZPJ] ( @JSON NVARCHAR(MAX) )
RETURNS @hierarchy TABLE
(
element_id INT IDENTITY(1, 1)
NOT NULL , /* internal surrogate primary key gives the order of parsing and the list order */
sequenceNo [INT] NULL , /* the place in the sequence for the element */
parent_ID INT ,/* if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */
Object_ID INT ,/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */
NAME NVARCHAR(2000) ,/* the name of the object */
StringValue NVARCHAR(MAX) NOT NULL ,/*the string representation of the value of the element. */
ValueType VARCHAR(10) NOT NULL /* the declared type of the value represented as a string in StringValue*/
)
AS
BEGIN
DECLARE @FirstObject INT , --the index of the first open bracket found in the JSON string
@OpenDelimiter INT ,--the index of the next open bracket found in the JSON string
@NextOpenDelimiter INT ,--the index of subsequent open bracket found in the JSON string
@NextCloseDelimiter INT ,--the index of subsequent close bracket found in the JSON string
@Type NVARCHAR(10) ,--whether it denotes an object or an array
@NextCloseDelimiterChar CHAR(1) ,--either a '}' or a ']'
@Contents NVARCHAR(MAX) , --the unparsed contents of the bracketed expression
@Start INT , --index of the start of the token that you are parsing
@end INT ,--index of the end of the token that you are parsing
@param INT ,--the parameter at the end of the next Object/Array token
@EndOfName INT ,--the index of the start of the parameter at end of Object/Array token
@token NVARCHAR(MAX) ,--either a string or object
@value NVARCHAR(MAX) , -- the value as a string
@SequenceNo INT , -- the sequence number within a list
@name NVARCHAR(200) , --the name as a string
@parent_ID INT ,--the next parent ID to allocate
@lenJSON INT ,--the current length of the JSON String
@characters NCHAR(36) ,--used to convert hex to decimal
@result BIGINT ,--the value of the hex symbol being parsed
@index SMALLINT ,--used for parsing the hex value
@Escape INT; --the index of the next escape character
DECLARE @Strings TABLE /* in this temporary table we keep all strings, even the names of the elements, since they are 'escaped' in a different way, and may contain, unescaped, brackets denoting objects or lists. These are replaced in the JSON string by tokens representing the string */
(
String_ID INT IDENTITY(1, 1) ,
StringValue NVARCHAR(MAX)
);
SELECT--initialise the characters to convert hex to ascii
@characters = '0123456789abcdefghijklmnopqrstuvwxyz' ,
@SequenceNo = 0 , --set the sequence no. to something sensible.
/* firstly we process all strings. This is done because [{} and ] aren't escaped in strings, which complicates an iterative parse. */
@parent_ID = 0;
WHILE 1 = 1 --forever until there is nothing more to do
BEGIN
SELECT @Start = PATINDEX('%[^a-zA-Z]["]%',
@JSON COLLATE SQL_Latin1_General_CP850_BIN);--next delimited string
IF @Start = 0
BREAK; --no more so drop through the WHILE loop
IF SUBSTRING(@JSON, @Start + 1, 1) = '"'
BEGIN --Delimited Name
SET @Start = @Start + 1;
SET @end = PATINDEX('%[^\]["]%',
RIGHT(@JSON,
LEN(@JSON + '|') - @Start));
END;
IF @end = 0 --no end delimiter to last string
BREAK; --no more
SELECT @token = SUBSTRING(@JSON, @Start + 1, @end - 1);
--now put in the escaped control characters
SELECT @token = REPLACE(@token, FromString, ToString)
FROM ( SELECT '\"' AS FromString ,
'"' AS ToString
UNION ALL
SELECT '\\' ,
'\'
UNION ALL
SELECT '\/' ,
'/'
UNION ALL
SELECT '\b' ,
CHAR(08)
UNION ALL
SELECT '\f' ,
CHAR(12)
UNION ALL
SELECT '\n' ,
CHAR(10)
UNION ALL
SELECT '\r' ,
CHAR(13)
UNION ALL
SELECT '\t' ,
CHAR(09)
) substitutions;
SELECT @result = 0 ,
@Escape = 1;
--Begin to take out any hex escape codes
WHILE @Escape > 0
BEGIN
SELECT @index = 0 ,
--find the next hex escape sequence
@Escape = PATINDEX('%\x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]%',
@token);
IF @Escape > 0 --if there is one
BEGIN
WHILE @index < 4 --there are always four digits to a \x sequence
BEGIN
SELECT --determine its value
@result = @result + POWER(16,
@index)
* ( CHARINDEX(SUBSTRING(@token,
@Escape + 2 + 3
- @index, 1),
@characters) - 1 ) ,
@index = @index + 1;
END;
-- and replace the hex sequence by its unicode value
SELECT @token = STUFF(@token, @Escape, 6,
NCHAR(@result));
END;
END;
--now store the string away
INSERT INTO @Strings
( StringValue )
SELECT @token;
-- and replace the string with a token
SELECT @JSON = STUFF(@JSON, @Start, @end + 1,
'@string'
+ CONVERT(NVARCHAR(5), @@identity));
END;
-- all strings are now removed. Now we find the first leaf.
WHILE 1 = 1 --forever until there is nothing more to do
BEGIN
SELECT @parent_ID = @parent_ID + 1;
--find the first object or list by looking for the open bracket
SELECT @FirstObject = PATINDEX('%[{[[]%',
@JSON COLLATE SQL_Latin1_General_CP850_BIN);--object or array
IF @FirstObject = 0
BREAK;
IF ( SUBSTRING(@JSON, @FirstObject, 1) = '{' )
SELECT @NextCloseDelimiterChar = '}' ,
@Type = 'object';
ELSE
SELECT @NextCloseDelimiterChar = ']' ,
@Type = 'array';
SELECT @OpenDelimiter = @FirstObject;
WHILE 1 = 1 --find the innermost object or list...
BEGIN
SELECT @lenJSON = LEN(@JSON + '|') - 1;
--find the matching close-delimiter proceeding after the open-delimiter
SELECT @NextCloseDelimiter = CHARINDEX(@NextCloseDelimiterChar,
@JSON,
@OpenDelimiter
+ 1);
--is there an intervening open-delimiter of either type
SELECT @NextOpenDelimiter = PATINDEX('%[{[[]%',
RIGHT(@JSON,
@lenJSON
- @OpenDelimiter)COLLATE SQL_Latin1_General_CP850_BIN);--object
IF @NextOpenDelimiter = 0
BREAK;
SELECT @NextOpenDelimiter = @NextOpenDelimiter
+ @OpenDelimiter;
IF @NextCloseDelimiter < @NextOpenDelimiter
BREAK;
IF SUBSTRING(@JSON, @NextOpenDelimiter, 1) = '{'
SELECT @NextCloseDelimiterChar = '}' ,
@Type = 'object';
ELSE
SELECT @NextCloseDelimiterChar = ']' ,
@Type = 'array';
SELECT @OpenDelimiter = @NextOpenDelimiter;
END;
---and parse out the list or name/value pairs
SELECT @Contents = SUBSTRING(@JSON, @OpenDelimiter + 1,
@NextCloseDelimiter
- @OpenDelimiter - 1);
SELECT @JSON = STUFF(@JSON, @OpenDelimiter,
@NextCloseDelimiter - @OpenDelimiter + 1,
'@' + @Type
+ CONVERT(NVARCHAR(5), @parent_ID));
WHILE ( PATINDEX('%[A-Za-z0-9@+.e]%',
@Contents COLLATE SQL_Latin1_General_CP850_BIN) ) <> 0
BEGIN
IF @Type = 'Object' --it will be a 0-n list containing a string followed by a string, number,boolean, or null
BEGIN
SELECT @SequenceNo = 0 ,
@end = CHARINDEX(':', ' ' + @Contents);--if there is anything, it will be a string-based name.
SELECT @Start = PATINDEX('%[^A-Za-z@][@]%',
' ' + @Contents);--AAAAAAAA
SELECT @token = SUBSTRING(' ' + @Contents,
@Start + 1,
@end - @Start - 1) ,
@EndOfName = PATINDEX('%[0-9]%',
@token COLLATE SQL_Latin1_General_CP850_BIN) ,
@param = RIGHT(@token,
LEN(@token)
- @EndOfName + 1);
SELECT @token = LEFT(@token, @EndOfName - 1) ,
@Contents = RIGHT(' ' + @Contents,
LEN(' ' + @Contents
+ '|') - @end
- 1);
SELECT @name = StringValue
FROM @Strings
WHERE String_ID = @param; --fetch the name
END;
ELSE
SELECT @name = NULL ,
@SequenceNo = @SequenceNo + 1;
SELECT @end = CHARINDEX(',', @Contents);-- a string-token, object-token, list-token, number,boolean, or null
IF @end = 0
SELECT @end = PATINDEX('%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%',
@Contents + ' ') + 1;
SELECT @Start = PATINDEX('%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%',
' ' + @Contents);
--select @start,@end, LEN(@contents+'|'), @contents
SELECT @value = RTRIM(SUBSTRING(@Contents, @Start,
@end - @Start)) ,
@Contents = RIGHT(@Contents + ' ',
LEN(@Contents + '|') - @end);
IF SUBSTRING(@value, 1, 7) = '@object'
INSERT INTO @hierarchy
( NAME ,
sequenceNo ,
parent_ID ,
StringValue ,
Object_ID ,
ValueType
)
SELECT @name ,
@SequenceNo ,
@parent_ID ,
SUBSTRING(@value, 8, 5) ,
SUBSTRING(@value, 8, 5) ,
'object';
ELSE
IF SUBSTRING(@value, 1, 6) = '@array'
INSERT INTO @hierarchy
( NAME ,
sequenceNo ,
parent_ID ,
StringValue ,
Object_ID ,
ValueType
)
SELECT @name ,
@SequenceNo ,
@parent_ID ,
SUBSTRING(@value, 7, 5) ,
SUBSTRING(@value, 7, 5) ,
'array';
ELSE
IF SUBSTRING(@value, 1, 7) = '@string'
INSERT INTO @hierarchy
( NAME ,
sequenceNo ,
parent_ID ,
StringValue ,
ValueType
)
SELECT @name ,
@SequenceNo ,
@parent_ID ,
cast(StringValue as text) ,
'string'
FROM @Strings
WHERE String_ID = SUBSTRING(@value,
8, 5);
ELSE
IF @value IN ( 'true', 'false' )
INSERT INTO @hierarchy
( NAME ,
sequenceNo ,
parent_ID ,
StringValue ,
ValueType
)
SELECT @name ,
@SequenceNo ,
@parent_ID ,
@value ,
'boolean';
ELSE
IF @value = 'null'
INSERT INTO @hierarchy
( NAME ,
sequenceNo ,
parent_ID ,
StringValue ,
ValueType
)
SELECT @name ,
@SequenceNo ,
@parent_ID ,
@value ,
'null';
ELSE
IF PATINDEX('%[^0-9]%',
@value COLLATE SQL_Latin1_General_CP850_BIN) > 0
INSERT INTO @hierarchy
( NAME ,
sequenceNo ,
parent_ID ,
StringValue ,
ValueType
)
SELECT
@name ,
@SequenceNo ,
@parent_ID ,
@value ,
'real';
ELSE
INSERT INTO @hierarchy
( NAME ,
sequenceNo ,
parent_ID ,
StringValue ,
ValueType
)
SELECT
@name ,
@SequenceNo ,
@parent_ID ,
@value ,
'int';
IF @Contents = ' '
SELECT @SequenceNo = 0;
END;
END;
INSERT INTO @hierarchy
( NAME ,
sequenceNo ,
parent_ID ,
StringValue ,
Object_ID ,
ValueType
)
SELECT '-' ,
1 ,
NULL ,
'' ,
@parent_ID - 1 ,
@Type;
--
RETURN;
END;
GO
在存储过程中使用解析JSON
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[sfp_dzpj_parse_rtn] @para varchar(max),@out_para varchar(max) output
AS
BEGIN
-- 处理数据使用变量
declare @noise varchar(50),@data varchar(max),@result varchar(10),@message varchar(max),@fph varchar(50),@busDateTime varchar(20)
-- 获取数据
select @noise = StringValue from dbo.parseJSON_DZPJ(@para) where NAME = 'noise' -- 获取noise(即唯一号)
select @data = StringValue from dbo.parseJSON_DZPJ(@para) where NAME = 'data' -- 获取电子票据接口返回值（全部）
select @fph = StringValue from dbo.parseJSON_DZPJ(@para) where NAME = 'busNo' -- 发票号
select @busDateTime = StringValue from dbo.parseJSON_DZPJ(@para) where NAME = 'busDateTime' -- 发票上传时的时间
-- base64()解码
SELECT @data = CONVERT(nvarchar(max), dbo.utf8_to_ucs2(dbo.base64_decode(@data)))
select @result = StringValue from dbo.parseJSON_DZPJ(@data) where NAME = 'result' -- 返回结果标识
select @message = StringValue from dbo.parseJSON_DZPJ(@data) where NAME = 'message' -- 返回结果内容
SELECT @message = CONVERT(nvarchar(max), dbo.utf8_to_ucs2(dbo.base64_decode(@message)))
-- 记录日志
insert into DZPJ_LOG_SFP(fph,result,message,noise)
values(@fph,@result,@message,@noise)
if @result <> 'S0000' begin -- 有错误
set @out_para = @message
return -- 有错误，直接返回
end
declare @billBatchCode varchar(50),@billNo varchar(20),@random varchar(20),@createTime varchar(20),@billQRCode varchar(max),@pictureUrl varchar(max),@pictureNetUrl varchar(max)
select @billBatchCode = StringValue from dbo.parseJSON_DZPJ(@message) where NAME = 'billBatchCode' -- 电子票据代码
select @billNo = StringValue from dbo.parseJSON_DZPJ(@message) where NAME = 'billNo' -- 电子票据号码
select @random = StringValue from dbo.parseJSON_DZPJ(@message) where NAME = 'random' -- 电子校验码
select @createTime = StringValue from dbo.parseJSON_DZPJ(@message) where NAME = 'createTime' -- 电子票据生成时间
select @billQRCode = StringValue from dbo.parseJSON_DZPJ(@message) where NAME = 'billQRCode' -- 电子票据二维码图片数据
select @pictureUrl = StringValue from dbo.parseJSON_DZPJ(@message) where NAME = 'pictureUrl' -- 电子票据H5页面URL
select @pictureNetUrl = StringValue from dbo.parseJSON_DZPJ(@message) where NAME = 'pictureNetUrl' -- 电子票据外网H5页面URL
-- 成功后，保存数据到表DZPJ_MZFP中，并更改发票是否生成电子票据的状态
update t03dr_fp set if_create_dzpj = 1 where fph = @fph --更改发票是否生成电子票据的状态
insert into DZPJ_MZFP(wyh,interface,fph,billBatchCode,billNo,random,createTime,billQRCode,pictureUrl,pictureNetUrl,czy,busDateTime)
values(@noise,'invEBillRegistration',@fph,@billBatchCode,@billNo,@random,@createTime,dbo.base64_decode(@billQRCode),@pictureUrl,@pictureNetUrl,'zzj01-自助机',@busDateTime)
set @out_para = @billQRCode
END
获取日期数字
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[fn_dzpj_get_noise]()
returns varchar(50)
as
begin
declare @dh varchar(50)
set @dh = convert(varchar(100),Sysdatetime())
/*1.第一次把'-'替换为''*/
set @dh = REPLACE(@dh,'-','')
/*2.第二次把' '替换为''*/
set @dh = REPLACE(@dh,' ','')
/*3.第三次把':'替换为''*/
set @dh = REPLACE(@dh,':','')
/*4.第四次把'.'替换为''*/
return REPLACE(@dh,'.','')
end获取字符串中的全部数字
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--获取字符串中的数字
CREATE FUNCTION [dbo].[Fun_GetNum]
( @Str VARCHAR(200) )
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @Start INT;
DECLARE @End INT;
DECLARE @Part NVARCHAR(MAX)
SET @Start = PATINDEX('%[.,0-9]%', @Str);
SET @End = PATINDEX('%[.,0-9]%',SUBSTRING(@Str, @Start+1,LEN(@Str) - @Start));
SET @Part = SUBSTRING(@Str,@Start,1)
WHILE @End >0
BEGIN
SET @Start = @start+@End
SET @Part = @Part+SUBSTRING(@Str,@Start,1)
SET @End = PATINDEX('%[.,0-9]%',SUBSTRING(@Str, @Start+1,LEN(@Str) - @Start));
END
if LEN(@Part) = 0 begin --若没有数字（即传入的不含数字），返回0
set @Part = '0'
end
RETURN @Part;
END;
GO获取 * 左或右面的数字
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--根据符号(*)获取左右【数字】数据
CREATE FUNCTION [dbo].[Fun_GetNum_BY_FH]
( @sort VARCHAR(10),@Str VARCHAR(200) )
RETURNS VARCHAR(100)
AS
BEGIN
--判断有没有 * 没有返回 空
if charindex('*',@Str) = 0 begin
declare @rtn VARCHAR(100)
select @rtn = dbo.Fun_GetNum(@Str)
return @rtn
end
if @sort = 'left' begin
---将*号左面的字符串截取出来,如果规格中没有*号会报错
set @Str = substring(@Str,1,charindex('*',@Str)-1)
end
else if @sort = 'right' begin
---将*号右面的的字符串截取出来,如果规格中没有*号会报错
set @Str = right(@Str,len(@Str) - charindex('*',@Str))
end
else begin
return ''
end
DECLARE @Start INT;
DECLARE @End INT;
DECLARE @Part NVARCHAR(MAX)
SET @Start = PATINDEX('%[.,0-9]%', @Str);
SET @End = PATINDEX('%[.,0-9]%',SUBSTRING(@Str, @Start+1,LEN(@Str) - @Start));
SET @Part = SUBSTRING(@Str,@Start,1)
WHILE @End >0
BEGIN
SET @Start = @start+@End
SET @Part = @Part+SUBSTRING(@Str,@Start,1)
SET @End = PATINDEX('%[.,0-9]%',SUBSTRING(@Str, @Start+1,LEN(@Str) - @Start));
END
RETURN @Part;
END;
GO获取字符串中的全部字符
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--获取字符串中的字符
CREATE FUNCTION [dbo].[Fun_GetStr]
( @Str VARCHAR(200) )
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @Start INT;
DECLARE @End INT;
DECLARE @Part NVARCHAR(MAX)
SET @Start = PATINDEX('%[a-z,A-Z,吖-座]%', @Str);
SET @End = PATINDEX('%[a-z,A-Z,吖-座]%',SUBSTRING(@Str, @Start+1,LEN(@Str) - @Start));
SET @Part = SUBSTRING(@Str,@Start,1)
WHILE @End >0
BEGIN
SET @Start = @start+@End
SET @Part = @Part+SUBSTRING(@Str,@Start,1)
SET @End = PATINDEX('%[a-z,A-Z,吖-座]%',SUBSTRING(@Str, @Start+1,LEN(@Str) - @Start));
END
if LEN(@Part) = 0 begin --若没有数字（即传入的不含数字），返回0
set @Part = '0'
end
RETURN @Part;
END;
GO
获取 * 左或右面的数据
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--获取符号(*)左右数据
CREATE FUNCTION [dbo].[Fun_GetStr_BY_FH]
( @sort VARCHAR(10),@Str VARCHAR(200) )
RETURNS VARCHAR(100)
AS
BEGIN
--判断有没有 * 没有返回 空
if charindex('*',@Str) = 0 begin
return ''
end
if @sort = 'left' begin
---将*号左面的字符串截取出来,如果规格中没有*号会报错
set @Str = substring(@Str,1,charindex('*',@Str)-1)
end
else if @sort = 'right' begin
---将*号右面的的字符串截取出来,如果规格中没有*号会报错
set @Str = right(@Str,len(@Str) - charindex('*',@Str))
end
RETURN @Str;
END;
GO
base64解码
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[base64_decode]
(
@encoded_text varchar(max)
)
RETURNS varbinary(max)
AS
BEGIN
DECLARE
@output varbinary(max),
@block_start int,
@encoded_length int,
@decoded_length int,
@mapr binary(122)
IF LEN(@encoded_text) & 3 > 0
OR @encoded_text LIKE '%[^ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=]%' COLLATE Latin1_General_Bin
RETURN NULL
SET @output = 0x
-- The nth byte of @mapr contains the base64 value of the character with an ASCII value of n.
-- eg. 65th byte = 0x00 = 0 = value of 'A'
SET @mapr =
0xFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF -- 1-33
+ 0xFFFFFFFFFFFFFFFFFFFF3EFFFFFF3F3435363738393A3B3C3DFFFFFF00FFFFFF -- 33-64
+ 0x000102030405060708090A0B0C0D0E0F10111213141516171819FFFFFFFFFFFF -- 65-96
+ 0x1A1B1C1D1E1F202122232425262728292A2B2C2D2E2F30313233 -- 97-122
--get the number of blocks to be decoded
SET @encoded_length = LEN(@encoded_text)
SET @decoded_length = @encoded_length / 4 * 3
--for each block
SET @block_start = 1
WHILE @block_start < @encoded_length
BEGIN
--decode the block and add to output
--BINARY values between 1 and 4 bytes can be implicitly cast to INT
SET @output = @output +
CAST(CAST(
SUBSTRING(@mapr, ASCII(SUBSTRING(@encoded_text, @block_start , 1)), 1) * 262144
+ SUBSTRING(@mapr, ASCII(SUBSTRING(@encoded_text, @block_start + 1, 1)), 1) * 4096
+ SUBSTRING(@mapr, ASCII(SUBSTRING(@encoded_text, @block_start + 2, 1)), 1) * 64
+ SUBSTRING(@mapr, ASCII(SUBSTRING(@encoded_text, @block_start + 3, 1)), 1)
AS int) AS binary(3))
SET @block_start = @block_start + 4
END
IF RIGHT(@encoded_text, 2) = '=='
SET @decoded_length = @decoded_length - 2
ELSE IF RIGHT(@encoded_text, 1) = '='
SET @decoded_length = @decoded_length - 1
RETURN SUBSTRING(@output, 1, @decoded_length)
END
GO
5894
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
