亚洲精品国产精品久久,成黄免费在线,国产精品国色综合久久

[數據庫]字符處理

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 ]

-- 1.?字符串IP地址轉換成IP數值函數。

CREATE ? FUNCTION ?dbo.f_IP2Int(

@ip ? char ( 15 )

) RETURNS ? bigint

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

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

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 ]

-- 1.?字符串IP地址轉換成IP數值函數。

CREATE ? FUNCTION ?dbo.f_Int2IP(

@IP ? bigint

) RETURNS ? varchar ( 15 )

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

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 ]

-- 分段截取函數

CREATE ? FUNCTION ?dbo.f_SetStr(

@s ? varchar ( 8000 ),?????? -- 包含數據項的字符串

@pos ? int ,????????????? -- 要更新的數據項的段

@value ? varchar ( 100 ),??? -- 更新后的值

@split ? varchar ( 10 )????? -- 數據分隔符

) RETURNS ? varchar ( 8000 )

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

--各種字符串分函數

--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

--*/

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

--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

--合并處理函數

CREATE?FUNCTION?dbo.f_str(@col1?varchar(10))

RETURNS?varchar(100)

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

--調用函數

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

--*/

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

--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

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

--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

--*/

--各種字符串分函數

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]

--3.2.1?循環截取法

CREATE?FUNCTION?f_splitSTR(

@s???varchar(8000),???--待分拆的字符串

@split?varchar(10)?????--數據分隔符

)RETURNS?@re?TABLE(col?varchar(100))

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

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

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]

--3.2.3.1?使用臨時性分拆輔助表法

CREATE?FUNCTION?f_splitSTR(

@s???varchar(8000),??--待分拆的字符串

@split?varchar(10)?????--數據分隔符

)RETURNS?@re?TABLE(col?varchar(100))

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

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

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]

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]

--3.2.3.2?使用永久性分拆輔助表法

--字符串分拆輔助表

SELECT?TOP?8000?ID=IDENTITY(int,1,1)?INTO?dbo.tb_splitSTR

FROM?syscolumns?a,syscolumns?b

--字符串分拆處理函數

CREATE?FUNCTION?f_splitSTR(

@s?????varchar(8000),??--待分拆的字符串

@split??varchar(10)?????--數據分隔符

)RETURNS?TABLE

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)

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

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]

--3.2.5?將數據項按數字與非數字再次拆份

CREATE?FUNCTION?f_splitSTR(

@s???varchar(8000),????--待分拆的字符串

@split?varchar(10)?????--數據分隔符

)RETURNS?@re?TABLE(No?varchar(100),Value?varchar(20))

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

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

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]

--3.2.6?分拆短信數據

CREATE?FUNCTION?f_splitSTR(@s?varchar(8000))

RETURNS?@re?TABLE(split?varchar(10),value?varchar(100))

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

--分段截取函數

--分段截取函數

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]

--分段截取函數

CREATE?FUNCTION?dbo.f_GetStr(

@s?varchar(8000),??????--包含多個數據項的字符串

@pos?int,?????????????--要獲取的數據項的位置

@split?varchar(10)?????--數據分隔符

)RETURNS?varchar(100)

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

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]

--1.?字符串IP地址轉換成IP數值函數。

CREATE?FUNCTION?dbo.f_IP2Int(

@ip?char(15)

)RETURNS?bigint

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

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

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]

--1.?字符串IP地址轉換成IP數值函數。

CREATE?FUNCTION?dbo.f_Int2IP(

@IP?bigint

)RETURNS?varchar(15)

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

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]

--1.?比較兩個字符串中包含的數據項是否相同的用戶定義函數：

CREATE?FUNCTION?dbo.f_CompareSTR(

@s1??varchar(8000),??--要比較的第一個字符串

@s2??varchar(8000),??--要比較的第二個字符串

@split?varchar(10)????--數據分隔符

)RETURNS?bit

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

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

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]

--2．比較兩個字符串中包含的數據項是否有任意一個相同

CREATE?FUNCTION?dbo.f_CompareSTR(

@s1??varchar(8000),??--要比較的第一個字符串

@s2??varchar(8000),??--要比較的第二個字符串

@split?varchar(10)????--數據分隔符

)RETURNS?bit

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 閱讀(202) 評論(0) 編輯收藏所屬分類: 常用配置代碼

新用戶注冊刷新評論列表


只有注冊用戶登錄后才能發表評論。




網站導航: 博客園 IT新聞 Chat2DB C++博客博問管理
相關文章: c sharp DateUtility [數據庫]字符處理 [數據庫]第一章 vbs tips DButil 誠實-謙虛 hibernate.hbm.xml log4j.properties build.xml

blogjava's web log

[數據庫]字符處理

導航

常用鏈接

留言簿(44)

新聞檔案

2.動態語言

3.工具箱

9.文檔教程

友情鏈接

搜索

最新評論