<rt id="bn8ez"></rt>
<label id="bn8ez"></label>
  • 

    • <span id="bn8ez"></span>
    

    <label id="bn8ez"><meter id="bn8ez"></meter></label>
    

    
    

    




    







												


        



    blogjava's web log
    

    blogjava's web log

    
...

    


    



  
    
			
    
				
					
	
    
		
    
			[數(shù)據(jù)庫]字符處理
		
    
		
    
		
    
				
				if
				?
				exists
				?(
				select
				?
				*
				?
				from
				?dbo.sysobjects?
				where
				?id?
				=
				?
				object_id
				(N
				'
				[dbo].[f_IP2Int]
				'
				)?
				and
				?xtype?
				in
				?(N
				'
				FN
				'
				,?N
				'
				IF
				'
				,?N
				'
				TF
				'
				))
    
				drop
				?
				function
				?
				[
				dbo
				]
				.
				[
				f_IP2Int
				]
				
						
    
						
				    
				GO
				
						
    
						
						
    
						
				    
				--
				1.?字符串IP地址轉(zhuǎn)換成IP數(shù)值函數(shù)。
				
						
    
						
				    
				CREATE
				?
				FUNCTION
				?dbo.f_IP2Int(
    
				@ip
				?
				char
				(
				15
				)
    )    
				RETURNS
				?
				bigint
				
						
    
						
				    
				AS
				
						
    
						
				    
				BEGIN
				
						
    
						????    
				DECLARE
				?
				@re
				?
				bigint
				
						
    
						????    
				SET
				?
				@re
				=
				0
				
						
    
						????    
				SELECT
				?
				@re
				=
				@re
				+
				LEFT
				(
				@ip
				,
				CHARINDEX
				(
				'
				.
				'
				,
				@ip
				+
				'
				.
				'
				)
				-
				1
				)
				*
				ID
    ????????,    
				@ip
				=
				STUFF
				(
				@ip
				,
				1
				,
				CHARINDEX
				(
				'
				.
				'
				,
				@ip
				+
				'
				.
				'
				),
				''
				)
    ????    
				FROM
				(
    ????????    
				SELECT
				?ID
				=
				CAST
				(
				16777216
				?
				as
				?
				bigint
				)
    ????????    
				UNION
				?
				ALL
				?
				SELECT
				?
				65536
				
						
    
						????????    
				UNION
				?
				ALL
				?
				SELECT
				?
				256
				
						
    
						????????    
				UNION
				?
				ALL
				?
				SELECT
				?
				1
				)a
    ????    
				RETURN
				(
				@re
				)
    
				END
				
						
    
						
				    
				GO
				
						
    
						
						
    
						
						
    
						
						
				    
				
				
						/*
						===========================================================
						*/
				
				
						
    
						
						
    
						
						
    
						
				    
				if
				?
				exists
				?(
				select
				?
				*
				?
				from
				?dbo.sysobjects?
				where
				?id?
				=
				?
				object_id
				(N
				'
				[dbo].[f_Int2IP]
				'
				)?
				and
				?xtype?
				in
				?(N
				'
				FN
				'
				,?N
				'
				IF
				'
				,?N
				'
				TF
				'
				))
    
				drop
				?
				function
				?
				[
				dbo
				]
				.
				[
				f_Int2IP
				]
				
						
    
						
				    
				GO
				
						
    
						
						
    
						
				    
				--
				1.?字符串IP地址轉(zhuǎn)換成IP數(shù)值函數(shù)。
				
						
    
						
				    
				CREATE
				?
				FUNCTION
				?dbo.f_Int2IP(
    
				@IP
				?
				bigint
				
						
    
						)    
				RETURNS
				?
				varchar
				(
				15
				)
    
				AS
				
						
    
						
				    
				BEGIN
				
						
    
						????    
				DECLARE
				?
				@re
				?
				varchar
				(
				15
				)
    ????    
				SET
				?
				@re
				=
				''
				
						
    
						????    
				SELECT
				?
				@re
				=
				@re
				+
				'
				.
				'
				+
				CAST
				(
				@IP
				/
				ID?
				as
				?
				varchar
				)
    ????????,    
				@IP
				=
				@IP
				%
				ID
    ????    
				from
				(
    ????????    
				SELECT
				?ID
				=
				CAST
				(
				16777216
				?
				as
				?
				bigint
				)
    ????????    
				UNION
				?
				ALL
				?
				SELECT
				?
				65536
				
						
    
						????????    
				UNION
				?
				ALL
				?
				SELECT
				?
				256
				
						
    
						????????    
				UNION
				?
				ALL
				?
				SELECT
				?
				1
				)a
    ????    
				RETURN
				(
				STUFF
				(
				@re
				,
				1
				,
				1
				,
				''
				))
    
				END
				
						
    
						
				    
				GO
				
						
    
						
				    
		
    
		
    
				
				if
				?
				exists
				?(
				select
				?
				*
				?
				from
				?dbo.sysobjects?
				where
				?id?
				=
				?
				object_id
				(N
				'
				[dbo].[f_SetStr]
				'
				)?
				and
				?xtype?
				in
				?(N
				'
				FN
				'
				,?N
				'
				IF
				'
				,?N
				'
				TF
				'
				))
    
				drop
				?
				function
				?
				[
				dbo
				]
				.
				[
				f_SetStr
				]
				
						
    
						
				    
				GO
				
						
    
						
						
    
						
				    
				--
				分段截取函數(shù)
				
						
    
						
				    
				CREATE
				?
				FUNCTION
				?dbo.f_SetStr(
    
				@s
				?
				varchar
				(
				8000
				),??????
				--
				包含數(shù)據(jù)項的字符串
				
						
    
						
				    
				@pos
				?
				int
				,?????????????
				--
				要更新的數(shù)據(jù)項的段
				
						
    
						
				    
				@value
				?
				varchar
				(
				100
				),???
				--
				更新后的值
				
						
    
						
				    
				@split
				?
				varchar
				(
				10
				)?????
				--
				數(shù)據(jù)分隔符
				
						
    
						
				    
				)
				RETURNS
				?
				varchar
				(
				8000
				)
    
				AS
				
						
    
						
				    
				BEGIN
				
						
    
						????    
				DECLARE
				?
				@splitlen
				?
				int
				,
				@p1
				?
				int
				,
				@p2
				?
				int
				
						
    
						????    
				SELECT
				?
				@splitlen
				=
				LEN
				(
				@split
				+
				'
				a
				'
				)
				-
				2
				,
    ????????    
				@p1
				=
				1
				,
    ????????    
				@p2
				=
				CHARINDEX
				(
				@split
				,
				@s
				+
				@split
				)
    ????    
				WHILE
				?
				@pos
				>
				1
				?
				AND
				?
				@p1
				<=
				@p2
				
						
    
						????????    
				SELECT
				?
				@pos
				=
				@pos
				-
				1
				,
    ????????????    
				@p1
				=
				@p2
				+
				@splitlen
				+
				1
				,
    ????????????    
				@p2
				=
				CHARINDEX
				(
				@split
				,
				@s
				+
				@split
				,
				@p1
				)
    ????    
				RETURN
				(
				CASE
				
						
    
						????????    
				WHEN
				?
				@p1
				<
				@p2
				?
				THEN
				?
				STUFF
				(
				@s
				,
				@p1
				,
				@p2
				-
				@p1
				,
				@value
				)
    ????????    
				WHEN
				?
				@p2
				>
				LEN
				(
				@s
				)?
				THEN
				?
				@s
				+
				@value
				
						
    
						????????    
				WHEN
				?
				@p2
				=
				@p1
				?
				THEN
				?
				STUFF
				(
				@s
				,
				@p1
				,
				0
				,
				@value
				)?
    ????????    
				ELSE
				?
				@s
				?
				END
				)
    
				END
				
						
    
						
				    
				GO
				
						
    
						
				    
		
    --各種字符串分函數(shù)


    --3.3.1?使用游標(biāo)法進(jìn)行字符串合并處理的示例。
    --    處理的數(shù)據(jù)
    CREATE?TABLE?tb(col1?varchar(10),col2?int)
    INSERT?tb?SELECT?'a',1
    UNION?ALL?SELECT?'a',2
    UNION?ALL?SELECT?'b',1
    UNION?ALL?SELECT?'b',2
    UNION?ALL?SELECT?'b',3

    --合并處理
    --    定義結(jié)果集表變量
    DECLARE?@t?TABLE(col1?varchar(10),col2?varchar(100))

    --定義游標(biāo)并進(jìn)行合并處理
    DECLARE?tb?CURSOR?LOCAL
    FOR
    SELECT?col1,col2?FROM?tb?ORDER?BY??col1,col2
    DECLARE?@col1_old?varchar(10),@col1?varchar(10),@col2?int,@s?varchar(100)
    OPEN?tb
    FETCH?tb?INTO?@col1,@col2
    SELECT?@col1_old=@col1,@s=''
    WHILE?@@FETCH_STATUS=0
    BEGIN
    ????    IF?@col1=@col1_old
    ????????    SELECT?@s=@s+','+CAST(@col2?as?varchar)
    ????    ELSE
    ????    BEGIN
    ????????    INSERT?@t?VALUES(@col1_old,STUFF(@s,1,1,''))
    ????????    SELECT?@s=','+CAST(@col2?as?varchar),@col1_old=@col1
    ????    END
    ????    FETCH?tb?INTO?@col1,@col2
    END
    INSERT?@t?VALUES(@col1_old,STUFF(@s,1,1,''))
    CLOSE?tb
    DEALLOCATE?tb
    --顯示結(jié)果并刪除測試數(shù)據(jù)
    SELECT?*?FROM?@t
    DROP?TABLE?tb
    /*--結(jié)果
    col1???????col2
    ----------?-----------
    a??????????1,2
    b??????????1,2,3
    --    */
    GO


    /*==============================================*/


    --3.3.2?使用用戶定義函數(shù)，配合SELECT處理完成字符串合并處理的示例
    --    處理的數(shù)據(jù)
    CREATE?TABLE?tb(col1?varchar(10),col2?int)
    INSERT?tb?SELECT?'a',1
    UNION?ALL?SELECT?'a',2
    UNION?ALL?SELECT?'b',1
    UNION?ALL?SELECT?'b',2
    UNION?ALL?SELECT?'b',3
    GO

    --合并處理函數(shù)
    CREATE?FUNCTION?dbo.f_str(@col1?varchar(10))
    RETURNS?varchar(100)
    AS
    BEGIN
    ????    DECLARE?@re?varchar(100)
    ????    SET?@re=''
    ????    SELECT?@re=@re+','+CAST(col2?as?varchar)
    ????    FROM?tb
    ????    WHERE?col1=@col1
    ????    RETURN(STUFF(@re,1,1,''))
    END
    GO

    --調(diào)用函數(shù)
    SELECT?col1,col2=dbo.f_str(col1)?FROM?tb?GROUP?BY?col1
    --刪除測試
    DROP?TABLE?tb
    DROP?FUNCTION?f_str
    /*--結(jié)果
    col1???????col2
    ----------?-----------
    a??????????1,2
    b??????????1,2,3
    --    */
    GO

    /*==============================================*/


    --3.3.3?使用臨時表實(shí)現(xiàn)字符串合并處理的示例
    --    處理的數(shù)據(jù)
    CREATE?TABLE?tb(col1?varchar(10),col2?int)
    INSERT?tb?SELECT?'a',1
    UNION?ALL?SELECT?'a',2
    UNION?ALL?SELECT?'b',1
    UNION?ALL?SELECT?'b',2
    UNION?ALL?SELECT?'b',3

    --合并處理
    SELECT?col1,col2=CAST(col2?as?varchar(100))?
    INTO?#t?FROM?tb
    ORDER?BY?col1,col2
    DECLARE?@col1?varchar(10),@col2?varchar(100)
    UPDATE?#t?SET?
    ????    @col2=CASE?WHEN?@col1=col1?THEN?@col2+','+col2?ELSE?col2?END,
    ????    @col1=col1,
    ????col2    =@col2
    SELECT?*?FROM?#t
    /*--更新處理后的臨時表
    col1???????col2
    ----------?-------------
    a??????????1
    a??????????1,2
    b??????????1
    b??????????1,2
    b??????????1,2,3
    --    */
    --得到最終結(jié)果
    SELECT?col1,col2=MAX(col2)?FROM?#t?GROUP?BY?col1
    /*--結(jié)果
    col1???????col2
    ----------?-----------
    a??????????1,2
    b??????????1,2,3
    --    */
    --刪除測試
    DROP?TABLE?tb,#t
    GO


    /*==============================================*/

    --3.3.4.1?每組?<=2?條記錄的合并
    --    處理的數(shù)據(jù)
    CREATE?TABLE?tb(col1?varchar(10),col2?int)
    INSERT?tb?SELECT?'a',1
    UNION?ALL?SELECT?'a',2
    UNION?ALL?SELECT?'b',1
    UNION?ALL?SELECT?'b',2
    UNION?ALL?SELECT?'c',3

    --合并處理
    SELECT?col1,
    ????col2    =CAST(MIN(col2)?as?varchar)
    ????????    +CASE?
    ????????????    WHEN?COUNT(*)=1?THEN?''
    ????????????    ELSE?','+CAST(MAX(col2)?as?varchar)
    ????????    END
    FROM?tb
    GROUP?BY?col1
    DROP?TABLE?tb
    /*--結(jié)果
    col1???????col2??????
    ----------?----------
    a??????????1,2
    b??????????1,2
    c??????????3
    --    */

    --3.3.4.2?每組?<=3?條記錄的合并
    --    處理的數(shù)據(jù)
    CREATE?TABLE?tb(col1?varchar(10),col2?int)
    INSERT?tb?SELECT?'a',1
    UNION?ALL?SELECT?'a',2
    UNION?ALL?SELECT?'b',1
    UNION?ALL?SELECT?'b',2
    UNION?ALL?SELECT?'b',3
    UNION?ALL?SELECT?'c',3

    --合并處理
    SELECT?col1,
    ????col2    =CAST(MIN(col2)?as?varchar)
    ????????    +CASE?
    ????????????    WHEN?COUNT(*)=3?THEN?','
    ????????????????    +CAST((SELECT?col2?FROM?tb?WHERE?col1=a.col1?AND?col2?NOT?IN(MAX(a.col2),MIN(a.col2)))?as?varchar)
    ????????????    ELSE?''
    ????????    END
    ????????    +CASE?
    ????????????    WHEN?COUNT(*)>=2?THEN?','+CAST(MAX(col2)?as?varchar)
    ????????????    ELSE?''
    ????????    END
    FROM?tb?a
    GROUP?BY?col1
    DROP?TABLE?tb
    /*--結(jié)果
    col1???????col2
    ----------?------------
    a??????????1,2
    b??????????1,2,3
    c??????????3
    --    */
    GO




    --各種字符串分函數(shù)

    if?exists?(select?*?from?dbo.sysobjects?where?id?=?object_id(N'[dbo].[f_splitSTR]')?and?xtype?in?(N'FN',?N'IF',?N'TF'))
    drop?function?[dbo].[f_splitSTR]
    GO

    --3.2.1?循環(huán)截取法
    CREATE?FUNCTION?f_splitSTR(
    @s???varchar(8000),???--待分拆的字符串
    @split?varchar(10)?????--數(shù)據(jù)分隔符
    )RETURNS?@re?TABLE(col?varchar(100))
    AS
    BEGIN
    ????    DECLARE?@splitlen?int
    ????    SET?@splitlen=LEN(@split+'a')-2
    ????    WHILE?CHARINDEX(@split,@s)>0
    ????    BEGIN
    ????????    INSERT?@re?VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
    ????????    SET?@s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
    ????    END
    ????    INSERT?@re?VALUES(@s)
    ????    RETURN
    END
    GO


    /*==============================================*/

    if?exists?(select?*?from?dbo.sysobjects?where?id?=?object_id(N'[dbo].[f_splitSTR]')?and?xtype?in?(N'FN',?N'IF',?N'TF'))
    drop?function?[dbo].[f_splitSTR]
    GO

    --3.2.3.1?使用臨時性分拆輔助表法
    CREATE?FUNCTION?f_splitSTR(
    @s???varchar(8000),??--待分拆的字符串
    @split?varchar(10)?????--數(shù)據(jù)分隔符
    )RETURNS?@re?TABLE(col?varchar(100))
    AS
    BEGIN
    ????    --創(chuàng)建分拆處理的輔助表(用戶定義函數(shù)中只能操作表變量)
    ????DECLARE?@t?TABLE(ID?int?IDENTITY,b?bit)
    ????    INSERT?@t(b)?SELECT?TOP?8000?0?FROM?syscolumns?a,syscolumns?b

    ????    INSERT?@re?SELECT?SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID)
    ????    FROM?@t
    ????    WHERE?ID<=LEN(@s+'a')?
    ????????    AND?CHARINDEX(@split,@split+@s,ID)=ID
    ????    RETURN
    END
    GO

    /*==============================================*/

    if?exists?(select?*?from?dbo.sysobjects?where?id?=?object_id(N'[dbo].[f_splitSTR]')?and?xtype?in?(N'FN',?N'IF',?N'TF'))
    drop?function?[dbo].[f_splitSTR]
    GO

    if?exists?(select?*?from?dbo.sysobjects?where?id?=?object_id(N'[dbo].[tb_splitSTR]')?and?objectproperty(id,N'IsUserTable')=1)
    drop?table?[dbo].[tb_splitSTR]
    GO

    --3.2.3.2?使用永久性分拆輔助表法
    --    字符串分拆輔助表
    SELECT?TOP?8000?ID=IDENTITY(int,1,1)?INTO?dbo.tb_splitSTR
    FROM?syscolumns?a,syscolumns?b
    GO

    --字符串分拆處理函數(shù)
    CREATE?FUNCTION?f_splitSTR(
    @s?????varchar(8000),??--待分拆的字符串
    @split??varchar(10)?????--數(shù)據(jù)分隔符
    )RETURNS?TABLE
    AS
    RETURN(
    ????    SELECT?col=CAST(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID)?as?varchar(100))
    ????    FROM?tb_splitSTR
    ????    WHERE?ID<=LEN(@s+'a')?
    ????????    AND?CHARINDEX(@split,@split+@s,ID)=ID)
    GO


    /*==============================================*/

    if?exists?(select?*?from?dbo.sysobjects?where?id?=?object_id(N'[dbo].[f_splitSTR]')?and?xtype?in?(N'FN',?N'IF',?N'TF'))
    drop?function?[dbo].[f_splitSTR]
    GO

    --3.2.5?將數(shù)據(jù)項按數(shù)字與非數(shù)字再次拆份
    CREATE?FUNCTION?f_splitSTR(
    @s???varchar(8000),????--待分拆的字符串
    @split?varchar(10)?????--數(shù)據(jù)分隔符
    )RETURNS?@re?TABLE(No?varchar(100),Value?varchar(20))
    AS
    BEGIN
    ????    --創(chuàng)建分拆處理的輔助表(用戶定義函數(shù)中只能操作表變量)
    ????DECLARE?@t?TABLE(ID?int?IDENTITY,b?bit)
    ????    INSERT?@t(b)?SELECT?TOP?8000?0?FROM?syscolumns?a,syscolumns?b

    ????    INSERT?@re?
    ????    SELECT????No=REVERSE(STUFF(col,1,PATINDEX('%[^-^.^0-9]%',col+'a')-1,'')),
    ????????Value    =REVERSE(LEFT(col,PATINDEX('%[^-^.^0-9]%',col+'a')-1))
    ????    FROM(
    ????????    SELECT?col=REVERSE(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID))
    ????????    FROM?@t
    ????????    WHERE?ID<=LEN(@s+'a')?
    ????????????    AND?CHARINDEX(@split,@split+@s,ID)=ID)a
    ????    RETURN
    END
    GO


    /*==============================================*/

    if?exists?(select?*?from?dbo.sysobjects?where?id?=?object_id(N'[dbo].[f_splitSTR]')?and?xtype?in?(N'FN',?N'IF',?N'TF'))
    drop?function?[dbo].[f_splitSTR]
    GO

    --3.2.6?分拆短信數(shù)據(jù)
    CREATE?FUNCTION?f_splitSTR(@s?varchar(8000))
    RETURNS?@re?TABLE(split?varchar(10),value?varchar(100))
    AS
    BEGIN
    ????    DECLARE?@splits?TABLE(split?varchar(10),splitlen?as?LEN(split))
    ????    INSERT?@splits(split)
    ????    SELECT?'AC'?UNION?ALL
    ????    SELECT?'BC'?UNION?ALL
    ????    SELECT?'CC'?UNION?ALL
    ????    SELECT?'DC'????
    ????    DECLARE?@pos1?int,@pos2?int,@split?varchar(10),@splitlen?int
    ????    SELECT?TOP?1?
    ????????    @pos1=1,@split=split,@splitlen=splitlen
    ????    FROM?@splits
    ????    WHERE?@s?LIKE?split+'%'
    ????    WHILE?@pos1>0
    ????    BEGIN
    ????????    SELECT?TOP?1
    ????????????    @pos2=CHARINDEX(split,@s,@splitlen+1)
    ????????    FROM?@splits
    ????????    WHERE?CHARINDEX(split,@s,@splitlen+1)>0
    ????????    ORDER?BY?CHARINDEX(split,@s,@splitlen+1)
    ????????    IF?@@ROWCOUNT=0
    ????????    BEGIN
    ????????????    INSERT?@re?VALUES(@split,STUFF(@s,1,@splitlen,''))
    ????????????    RETURN
    ????????    END
    ????????    ELSE
    ????????    BEGIN
    ????????????    INSERT?@re?VALUES(@split,SUBSTRING(@s,@splitlen+1,@pos2-@splitlen-1))
    ????????????    SELECT?TOP?1?
    ????????????????    @pos1=1,@split=split,@splitlen=splitlen,@s=STUFF(@s,1,@pos2-1,'')
    ????????????    FROM?@splits
    ????????????    WHERE?STUFF(@s,1,@pos2-1,'')?LIKE?split+'%'
    ????????    END
    ????    END
    ????    RETURN
    END
    GO

    --分段截取函數(shù)

    --分段截取函數(shù)
    if?exists?(select?*?from?dbo.sysobjects?where?id?=?object_id(N'[dbo].[f_GetStr]')?and?xtype?in?(N'FN',?N'IF',?N'TF'))
    drop?function?[dbo].[f_GetStr]
    GO

    --分段截取函數(shù)
    CREATE?FUNCTION?dbo.f_GetStr(
    @s?varchar(8000),??????--包含多個數(shù)據(jù)項的字符串
    @pos?int,?????????????--要獲取的數(shù)據(jù)項的位置
    @split?varchar(10)?????--數(shù)據(jù)分隔符
    )RETURNS?varchar(100)
    AS
    BEGIN
    ????    IF?@s?IS?NULL?RETURN(NULL)
    ????    DECLARE?@splitlen?int
    ????    SELECT?@splitlen=LEN(@split+'a')-2
    ????    WHILE?@pos>1?AND?CHARINDEX(@split,@s+@split)>0
    ????????    SELECT?@pos=@pos-1,
    ????????????    @s=STUFF(@s,1,CHARINDEX(@split,@s+@split)+@splitlen,'')
    ????    RETURN(ISNULL(LEFT(@s,CHARINDEX(@split,@s+@split)-1),''))
    END
    GO

    3 IP地址處理函數(shù)

    if?exists?(select?*?from?dbo.sysobjects?where?id?=?object_id(N'[dbo].[f_IP2Int]')?and?xtype?in?(N'FN',?N'IF',?N'TF'))
    drop?function?[dbo].[f_IP2Int]
    GO

    --1.?字符串IP地址轉(zhuǎn)換成IP數(shù)值函數(shù)。
    CREATE?FUNCTION?dbo.f_IP2Int(
    @ip?char(15)
    )    RETURNS?bigint
    AS
    BEGIN
    ????    DECLARE?@re?bigint
    ????    SET?@re=0
    ????    SELECT?@re=@re+LEFT(@ip,CHARINDEX('.',@ip+'.')-1)*ID
    ????????,    @ip=STUFF(@ip,1,CHARINDEX('.',@ip+'.'),'')
    ????    FROM(
    ????????    SELECT?ID=CAST(16777216?as?bigint)
    ????????    UNION?ALL?SELECT?65536
    ????????    UNION?ALL?SELECT?256
    ????????    UNION?ALL?SELECT?1)a
    ????    RETURN(@re)
    END
    GO


    /*===========================================================*/


    if?exists?(select?*?from?dbo.sysobjects?where?id?=?object_id(N'[dbo].[f_Int2IP]')?and?xtype?in?(N'FN',?N'IF',?N'TF'))
    drop?function?[dbo].[f_Int2IP]
    GO

    --1.?字符串IP地址轉(zhuǎn)換成IP數(shù)值函數(shù)。
    CREATE?FUNCTION?dbo.f_Int2IP(
    @IP?bigint
    )    RETURNS?varchar(15)
    AS
    BEGIN
    ????    DECLARE?@re?varchar(15)
    ????    SET?@re=''
    ????    SELECT?@re=@re+'.'+CAST(@IP/ID?as?varchar)
    ????????,    @IP=@IP%ID
    ????    from(
    ????????    SELECT?ID=CAST(16777216?as?bigint)
    ????????    UNION?ALL?SELECT?65536
    ????????    UNION?ALL?SELECT?256
    ????????    UNION?ALL?SELECT?1)a
    ????    RETURN(STUFF(@re,1,1,''))
    END
    GO

    if?exists?(select?*?from?dbo.sysobjects?where?id?=?object_id(N'[dbo].[f_CompareSTR]')?and?xtype?in?(N'FN',?N'IF',?N'TF'))
    drop?function?[dbo].[f_CompareSTR]
    GO

    --1.?比較兩個字符串中包含的數(shù)據(jù)項是否相同的用戶定義函數(shù)：
    CREATE?FUNCTION?dbo.f_CompareSTR(
    @s1??varchar(8000),??--要比較的第一個字符串
    @s2??varchar(8000),??--要比較的第二個字符串
    @split?varchar(10)????--數(shù)據(jù)分隔符
    )RETURNS?bit
    AS
    BEGIN
    ????    IF?LEN(@s1)<>LEN(@s2)?RETURN(0)
    ????    DECLARE?@r1?TABLE(col?varchar(100))
    ????    DECLARE?@r2?TABLE(col?varchar(100))
    ????    DECLARE?@splitlen?int
    ????    SET?@splitlen=LEN(@split+'a')-2
    ????    WHILE?CHARINDEX(@split,@s1)>0
    ????    BEGIN
    ????????    INSERT?@r1?VALUES(LEFT(@s1,CHARINDEX(@split,@s1)-1))
    ????????    SET?@s1=STUFF(@s1,1,CHARINDEX(@split,@s1)+@splitlen,'')
    ????    END
    ????    INSERT?@r1?VALUES(@s1)

    ????    WHILE?CHARINDEX(@split,@s2)>0
    ????    BEGIN
    ????????    INSERT?@r2?VALUES(LEFT(@s2,CHARINDEX(@split,@s2)-1))
    ????????    SET?@s2=STUFF(@s2,1,CHARINDEX(@split,@s2)+@splitlen,'')
    ????    END
    ????    INSERT?@r2?VALUES(@s2)
    ????    RETURN(CASE
    ????????    WHEN?EXISTS(SELECT?*?FROM?@r1?a?FULL?JOIN?@r2?b?ON?a.col=b.col?WHERE?a.col?IS?NULL?OR?b.col?IS?NULL)
    ????????    THEN?0?ELSE?1?END)
    END
    GO


    /*================================================================*/


    if?exists?(select?*?from?dbo.sysobjects?where?id?=?object_id(N'[dbo].[f_CompareSTR]')?and?xtype?in?(N'FN',?N'IF',?N'TF'))
    drop?function?[dbo].[f_CompareSTR]
    GO

    --2．比較兩個字符串中包含的數(shù)據(jù)項是否有任意一個相同
    CREATE?FUNCTION?dbo.f_CompareSTR(
    @s1??varchar(8000),??--要比較的第一個字符串
    @s2??varchar(8000),??--要比較的第二個字符串
    @split?varchar(10)????--數(shù)據(jù)分隔符
    )RETURNS?bit
    AS
    BEGIN
    ????    DECLARE?@splitlen?int
    ????    SET?@splitlen=LEN(@split+'a')-2
    ????    WHILE?CHARINDEX(@split,@s1)>0
    ????    BEGIN
    ????????    IF?CHARINDEX(@split+LEFT(@s1,CHARINDEX(@split,@s1)-1)+@split,@split+@s2+@split)>0
    ????????????    RETURN(1)
    ????????    SET?@s1=STUFF(@s1,1,CHARINDEX(@split,@s1)+@splitlen,'')
    ????    END
    ????    RETURN(CASE?WHEN?CHARINDEX(@split+@s1+@split,@split+@s2+@split)>0?THEN?1?ELSE?0?END)
    END
    
		
    
			posted on 2007-01-19 21:14 record java and net 閱讀(205) 評論(0)  編輯  收藏  所屬分類: 常用配置代碼 
		
    
	
    
	
	


	


    
	    
    




    








    

				
			
    
			
    
				
					
					

    導(dǎo)航
    

					
					
					

    常用鏈接
    


    留言簿(44)
    


		
    新聞檔案
    
		
				
			
	
		
    2.動態(tài)語言
    
		
				
			
	
		
    3.工具箱
    
		
				
			
	
		
    9.文檔教程
    
		
				
			
	
		
    友情鏈接
    
		
				
			
	



    搜索
    



    最新評論
	
    


					

				
			
    			
			
	

    






    




    







    
        
	



