SQL code
-- 3、字符串相关操作 -- -3.1 提取字符串中不同类型字符 use PracticeDB go create function fun_getCN( @str nvarchar ( 4000 )) returns nvarchar ( 4000 ) as begin declare @word nchar ( 1 ), @CN nvarchar ( 4000 ) set @CN = '' while len ( @str ) > 0 begin set @word =left ( @str , 1 ) if unicode ( @word ) between 19968 and 19968 + 20901 set @CN = @CN + @word set @str =right ( @str , len ( @str ) - 1 ) end return @CN end select dbo.fun_getCN( ' ASDKG论坛KDL ' ) -- 论坛 select dbo.fun_getCN( ' ASDKG論壇KDL ' ) -- 論壇 select dbo.fun_getCN( ' ASDKDL ' ) -- 空字符串 -- 提取数字 IF OBJECT_ID ( ' DBO.GET_NUMBER2 ' ) IS NOT NULL DROP FUNCTION DBO.GET_NUMBER2 GO CREATE FUNCTION DBO.GET_NUMBER2( @S VARCHAR ( 100 )) RETURNS VARCHAR ( 100 ) AS BEGIN WHILE PATINDEX ( ' %[^0-9]% ' , @S ) > 0 BEGIN set @s = stuff ( @s , patindex ( ' %[^0-9]% ' , @s ), 1 , '' ) END RETURN @S END GO -- 测试 PRINT DBO.GET_NUMBER( ' 呵呵ABC123ABC ' ) GO -- 123 -- ------------------------------------------------------------------ -- 提取英文 IF OBJECT_ID ( ' DBO.GET_STR ' ) IS NOT NULL DROP FUNCTION DBO.GET_STR GO CREATE FUNCTION DBO.GET_STR( @S VARCHAR ( 100 )) RETURNS VARCHAR ( 100 ) AS BEGIN WHILE PATINDEX ( ' %[^a-z]% ' , @S ) > 0 BEGIN set @s = stuff ( @s , patindex ( ' %[^a-z]% ' , @s ), 1 , '' ) END RETURN @S END GO -- 测试 PRINT DBO.GET_STR( ' 呵呵ABC123ABC ' ) GO -- ------------------------------------------------------------------ -- 提取中文 IF OBJECT_ID ( ' DBO.CHINA_STR ' ) IS NOT NULL DROP FUNCTION DBO.CHINA_STR GO CREATE FUNCTION DBO.CHINA_STR( @S NVARCHAR ( 100 )) RETURNS VARCHAR ( 100 ) AS BEGIN WHILE PATINDEX ( ' %[^吖-座]% ' , @S ) > 0 SET @S = STUFF ( @S , PATINDEX ( ' %[^吖-座]% ' , @S ), 1 ,N '' ) RETURN @S END GO PRINT DBO.CHINA_STR( ' 呵呵ABC123ABC ' ) GO -- 3.2 从字符串中取固定数量的字段 IF OBJECT_ID ( ' [fn_test] ' ) IS NOT NULL DROP FUNCTION [ fn_test ] GO CREATE FUNCTION [ fn_test ] ( @s NVARCHAR ( 200 ), -- 输入的字符串, @len INT -- 每个字符串长度(2或4) ) RETURNS @r TABLE (id int ,col NVARCHAR ( 4 )) AS BEGIN DECLARE @i INT SET @i = 0 WHILE LEN ( @s ) > 0 BEGIN SET @i = @i + 1 INSERT @r SELECT @i , LEFT ( @s , @len ) SELECT @s = STUFF ( @s , 1 , @len , '' ) END RETURN END GO -- 3.3 关于字符串拆分 IF OBJECT_ID ( ' tb ' ) IS NOT NULL DROP TABLE tb GO CREATE TABLE tb (id INT ,col VARCHAR ( 30 )) INSERT INTO tb VALUES ( 1 , ' aa,bb ' ) INSERT INTO tb VALUES ( 2 , ' aaa,bbb,ccc ' ) GO -- 3.3.1.2000/2005通用方法 SELECT a.id, col = SUBSTRING (a.col, number , CHARINDEX ( ' , ' ,a.col + ' , ' , number ) - b. number ) FROM tb a JOIN master..spt_values b ON b.type = ' p ' -- AND SUBSTRING(','+a.col,b.number,1)=',' --用此条件或下面的条件均可 AND CHARINDEX ( ' , ' , ' , ' + a.col, number ) = number -- 结果: /* id col ----------- -------------------------------------------------- 1 aa 1 bb 2 aaa 2 bbb 2 ccc (5 行受影响) */ -- 3.3.2.2005以上新方法: SELECT a.id,b.col FROM ( SELECT id,col = CAST ( ' <v> ' + REPLACE (col, ' , ' , ' </v><v> ' ) + ' </v> ' AS XML) FROM tb ) AS a OUTER APPLY ( SELECT C.value( ' . ' , ' varchar(50) ' ) AS col -- 此处value必须为小写 FROM a.col.nodes( ' /v ' ) AS T(C) ) AS b -- 结果: /* id col ----------- -------------------------------------------------- 1 aa 1 bb 2 aaa 2 bbb 2 ccc (5 行受影响) */ -- 3.3.3.游标循环法: DECLARE @t TABLE (id INT ,col NVARCHAR ( 50 )) DECLARE @id INT , @col nvarchar ( 200 ) DECLARE c CURSOR FOR SELECT * FROM tb OPEN c FETCH NEXT FROM c INTO @id , @col WHILE @@FETCH_STATUS = 0 BEGIN WHILE CHARINDEX ( ' , ' , @col ) > 0 BEGIN INSERT @t SELECT @id , LEFT ( @col , CHARINDEX ( ' , ' , @col + ' , ' ) - 1 ) SET @col = STUFF ( @col , 1 , CHARINDEX ( ' , ' , @col ), '' ) END INSERT @t SELECT @id , LEFT ( @col , CHARINDEX ( ' , ' , @col + ' , ' ) - 1 ) -- 退出循环后插入最后获取的值 FETCH NEXT FROM c INTO @id , @col END CLOSE c DEALLOCATE c -- 查询 SELECT * FROM @t -- 结果: /* id col ---------- -------------------------------------------------- 1 aa 1 bb 2 aaa 2 bbb 2 ccc (5 行受影响) */ -- 3.3.4.SQL2005 函数法: IF OBJECT_ID ( ' tb ' ) IS NOT NULL DROP TABLE tb GO CREATE TABLE tb (id INT ,col VARCHAR ( 30 )) INSERT INTO tb VALUES ( 1 , ' aa,bb ' ) INSERT INTO tb VALUES ( 2 , ' aaa,bbb,ccc ' ) GO IF OBJECT_ID ( ' f_str ' ) IS NOT NULL DROP FUNCTION f_str GO CREATE FUNCTION f_str( @str VARCHAR ( 20 )) RETURNS @t TABLE (col VARCHAR ( 20 )) AS BEGIN SET @str = @str + ' , ' WHILE len ( @str ) > 0 BEGIN INSERT @t SELECT LEFT ( @str , CHARINDEX ( ' , ' , @str ) - 1 ) SET @str = STUFF ( @str , 1 , CHARINDEX ( ' , ' , @str ), '' ) END RETURN END GO -- 调用查询 SELECT a.id,b.col FROM tb a CROSS APPLY f_str(a.col) b /* id col ----------- -------------------- 1 aa 1 bb 2 aaa 2 bbb 2 ccc (5 行受影响) */ -- 3.3.5.SQL2005函数法二: IF OBJECT_ID ( ' tb ' ) IS NOT NULL DROP TABLE tb GO CREATE TABLE tb (id INT ,col VARCHAR ( 30 )) INSERT INTO tb VALUES ( 1 , ' aa,bb ' ) INSERT INTO tb VALUES ( 2 , ' aaa,bbb,ccc ' ) GO IF OBJECT_ID ( ' f_str ' ) IS NOT NULL DROP FUNCTION f_str GO CREATE FUNCTION f_str( @str VARCHAR ( 50 )) RETURNS @t TABLE (col VARCHAR ( 50 )) AS BEGIN DECLARE @xml XML SET @xml = ' <v> ' + REPLACE ( @str , ' , ' , ' </v><v> ' ) + ' </v> ' INSERT @t SELECT C.value( ' . ' , ' varchar(50) ' ) FROM @xml .nodes( ' /v ' ) AS T(C) RETURN END GO -- 调用查询 SELECT a.id,b.col FROM tb a CROSS APPLY f_str(a.col) b /* id col ----------- ------------------ 1 aa 1 bb 2 aaa 2 bbb 2 ccc (5 行受影响) */ -- SQL2000/2005字符串拆分为列表通用函数 IF OBJECT_ID ( ' f_getstr ' ) IS NOT NULL DROP FUNCTION f_getstr GO CREATE FUNCTION f_getstr( @s NVARCHAR ( 4000 ), -- 待分拆的字符串 @flag NVARCHAR ( 10 ) = '' -- 数据分隔符 ) RETURNS @r TABLE (col NVARCHAR ( 1000 )) AS BEGIN IF ISNULL ( @flag , '' ) = '' AND LEN ( ISNULL ( @flag , '' ) + ' a ' ) = 1 INSERT @r SELECT SUBSTRING ( @s , number + 1 , 1 ) FROM master..spt_values WHERE TYPE = ' p ' and number < LEN ( @s + ' a ' ) - 1 ELSE INSERT @r SELECT SUBSTRING ( @s , number , CHARINDEX ( @flag , @s + @flag , number ) - number ) FROM master..spt_values WHERE TYPE = ' p ' and number <= len ( @s + ' a ' ) -- AND SUBSTRING(@flag+@s,number,1)=@flag --用此条件或下面的条件均可 AND CHARINDEX ( @flag , @flag + @s , number ) = number RETURN END GO -- 本实例技巧，利用master库自带的spt_values表，取number字段作为连续的序号， -- 省去创建序号表，尽量做到通用，再加上字符串处理函数取得最终结果。 -- 每个字符拆分取出 SELECT * FROM dbo.f_getstr(N ' 一个世界一个家 ' , NULL ) SELECT * FROM dbo.f_getstr(N ' 一个世界一个家 ' , '' ) SELECT * FROM dbo.f_getstr(N ' 一个世界一个家 ' , default ) /* col ------ 一 个 世 界 一 个 家 (7 行受影响) */ -- 指定分隔符拆分取出 SELECT * FROM dbo.f_getstr(N ' 一个 世界 一个 家 ' ,N ' ' ) SELECT * FROM dbo.f_getstr(N ' 一个,世界,一个,家 ' ,N ' , ' ) SELECT * FROM dbo.f_getstr(N ' 一个%世界%一个%家 ' ,N ' % ' ) SELECT * FROM dbo.f_getstr(N ' 一个中国世界中国一个中国家 ' ,N ' 中国 ' ) /* col --------- 一个 世界 一个 家 (4 行受影响) */ -- SQL2005以上版本可以结合apply进行拆分列值 IF OBJECT_ID ( ' tb ' ) IS NOT NULL DROP TABLE tb GO CREATE TABLE tb (id INT ,col VARCHAR ( 30 )) INSERT INTO tb VALUES ( 1 , ' aa,bb ' ) INSERT INTO tb VALUES ( 2 , ' aaa,bbb,ccc ' ) GO SELECT id,b.col FROM tb CROSS APPLY f_getstr(col, ' , ' ) b SELECT id,b.col FROM tb OUTER APPLY f_getstr(col, ' , ' ) b /* id col ----------- ----------- 1 aa 1 bb 2 aaa 2 bbb 2 ccc (5 行受影响) */ 本文来自CSDN博客，转载请标明出处：http: // blog.csdn.net / htl258 / archive / 2010 / 04 / 28 / 5537235 .aspx 本文来自CSDN博客，转载请标明出处：http: // blog.csdn.net / htl258 / archive / 2009 / 09 / 09 / 4533846 . as
849
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
