sql 字符首字母展示中遇到 N多特殊符号时的问题！

tangtang89522 2011-06-16 09:47:42

贴一下我目前的函数,目前函数中存在的问题是针对很多特殊的字符，只能发现一个过滤掉一个。这个方法很笨。想请教一下各位，什么方法可以过滤掉所有的特殊字符！
提供以下特殊的数据：（估计还有很多我想不到的奇奇怪怪的字符，总之这个目前没找到合适的方法可以过滤掉！）
测试<》>!:,.。}】【）
。。。。。



alter FUNCTION [dbo].[uf_getpinyin](@str  nvarchar(4000))

returns  nvarchar(4000)  

--WITH  ENCRYPTION  

AS  

--生成拼音首码  

BEGIN

    DECLARE  @strlen INT,@return VARCHAR(500),@i INT

    DECLARE @n INT,@c CHAR(1),@chn NCHAR(1), @word nchar(1),@str_new  nvarchar(4000)

	--把特殊字符去掉

	select  @str_new=''--,@str=dbo.uf_convert(@str,0) 此处会把半角变成全角

	while len(@str)>0

	begin

		set @word=left(@str,1)

		IF @word  --like '%[0-9a-zA-Z]%' or @word like '%[^吖-座]%' 此方法不行 <>等字符都过滤不了

		NOT IN ('，','、','。','；','：','（','）','【','】','“','”','‘','’','(',')','·','-','+','.','Ⅱ','Ⅰ','Ⅱ','<','>') 

			set @str_new=@str_new+@word

		set @str=right(@str,len(@str)-1)

	end



    SELECT @strlen=LEN(@str_new),@return='',@i=0

    SET @i=0

    WHILE @i<@strlen

    BEGIN

        SELECT @i=@i+1,@n=63,@chn=substring(@str_new,@i,1)

        IF @chn>'z'

		BEGIN

			

			SELECT @n = @n+1,@c = CASE chn WHEN @chn THEN CHAR(@n) ELSE @c END

			FROM (SELECT TOP 27 * FROM (

					SELECT chn = '吖'

                    UNION ALL SELECT '八'

                    UNION ALL SELECT '嚓'

                    UNION ALL SELECT '咑'

                    UNION ALL SELECT '妸'

                    UNION ALL SELECT '发'

                    UNION ALL SELECT '旮'

                    UNION ALL SELECT '铪'

                    UNION ALL SELECT '丌'        --because have no 'i'

                    UNION ALL SELECT '丌'

                    UNION ALL SELECT '咔'

                    UNION ALL SELECT '垃'

                    UNION ALL SELECT '嘸'

                    UNION ALL SELECT '拏'

                    UNION ALL SELECT '噢'

                    UNION ALL SELECT '妑'

                    UNION ALL SELECT '七'

                    UNION ALL SELECT '呥'

                    UNION ALL SELECT '仨'

                    UNION ALL SELECT '他'

                    UNION ALL SELECT '屲'        --no 'u'

                    UNION ALL SELECT '屲'        --no 'v'

                    UNION ALL SELECT '屲'

                    UNION ALL SELECT '夕'

                    UNION ALL SELECT '丫'

                    UNION ALL SELECT '帀'

                    UNION ALL SELECT @chn) AS a

                ORDER BY chn COLLATE Chinese_PRC_CI_AS 

            ) AS b

		END

        ELSE 

        SET @c=(case when  ISNUMERIC(@chn)=1 then (

			select JC from (

			SELECT ZM='1',JC='Y'

			union all select '2', 'E'union all select '3', 'S'union all select '4', 'S'union all select '5', 'W'union all select '6', 'L'union all select '7', 'Q' 

			union all select '8', 'B' union all select '9', 'J' union all select '0', 'L'  ) a

			where ZM=@chn) else @chn end)

        SET @return=@return+@c

    END

    RETURN(@return)

END