存储过程与用户自定义函数得到第一个汉字的拼音代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】存储过程与用户自定义函数得到第一个汉字的拼音
作者:Dezai.CN / 发布于2011/3/2/ 1073
<div><span style="color: #0000ff">CREATE</span><span style="color: #000000"> </span><span style="color: #0000ff">PROCEDURE</span><span style="color: #000000"> </span><span style="color: #ff0000">[</span><span style="color: #ff0000">dbo</span><span style="color: #ff0000">]</span><span style="color: #000000">.</span><span style="color: #ff0000">[</span><span style="color: #ff0000">CarType_SeriesEdit</span><span style="color: #ff0000">]</span><span style="color: #000000">
 </span><span style="color: #008000">@SeriesId</span><span style="color: #000000"> </span><span style="color: #0000ff">int</span><span style="color: #000000">,
 </span><span style="color: #008000">@BrandId</span><span style="color: #000000"> </span><span style="color: #0000ff">int</span><span style="color: #000000">,
 </span><span style="color: #008000">@SeriesName</span><span style="color: #000000"> </span><span style="color: #0000ff">varchar</span><span style="color: #000000">(</span><span style="color: #800000; font-weight: bold">128</span><span style="color: #000000">),
 </span><span style="color: #008000">@Letter</span><span style="color: #000000"> </span><span style="color: #0000ff">varchar</span><span style="color: #000000">(</span><span style="color: #800000; font-weight: bold">2</span><span style="color: #000000">),
 </span><span style="color: #008000">@Descript</span><span style="color: #000000"> </span><span style="color: #0000ff">varchar</span><span style="color: #000000">(</span><span style="color: #800000; font-weight: bold">1024</span><span style="color: #000000">),
 </span><span style="color: #008000">@ClassId</span><span style="color: #000000"> </span><span style="color: #0000ff">int</span><span style="color: #000000">