主站蜘蛛池模板：
亚洲va久久久噜噜噜久久|
免费无码肉片在线观看|
国产国拍亚洲精品福利|
美景之屋4在线未删减免费
|
亚洲电影中文字幕|
成人电影在线免费观看|
亚洲成A∨人片在线观看不卡
|
大陆一级毛片免费视频观看i|
一级毛片直播亚洲|
麻豆69堂免费视频|
亚洲精品国产精品乱码不卞|
久久99久久成人免费播放|
亚洲av永久无码精品古装片|
中文字幕免费在线观看|
亚洲香蕉在线观看|
免费毛片在线播放|
一级毛片免费播放试看60分钟|
亚洲免费观看视频|
亚洲视频免费在线看|
精品国产日韩久久亚洲|
亚洲AV无码之日韩精品|
a色毛片免费视频|
亚洲黄色片在线观看|
啦啦啦手机完整免费高清观看|
国产亚洲精彩视频|
九月丁香婷婷亚洲综合色|
亚洲啪啪免费视频|
国产亚洲视频在线观看网址|
亚洲无人区午夜福利码高清完整版|
久久国产乱子免费精品|
在线观看亚洲AV日韩A∨|
亚洲精品国产精品乱码不卡|
一级毛片免费播放|
亚洲Av无码国产一区二区|
亚洲第一黄片大全|
**实干一级毛片aa免费|
国产精品日本亚洲777|
亚洲国产香蕉碰碰人人|
国产色爽女小说免费看|
黄网站免费在线观看|
亚洲日韩av无码中文|