目前工作中遇到一个情况，需要将SQL Server中的一个 字段 提取 拼音 的首 字母 ， 字段 由汉字、英文、数字以及-构成，百度了一堆，找到如下方法，记录一下，以备后用！ 首先建立一个函数 -- 生成 拼音 首码 CREATE function fn_GetPy( @str nvarchar ( 4000
目前工作中遇到一个情况，需要将sql server中的一个字段提取拼音的首字母，字段由汉字、英文、数字以及“-”构成，百度了一堆，找到如下方法，记录一下，以备后用！
首先建立一个函数
<span >--</span><span >生成<strong>拼音</strong>首码 </span> <span >CREATE</span> <span >function</span> fn_GetPy(<span >@str</span> <span >nvarchar</span>(<span >4000</span><span >)) </span><span >returns</span> <span >nvarchar</span>(<span >4000</span><span >) </span><span >--</span><span >WITH ENCRYPTION </span> <span >as</span> <span >begin</span> <span >declare</span> <span >@intLen</span> <span >int</span> <span >declare</span> <span >@strRet</span> <span >nvarchar</span>(<span >4000</span><span >) </span><span >declare</span> <span >@temp</span> <span >nvarchar</span>(<span >100</span><span >) </span><span >set</span> <span >@intLen</span> <span >=</span> <span >len</span>(<span >@str</span><span >) </span><span >set</span> <span >@strRet</span> <span >=</span> <span >''</span> <span >while</span> <span >@intLen</span> <span >></span> <span >0</span> <span >begin</span> <span >set</span> <span >@temp</span> <span >=</span> <span >''</span> <span >select</span> <span >@temp</span> <span >=</span> <span >case</span> <span >when</span> <span >substring</span>(<span >@str</span>,<span >@intLen</span>,<span >1</span>) <span >>=</span> <span >'</span><span >帀</span><span >'</span> <span >then</span> <span >'</span><span >Z</span><span >'</span> <span >when</span> <span >substring</span>(<span >@str</span>,<span >@intLen</span>,<span >1</span>) <span >>=</span> <span >'</span><span >丫</span><span >'</span> <span >then</span> <span >'</span><span >Y</span><span >'</span> <span >when</span> <span >substring</span>(<span >@str</span>,<span >@intLen</span>,<span >1</span>) <span >>=</span> <span >'</span><span >夕</span><span >'</span> <span >then</span> <span >'</span><span >X</span><span >'</span> <span >when</span> <span >substring</span>(<span >@str</span>,<span >@intLen</span>,<span >1</span>) <span >>=</span> <span >'</span><span >屲</span><span >'</span> <span >then</span> <span >'</span><span >W</span><span >'</span> <span >when</span> <span >substring</span>(<span >@str</span>,<span >@intLen</span>,<span >1</span>) <span >>=</span> <span >'</span><span >他</span><span >'</span> <span >then</span> <span >'</span><span >T</span><span >'</span> <span >when</span> <span >substring</span>(<span >@str</span>,<span >@intLen</span>,<span >1</span>) <span >>=</span> <span >'</span><span >仨</span><span >'</span> <span >then</span> <span >'</span><span >S</span><span >'</span> <span >when</span> <span >substring</span>(<span >@str</span>,<span >@intLen</span>,<span >1</span>) <span >>=</span> <span >'</span><span >呥</span><span >'</span> <span >then</span> <span >'</span><span >R</span><span >'</span> <span >when</span> <span >substring</span>(<span >@str</span>,<span >@intLen</span>,<span >1</span>) <span >>=</span> <span >'</span><span >七</span><span >'</span> <span >then</span> <span >'</span><span >Q</span><span >'</span> <span >when</span> <span >substring</span>(<span >@str</span>,<span >@intLen</span>,<span >1</span>) <span >>=</span> <span >'</span><span >妑</span><span >'</span> <span >then</span> <span >'</span><span >P</span><span >'</span> <span >when</span> <span >substring</span>(<span >@str</span>,<span >@intLen</span>,<span >1</span>) <span >>=</span> <span >'</span><span >噢</span><span >'</span> <span >then</span> <span >'</span><span >O</span><span >'</span> <span >when</span> <span >substring</span>(<span >@str</span>,<span >@intLen</span>,<span >1</span>) <span >>=</span> <span >'</span><span >拏</span><span >'</span> <span >then</span> <span >'</span><span >N</span><span >'</span> <span >when</span> <span >substring</span>(<span >@str</span>,<span >@intLen</span>,<span >1</span>) <span >>=</span> <span >'</span><span >嘸</span><span >'</span> <span >then</span> <span >'</span><span >M</span><span >'</span> <span >when</span> <span >substring</span>(<span >@str</span>,<span >@intLen</span>,<span >1</span>) <span >>=</span> <span >'</span><span >垃</span><span >'</span> <span >then</span> <span >'</span><span >L</span><span >'</span> <span >when</span> <span >substring</span>(<span >@str</span>,<span >@intLen</span>,<span >1</span>) <span >>=</span> <span >'</span><span >咔</span><span >'</span> <span >then</span> <span >'</span><span >K</span><span >'</span> <span >when</span> <span >substring</span>(<span >@str</span>,<span >@intLen</span>,<span >1</span>) <span >>=</span> <span >'</span><span >丌</span><span >'</span> <span >then</span> <span >'</span><span >J</span><span >'</span> <span >when</span> <span >substring</span>(<span >@str</span>,<span >@intLen</span>,<span >1</span>) <span >>=</span> <span >'</span><span >铪</span><span >'</span> <span >then</span> <span >'</span><span >H</span><span >'</span> <span >when</span> <span >substring</span>(<span >@str</span>,<span >@intLen</span>,<span >1</span>) <span >>=</span> <span >'</span><span >旮</span><span >'</span> <span >then</span> <span >'</span><span >G</span><span >'</span> <span >when</span> <span >substring</span>(<span >@str</span>,<span >@intLen</span>,<span >1</span>) <span >>=</span> <span >'</span><span >发</span><span >'</span> <span >then</span> <span >'</span><span >F</span><span >'</span> <span >when</span> <span >substring</span>(<span >@str</span>,<span >@intLen</span>,<span >1</span>) <span >>=</span> <span >'</span><span >妸</span><span >'</span> <span >then</span> <span >'</span><span >E</span><span >'</span> <span >when</span> <span >substring</span>(<span >@str</span>,<span >@intLen</span>,<span >1</span>) <span >>=</span> <span >'</span><span >咑</span><span >'</span> <span >then</span> <span >'</span><span >D</span><span >'</span> <span >when</span> <span >substring</span>(<span >@str</span>,<span >@intLen</span>,<span >1</span>) <span >>=</span> <span >'</span><span >嚓</span><span >'</span> <span >then</span> <span >'</span><span >C</span><span >'</span> <span >when</span> <span >substring</span>(<span >@str</span>,<span >@intLen</span>,<span >1</span>) <span >>=</span> <span >'</span><span >八</span><span >'</span> <span >then</span> <span >'</span><span >B</span><span >'</span> <span >when</span> <span >substring</span>(<span >@str</span>,<span >@intLen</span>,<span >1</span>) <span >>=</span> <span >'</span><span >吖</span><span >'</span> <span >then</span> <span >'</span><span >A</span><span >'</span> <span >else</span> <span >rtrim</span>(<span >ltrim</span>(<span >substring</span>(<span >@str</span>,<span >@intLen</span>,<span >1</span><span >))) </span><span >end</span> <span >--</span><span >对于汉字特殊字符，不生成<strong>拼音</strong>码 </span> <span >if</span> (<span >ascii</span>(<span >@temp</span>)<span >></span><span >127</span>) <span >set</span> <span >@temp</span> <span >=</span> <span >''</span> <span >--</span><span >对于英文中小括号，不生成<strong>拼音</strong>码 </span> <span >if</span> <span >@temp</span> <span >=</span> <span >'</span><span >(</span><span >'</span> <span >or</span> <span >@temp</span> <span >=</span> <span >'</span><span >)</span><span >'</span> <span >set</span> <span >@temp</span> <span >=</span> <span >''</span> <span >select</span> <span >@strRet</span> <span >=</span> <span >@temp</span> <span >+</span> <span >@strRet</span> <span >set</span> <span >@intLen</span> <span >=</span> <span >@intLen</span> <span >-</span> <span >1</span> <span >end</span> <span >return</span> <span >lower</span>(<span >@strRet</span><span >) </span><span >end</span>
执行语句
<span >SELECT</span> 需转换中文<strong>字段</strong>, dbo.fn_GetPy(中文<strong>字段</strong>) <span >AS</span><span > 列别名 </span><span >FROM</span> 表名称
感谢：Luckeryin
每个人都需要一台速度更快、更稳定的 PC。随着时间的推移，垃圾文件、旧注册表数据和不必要的后台进程会占用资源并降低性能。幸运的是，许多工具可以让 Windows 保持平稳运行。
Copyright 2014-2025 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号
191
收藏
取消收藏
