blogjava's web log
          

          blogjava's web log

          
...

          


          



  
          
			
          
				
					
	
          
		
          
			[數據庫]字符處理
		
          
		
          
		
          
				
				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地址轉換成IP數值函數。
				
						
          
						
				          
				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地址轉換成IP數值函數。
				
						
          
						
				          
				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
				
						
          
						
						
          
						
				          
				--
				分段截取函數
				
						
          
						
				          
				CREATE
				?
				FUNCTION
				?dbo.f_SetStr(
          
				@s
				?
				varchar
				(
				8000
				),??????
				--
				包含數據項的字符串
				
						
          
						
				          
				@pos
				?
				int
				,?????????????
				--
				要更新的數據項的段
				
						
          
						
				          
				@value
				?
				varchar
				(
				100
				),???
				--
				更新后的值
				
						
          
						
				          
				@split
				?
				varchar
				(
				10
				)?????
				--
				數據分隔符
				
						
          
						
				          
				)
				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
				
						
          
						
				          
		
          --各種字符串分函數


          --3.3.1?使用游標法進行字符串合并處理的示例。
          --          處理的數據
          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

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

          --定義游標并進行合并處理
          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
          --顯示結果并刪除測試數據
          SELECT?*?FROM?@t
          DROP?TABLE?tb
          /*--結果
          col1???????col2
          ----------?-----------
          a??????????1,2
          b??????????1,2,3
          --          */
          GO


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


          --3.3.2?使用用戶定義函數，配合SELECT處理完成字符串合并處理的示例
          --          處理的數據
          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

          --合并處理函數
          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

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

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


          --3.3.3?使用臨時表實現字符串合并處理的示例
          --          處理的數據
          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
          --          */
          --得到最終結果
          SELECT?col1,col2=MAX(col2)?FROM?#t?GROUP?BY?col1
          /*--結果
          col1???????col2
          ----------?-----------
          a??????????1,2
          b??????????1,2,3
          --          */
          --刪除測試
          DROP?TABLE?tb,#t
          GO


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

          --3.3.4.1?每組?<=2?條記錄的合并
          --          處理的數據
          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
          /*--結果
          col1???????col2??????
          ----------?----------
          a??????????1,2
          b??????????1,2
          c??????????3
          --          */

          --3.3.4.2?每組?<=3?條記錄的合并
          --          處理的數據
          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
          /*--結果
          col1???????col2
          ----------?------------
          a??????????1,2
          b??????????1,2,3
          c??????????3
          --          */
          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.1?循環截取法
          CREATE?FUNCTION?f_splitSTR(
          @s???varchar(8000),???--待分拆的字符串
          @split?varchar(10)?????--數據分隔符
          )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)?????--數據分隔符
          )RETURNS?@re?TABLE(col?varchar(100))
          AS
          BEGIN
          ????          --創建分拆處理的輔助表(用戶定義函數中只能操作表變量)
          ????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

          --字符串分拆處理函數
          CREATE?FUNCTION?f_splitSTR(
          @s?????varchar(8000),??--待分拆的字符串
          @split??varchar(10)?????--數據分隔符
          )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?將數據項按數字與非數字再次拆份
          CREATE?FUNCTION?f_splitSTR(
          @s???varchar(8000),????--待分拆的字符串
          @split?varchar(10)?????--數據分隔符
          )RETURNS?@re?TABLE(No?varchar(100),Value?varchar(20))
          AS
          BEGIN
          ????          --創建分拆處理的輔助表(用戶定義函數中只能操作表變量)
          ????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?分拆短信數據
          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

          --分段截取函數

          --分段截取函數
          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

          --分段截取函數
          CREATE?FUNCTION?dbo.f_GetStr(
          @s?varchar(8000),??????--包含多個數據項的字符串
          @pos?int,?????????????--要獲取的數據項的位置
          @split?varchar(10)?????--數據分隔符
          )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地址處理函數

          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地址轉換成IP數值函數。
          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地址轉換成IP數值函數。
          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.?比較兩個字符串中包含的數據項是否相同的用戶定義函數：
          CREATE?FUNCTION?dbo.f_CompareSTR(
          @s1??varchar(8000),??--要比較的第一個字符串
          @s2??varchar(8000),??--要比較的第二個字符串
          @split?varchar(10)????--數據分隔符
          )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．比較兩個字符串中包含的數據項是否有任意一個相同
          CREATE?FUNCTION?dbo.f_CompareSTR(
          @s1??varchar(8000),??--要比較的第一個字符串
          @s2??varchar(8000),??--要比較的第二個字符串
          @split?varchar(10)????--數據分隔符
          )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 閱讀(203) 評論(0)  編輯  收藏  所屬分類: 常用配置代碼 
		
          
	
          
	
	


	


          
	    
    




          








          

				
			
          
			
          
				
					
					

          導航
          

					
					
					

          常用鏈接
          


          留言簿(44)
          


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



          搜索
          



          最新評論
	
          


					

				
			
          			
			
	

          






          




    







          
        
	




主站蜘蛛池模板：
雷波县|
兰州市|
同江市|
娄烦县|
东莞市|
内乡县|
乌兰浩特市|
隆安县|
光山县|
杨浦区|
孝昌县|
宁强县|
平潭县|
台山市|
景洪市|
元阳县|
柳江县|
阳新县|
连州市|
垫江县|
泸州市|
定陶县|
洪泽县|
塘沽区|
左贡县|
博罗县|
如东县|
峨眉山市|
长寿区|
金秀|
册亨县|
双鸭山市|
邛崃市|
封开县|
隆林|
四子王旗|
鹤庆县|
镇巴县|
措美县|
微博|
丰县|