目前工作中遇到一个情况，需要将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
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
