SQL Server 字段提取拼音首字母

目前工作中遇到一个情况，需要将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