</span><span style="color: #0000ff">AS</span><span style="color: #000000">
</span><span style="color: #0000ff">SET</span><span style="color: #000000"> NOCOUNT </span><span style="color: #0000ff">ON</span><span style="color: #000000">
</span><span style="color: #0000ff">set</span><span style="color: #000000"> </span><span style="color: #008000">@letter</span><span style="color: #808080">=</span><span style="color: #000000">dbo.uf_getPY(</span><span style="color: #ff00ff">substring</span><span style="color: #000000">(</span><span style="color: #ff00ff">ltrim</span><span style="color: #000000">(</span><span style="color: #008000">@SeriesName</span><span style="color: #000000">),</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">,</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">))
</span><span style="color: #0000ff">IF</span><span style="color: #000000">( </span><span style="color: #008000">@SeriesId</span><span style="color: #808080"><>-</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">)
</span><span style="color: #0000ff">BEGIN</span><span style="color: #000000">
 </span><span style="color: #0000ff">UPDATE</span><span style="color: #000000"> </span><span style="color: #ff0000">[</span><span style="color: #ff0000">dbo</span><span style="color: #ff0000">]</span><span style="color: #000000">.</span><span style="color: #ff0000">[</span><span style="color: #ff0000">CarSeries</span><span style="color: #ff0000">]</span><span style="color: #000000"> </span><span style="color: #0000ff">SET</span><span style="color: #000000">
 </span><span style="color: #ff0000">[</span><span style="color: #ff0000">SeriesName</span><span style="color: #ff0000">]</span><span style="color: #000000"> </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #008000">@SeriesName</span><span style="color: #000000">,
 </span><span style="color: #ff0000">[</span><span style="color: #ff0000">Letter</span><span style="color: #ff0000">]</span><span style="color: #808080">=</span><span style="color: #008000">@Letter</span><span style="color: #000000">,
 </span><span style="color: #ff0000">[</span><span style="color: #ff0000">Descript</span><span style="color: #ff0000">]</span><span style="color: #000000"> </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #008000">@Descript</span><span style="color: #000000">,
 </span><span style="color: #ff0000">[</span><span style="color: #ff0000">ClassId</span><span style="color: #ff0000">]</span><span style="color: #808080">=</span><span style="color: #008000">@ClassId</span><span style="color: #000000">
 </span><span style="color: #0000ff">WHERE</span><span style="color: #000000">
 </span><span style="color: #ff0000">[</span><span style="color: #ff0000">SeriesId</span><span style="color: #ff0000">]</span><span style="color: #000000"> </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #008000">@SeriesId</span><span style="color: #000000">
</span><span style="color: #0000ff">END</span><span style="color: #000000">
</span><span style="color: #0000ff">ELSE</span><span style="color: #000000">
</span><span style="color: #0000ff">BEGIN</span><span style="color: #000000">
 </span><span style="color: #0000ff">INSERT</span><span style="color: #000000"> </span><span style="color: #0000ff">INTO</span><span style="color: #000000"> </span><span style="color: #ff0000">[</span><span style="color: #ff0000">dbo</span><span style="color: #ff0000">]</span><span style="color: #000000">.</span><span style="color: #ff0000">[</span><span style="color: #ff0000">CarSeries</span><span style="color: #ff0000">]</span><span style="color: #000000"> (
 </span><span style="color: #ff0000">[</span><span style="color: #ff0000">BrandId</span><span style="color: #ff0000">]</span><span style="color: #000000">,
 </span><span style="color: #ff0000">[</span><span style="color: #ff0000">SeriesName</span><span style="color: #ff0000">]</span><span style="color: #000000">,
 </span><span style="color: #ff0000">[</span><span style="color: #ff0000">Letter</span><span style="color: #ff0000">]</span><span style="color: #000000">,
 </span><span style="color: #ff0000">[</span><span style="color: #ff0000">Descript</span><span style="color: #ff0000">]</span><span style="color: #000000">,
 </span><span style="color: #ff0000">[</span><span style="color: #ff0000">ClassId</span><span style="color: #ff0000">]</span><span style="color: #000000">
 ) </span><span style="color: #0000ff">VALUES</span><span style="color: #000000"> (
 </span><span style="color: #008000">@BrandId</span><span style="color: #000000">,
 </span><span style="color: #008000">@SeriesName</span><span style="color: #000000">,
 </span><span style="color: #008000">@Letter</span><span style="color: #000000">,
 </span><span style="color: #008000">@Descript</span><span style="color: #000000">,
 </span><span style="color: #008000">@ClassId</span><span style="color: #000000">
 )
</span><span style="color: #0000ff">set</span><span style="color: #000000"> </span><span style="color: #008000">@SeriesId</span><span style="color: #808080">=</span><span style="color: #008000; font-weight: bold">@@identity</span><span style="color: #000000">
</span><span style="color: #0000ff">END</span><span style="color: #000000">
</span><span style="color: #0000ff">return</span><span style="color: #000000"> </span><span style="color: #008000">@SeriesId</span><span style="color: #000000">


</span><span style="color: #0000ff">GO</span><span style="color: #000000">

</span><span style="color: #0000ff">CREATE</span><span style="color: #000000"> </span><span style="color: #0000ff">function</span><span style="color: #000000"> </span><span style="color: #ff0000">[</span><span style="color: #ff0000">dbo</span><span style="color: #ff0000">]</span><span style="color: #000000">.</span><span style="color: #ff0000">[</span><span style="color: #ff0000">uf_getPY</span><span style="color: #ff0000">]</span><span style="color: #000000">
( 
 </span><span style="color: #008000">@str</span><span style="color: #000000"> </span><span style="color: #0000ff">nvarchar</span><span style="color: #000000">(</span><span style="color: #800000; font-weight: bold">4000</span><span style="color: #000000">) 
) 
</span><span style="color: #0000ff">returns</span><span style="color: #000000"> </span><span style="color: #0000ff">nvarchar</span><span style="color: #000000">(</span><span style="color: #800000; font-weight: bold">4000</span><span style="color: #000000">) 
</span><span style="color: #0000ff">as</span><span style="color: #000000"> 
</span><span style="color: #0000ff">begin</span><span style="color: #000000"> 
 </span><span style="color: #0000ff">declare</span><span style="color: #000000"> </span><span style="color: #008000">@word</span><span style="color: #000000"> </span><span style="color: #0000ff">nchar</span><span style="color: #000000">(</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">),</span><span style="color: #008000">@PY</span><span style="color: #000000"> </span><span style="color: #0000ff">nvarchar</span><span style="color: #000000">(</span><span style="color: #800000; font-weight: bold">4000</span><span style="color: #000000">) 
 </span><span style="color: #0000ff">set</span><span style="color: #000000"> </span><span style="color: #008000">@PY</span><span style="color: #808080">=</span><span style="color: #ff0000">''</span><span style="color: #000000"> 
 </span><span style="color: #0000ff">while</span><span style="color: #000000"> </span><span style="color: #ff00ff">len</span><span style="color: #000000">(</span><span style="color: #008000">@str</span><span style="color: #000000">)</span><span style="color: #808080">></span><span style="color: #800000; font-weight: bold">0</span><span style="color: #000000"> 
 </span><span style="color: #0000ff">begin</span><span style="color: #000000"> 
 </span><span style="color: #0000ff">set</span><span style="color: #000000"> </span><span style="color: #008000">@word</span><span style="color: #808080">=left</span><span style="color: #000000">(</span><span style="color: #008000">@str</span><span style="color: #000000">,</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">) 
 </span><span style="color: #008080">--</span><span style="color: #008080">如果非汉字字符，返回原字符 </span><span style="color: #008080">
</span><span style="color: #000000"> </span><span style="color: #0000ff">set</span><span style="color: #000000"> </span><span style="color: #008000">@PY</span><span style="color: #808080">=</span><span style="color: #008000">@PY</span><span style="color: #808080">+</span><span style="color: #000000">(</span><span style="color: #ff00ff">case</span><span style="color: #000000"> </span><span style="color: #0000ff">when</span><span style="color: #000000"> </span><span style="color: #ff00ff">unicode</span><span style="color: #000000">(</span><span style="color: #008000">@word</span><span style="color: #000000">) </span><span style="color: #808080">between</span><span style="color: #000000"> </span><span style="color: #800000; font-weight: bold">19968</span><span style="color: #000000"> </span><span style="color: #808080">and</span><span style="color: #000000"> </span><span style="color: #800000; font-weight: bold">19968</span><span style="color: #808080">+</span><span style="color: #800000; font-weight: bold">20901</span><span style="color: #000000"> 
 </span><span style="color: #0000ff">then</span><span style="color: #000000"> ( 
 </span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #0000ff">top</span><span style="color: #000000"> </span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000"> PY
</span><span style="color: #0000ff">from</span><span style="color: #000000"> 
 ( 
 </span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">A</span><span style="color: #ff0000">'</span><span style="color: #000000"> </span><span style="color: #0000ff">as</span><span style="color: #000000"> PY,N</span><span style="color: #ff0000">'</span><span style="color: #ff0000">驁</span><span style="color: #ff0000">'</span><span style="color: #000000"> </span><span style="color: #0000ff">as</span><span style="color: #000000"> word 
 </span><span style="color: #0000ff">union</span><span style="color: #000000"> </span><span style="color: #808080">all</span><span style="color: #000000"> </span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">B</span><span style="color: #ff0000">'</span><span style="color: #000000">,N</span><span style="color: #ff0000">'</span><span style="color: #ff0000">簿</span><span style="color: #ff0000">'</span><span style="color: #000000"> 
 </span><span style="color: #0000ff">union</span><span style="color: #000000"> </span><span style="color: #808080">all</span><span style="color: #000000"> </span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">C</span><span style="color: #ff0000">'</span><span style="color: #000000">,N</span><span style="color: #ff0000">'</span><span style="color: #ff0000">錯</span><span style="color: #ff0000">'</span><span style="color: #000000"> 
 </span><span style="color: #0000ff">union</span><span style="color: #000000"> </span><span style="color: #808080">all</span><span style="color: #000000"> </span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">D</span><span style="color: #ff0000">'</span><span style="color: #000000">,N</span><span style="color: #ff0000">'</span><span style="color: #ff0000">鵽</span><span style="color: #ff0000">'</span><span style="color: #000000"> 
 </span><span style="color: #0000ff">union</span><span style="color: #000000"> </span><span style="color: #808080">all</span><span style="color: #000000"> </span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">E</span><span style="color: #ff0000">'</span><span style="color: #000000">,N</span><span style="color: #ff0000">'</span><span style="color: #ff0000">樲</span><span style="color: #ff0000">'</span><span style="color: #000000"> 
 </span><span style="color: #0000ff">union</span><span style="color: #000000"> </span><span style="color: #808080">all</span><span style="color: #000000"> </span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">F</span><span style="color: #ff0000">'</span><span style="color: #000000">,N</span><span style="color: #ff0000">'</span><span style="color: #ff0000">鰒</span><span style="color: #ff0000">'</span><span style="color: #000000"> 
 </span><span style="color: #0000ff">union</span><span style="color: #000000"> </span><span style="color: #808080">all</span><span style="color: #000000"> </span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">G</span><span style="color: #ff0000">'</span><span style="color: #000000">,N</span><span style="color: #ff0000">'</span><span style="color: #ff0000">腂</span><span style="color: #ff0000">'</span><span style="color: #000000"> 
 </span><span style="color: #0000ff">union</span><span style="color: #000000"> </span><span style="color: #808080">all</span><span style="color: #000000"> </span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">H</span><span style="color: #ff0000">'</span><span style="color: #000000">,N</span><span style="color: #ff0000">'</span><span style="color: #ff0000">夻</span><span style="color: #ff0000">'</span><span style="color: #000000"> 
 </span><span style="color: #0000ff">union</span><span style="color: #000000"> </span><span style="color: #808080">all</span><span style="color: #000000"> </span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">J</span><span style="color: #ff0000">'</span><span style="color: #000000">,N</span><span style="color: #ff0000">'</span><span style="color: #ff0000">攈</span><span style="color: #ff0000">'</span><span style="color: #000000"> 
 </span><span style="color: #0000ff">union</span><span style="color: #000000"> </span><span style="color: #808080">all</span><span style="color: #000000"> </span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">K</span><span style="color: #ff0000">'</span><span style="color: #000000">,N</span><span style="color: #ff0000">'</span><span style="color: #ff0000">穒</span><span style="color: #ff0000">'</span><span style="color: #000000"> 
 </span><span style="color: #0000ff">union</span><span style="color: #000000"> </span><span style="color: #808080">all</span><span style="color: #000000"> </span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">L</span><span style="color: #ff0000">'</span><span style="color: #000000">,N</span><span style="color: #ff0000">'</span><span style="color: #ff0000">鱳</span><span style="color: #ff0000">'</span><span style="color: #000000"> 
 </span><span style="color: #0000ff">union</span><span style="color: #000000"> </span><span style="color: #808080">all</span><span style="color: #000000"> </span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">M</span><span style="color: #ff0000">'</span><span style="color: #000000">,N</span><span style="color: #ff0000">'</span><span style="color: #ff0000">旀</span><span style="color: #ff0000">'</span><span style="color: #000000"> 
 </span><span style="color: #0000ff">union</span><span style="color: #000000"> </span><span style="color: #808080">all</span><span style="color: #000000"> </span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">N</span><span style="color: #ff0000">'</span><span style="color: #000000">,N</span><span style="color: #ff0000">'</span><span style="color: #ff0000">桛</span><span style="color: #ff0000">'</span><span style="color: #000000"> 
 </span><span style="color: #0000ff">union</span><span style="color: #000000"> </span><span style="color: #808080">all</span><span style="color: #000000"> </span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">O</span><span style="color: #ff0000">'</span><span style="color: #000000">,N</span><span style="color: #ff0000">'</span><span style="color: #ff0000">漚</span><span style="color: #ff0000">'</span><span style="color: #000000"> 
 </span><span style="color: #0000ff">union</span><span style="color: #000000"> </span><span style="color: #808080">all</span><span style="color: #000000"> </span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">P</span><span style="color: #ff0000">'</span><span style="color: #000000">,N</span><span style="color: #ff0000">'</span><span style="color: #ff0000">曝</span><span style="color: #ff0000">'</span><span style="color: #000000"> 
 </span><span style="color: #0000ff">union</span><span style="color: #000000"> </span><span style="color: #808080">all</span><span style="color: #000000"> </span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">Q</span><span style="color: #ff0000">'</span><span style="color: #000000">,N</span><span style="color: #ff0000">'</span><span style="color: #ff0000">囕</span><span style="color: #ff0000">'</span><span style="color: #000000"> 
 </span><span style="color: #0000ff">union</span><span style="color: #000000"> </span><span style="color: #808080">all</span><span style="color: #000000"> </span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">R</span><span style="color: #ff0000">'</span><span style="color: #000000">,N</span><span style="color: #ff0000">'</span><span style="color: #ff0000">鶸</span><span style="color: #ff0000">'</span><span style="color: #000000"> 
 </span><span style="color: #0000ff">union</span><span style="color: #000000"> </span><span style="color: #808080">all</span><span style="color: #000000"> </span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">S</span><span style="color: #ff0000">'</span><span style="color: #000000">,N</span><span style="color: #ff0000">'</span><span style="color: #ff0000">蜶</span><span style="color: #ff0000">'</span><span style="color: #000000"> 
 </span><span style="color: #0000ff">union</span><span style="color: #000000"> </span><span style="color: #808080">all</span><span style="color: #000000"> </span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">T</span><span style="color: #ff0000">'</span><span style="color: #000000">,N</span><span style="color: #ff0000">'</span><span style="color: #ff0000">籜</span><span style="color: #ff0000">'</span><span style="color: #000000"> 
 </span><span style="color: #0000ff">union</span><span style="color: #000000"> </span><span style="color: #808080">all</span><span style="color: #000000"> </span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">W</span><span style="color: #ff0000">'</span><span style="color: #000000">,N</span><span style="color: #ff0000">'</span><span style="color: #ff0000">鶩</span><span style="color: #ff0000">'</span><span style="color: #000000"> 
 </span><span style="color: #0000ff">union</span><span style="color: #000000"> </span><span style="color: #808080">all</span><span style="color: #000000"> </span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">X</span><span style="color: #ff0000">'</span><span style="color: #000000">,N</span><span style="color: #ff0000">'</span><span style="color: #ff0000">鑂</span><span style="color: #ff0000">'</span><span style="color: #000000"> 
 </span><span style="color: #0000ff">union</span><span style="color: #000000"> </span><span style="color: #808080">all</span><span style="color: #000000"> </span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">Y</span><span style="color: #ff0000">'</span><span style="color: #000000">,N</span><span style="color: #ff0000">'</span><span style="color: #ff0000">韻</span><span style="color: #ff0000">'</span><span style="color: #000000"> 
 </span><span style="color: #0000ff">union</span><span style="color: #000000"> </span><span style="color: #808080">all</span><span style="color: #000000"> </span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">Z</span><span style="color: #ff0000">'</span><span style="color: #000000">,N</span><span style="color: #ff0000">'</span><span style="color: #ff0000">咗</span><span style="color: #ff0000">'</span><span style="color: #000000"> 
 ) T 
 </span><span style="color: #0000ff">where</span><span style="color: #000000"> word</span><span style="color: #808080">>=</span><span style="color: #008000">@word</span><span style="color: #000000"> collate Chinese_PRC_CS_AS_KS_WS 
 </span><span style="color: #0000ff">order</span><span style="color: #000000"> </span><span style="color: #0000ff">by</span><span style="color: #000000"> PY </span><span style="color: #0000ff">ASC</span><span style="color: #000000"> 
 ) 
 </span><span style="color: #0000ff">else</span><span style="color: #000000"> </span><span style="color: #ff00ff">upper</span><span style="color: #000000">(</span><span style="color: #008000">@word</span><span style="color: #000000">)
 </span><span style="color: #0000ff">end</span><span style="color: #000000">) 
 </span><span style="color: #0000ff">set</span><span style="color: #000000"> </span><span style="color: #008000">@str</span><span style="color: #808080">=right</span><span style="color: #000000">(</span><span style="color: #008000">@str</span><span style="color: #000000">,</span><span style="color: #ff00ff">len</span><span style="color: #000000">(</span><span style="color: #008000">@str</span><span style="color: #000000">)</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">) 
 </span><span style="color: #0000ff">end</span><span style="color: #000000"> 
 </span><span style="color: #0000ff">return</span><span style="color: #000000"> </span><span style="color: #008000">@PY</span><span style="color: #000000"> 
</span><span style="color: #0000ff">end</span><span style="color: #000000">
</span></div>
试试其它关键字
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)