❶ sql語句提取出中文的拼音首字母
正好最近收藏了一個 你可以看下思路
--將中文字元串轉化成文字首拼音的組合
create function fun_getPY(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @word nchar(1),@PY nvarchar(4000)
set @PY=''
while len(@str)>0
begin
set @word=left(@str,1)
--如果非漢字字元，返回原字元
set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901
then (select top 1 PY from (
select 'A' as PY,N'驁' as word
union all select 'B',N'簿'
union all select 'C',N'錯'
union all select 'D',N'鵽'
union all select 'E',N'樲'
union all select 'F',N'鰒'
union all select 'G',N'腂'
union all select 'H',N'夻'
union all select 'J',N'攈'
union all select 'K',N'穒'
union all select 'L',N'鱳'
union all select 'M',N'旀'
union all select 'N',N'桛'
union all select 'O',N'漚'
union all select 'P',N'曝'
union all select 'Q',N'囕'
union all select 'R',N'鶸'
union all select 'S',N'蜶'
union all select 'T',N'籜'
union all select 'W',N'鶩'
union all select 'X',N'鑂'
union all select 'Y',N'韻'
union all select 'Z',N'咗'
) T
where word>=@word collate Chinese_PRC_CS_AS_KS_WS
order by PY ASC) else @word end)
set @str=right(@str,len(@str)-1)
end
return @PY
end
--函數調用實例：
select dbo.fun_getPY('中華人民共和國AAA01')
/*
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ZHRMGHGAAA01
(1 行受影響)
*/
❷ SQL 語句獲取多漢字拼音縮寫如何寫成SQL update 更新語句
什麼資料庫，如果是oracle的提供給你,不用客氣,就是分少了些。
CREATE OR REPLACE FUNCTION PUB_GET_PY(p_漢字 CHAR DEFAULT '%')
RETURN CHAR
IS
/********************************************************************
過程名稱：PUB_GET_PY
功能：生成漢字拼音的首字元
********************************************************************/
v_漢字內碼 NUMBER(8);
v_拼音 CHAR(1);
BEGIN
v_漢字內碼 := ASCII(p_漢字);
IF v_漢字內碼 BETWEEN 45217 AND 45252 THEN
v_拼音 := 'A';
ELSIF v_漢字內碼 BETWEEN 45253 AND 45760 THEN
v_拼音 := 'B';
ELSIF v_漢字內碼 BETWEEN 45761 AND 46317 THEN
v_拼音 := 'C';
ELSIF v_漢字內碼 BETWEEN 46318 AND 46825 THEN
v_拼音 := 'D';
ELSIF v_漢字內碼 BETWEEN 46826 AND 47009 THEN
v_拼音 := 'E';
ELSIF v_漢字內碼 BETWEEN 47010 AND 47296 THEN
v_拼音 := 'F';
ELSIF v_漢字內碼 BETWEEN 47297 AND 47613 THEN
v_拼音 := 'G';
ELSIF v_漢字內碼 BETWEEN 47614 AND 48116 THEN
v_拼音 := 'H';
ELSIF v_漢字內碼 BETWEEN 48117 AND 49061 THEN
v_拼音 := 'J';
ELSIF v_漢字內碼 BETWEEN 49062 AND 49323 THEN
v_拼音 := 'K';
ELSIF v_漢字內碼 BETWEEN 49324 AND 49895 THEN
v_拼音 := 'L';
ELSIF v_漢字內碼 BETWEEN 49896 AND 50370 THEN
v_拼音 := 'M';
ELSIF v_漢字內碼 BETWEEN 50371 AND 50613 THEN
v_拼音 := 'N';
ELSIF v_漢字內碼 BETWEEN 50614 AND 50621 THEN
v_拼音 := 'O';
ELSIF v_漢字內碼 BETWEEN 50622 AND 50925 THEN
v_拼音 := 'P';
ELSIF v_漢字內碼 BETWEEN 50926 AND 51386 THEN
v_拼音 := 'Q';
ELSIF v_漢字內碼 BETWEEN 51387 AND 51445 THEN
v_拼音 := 'R';
ELSIF v_漢字內碼 BETWEEN 51446 AND 52217 THEN
v_拼音 := 'S';
ELSIF v_漢字內碼 BETWEEN 52218 AND 52697 THEN
v_拼音 := 'T';
ELSIF v_漢字內碼 BETWEEN 52698 AND 52979 THEN
v_拼音 := 'W';
ELSIF v_漢字內碼 BETWEEN 52980 AND 53640 THEN
v_拼音 := 'X';
ELSIF v_漢字內碼 BETWEEN 53641 AND 54480 THEN
v_拼音 := 'Y';
ELSIF v_漢字內碼 BETWEEN 54481 AND 55289 THEN
v_拼音 := 'Z';
ELSE
v_拼音 := NULL;
END IF;
RETURN v_拼音;
END PUB_GET_PY;
CREATE OR REPLACE FUNCTION PUB_GET_STRING_PY(strs varchar2)
return varchar2
is
/***********************************
過程名稱：PUB_GET_STRING_PY
功能：生成漢字字元串的拼音首字元
**********************************/
v_length number;
v_char varchar2(2);
v_charlength number;
v_charPY varchar2(2);
i int:=1;
myresult varchar2(500):='';
begin
--獲取字元串長度
v_length:=length(strs);
while i<=v_length
loop
--按順序取得字元
v_char:=substr(strs,i,1);
v_charlength:=lengthb(rtrim(v_char));
v_charPY:=rtrim(v_char);
--判斷是否是漢字
if v_charlength=2 then
v_charPY:=pub_get_py(rtrim(v_char));
end if;
--將拼音進行連接
myresult:=myresult || v_charPY;
i:=i+1;
end loop;
return myresult;
end PUB_GET_STRING_PY;
/
❸ sql 取中文的拼音首字母
Create Function f_GetallPy(@chn nvarchar(100))
returns varchar(30)
as
begin
declare @i int,@j int,@result varchar(100)
set @result=''
set @i=len(@chn)
set @j=1
while @j<=@i
begin
set @result = @result + dbo.f_GetPy(substring(@chn,@j,1))
set @j=@j+1
end
return @result
end
這樣你就明白了吧
❹ 怎麼在SQL SERVER中把漢字 轉化為 拼音碼
CREATE FUNCTION Fun_GetPY
(
@Str NVARCHAR(4000)
)
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE @Word NCHAR(1)
DECLARE @PY NVARCHAR(4000)
SET @PY = ''
WHILE LEN(@Str) > 0
BEGIN
SET @Word = LEFT(@Str, 1)
--如果非漢字字元，返回原字元
SET @PY = @PY + (CASE WHEN UNICODE(@Word) BETWEEN 19968 AND 19968 + 20901
THEN (SELECT TOP 1 PY FROM(
SELECT 'A' AS PY, N'驁' AS WORD
UNION ALL SELECT 'B',N'簿'
UNION ALL SELECT 'C',N'錯'
UNION ALL SELECT 'D',N'鵽'
UNION ALL SELECT 'E',N'樲'
UNION ALL SELECT 'F',N'鰒'
UNION ALL SELECT 'G',N'腂'
UNION ALL SELECT 'H',N'夻'
UNION ALL SELECT 'J',N'攈'
UNION ALL SELECT 'K',N'穒'
UNION ALL SELECT 'L',N'鱳'
UNION ALL SELECT 'M',N'旀'
UNION ALL SELECT 'N',N'桛'
UNION ALL SELECT 'O',N'漚'
UNION ALL SELECT 'P',N'曝'
UNION ALL SELECT 'Q',N'囕'
UNION ALL SELECT 'R',N'鶸'
UNION ALL SELECT 'S',N'蜶'
UNION ALL SELECT 'T',N'籜'
UNION ALL SELECT 'W',N'鶩'
UNION ALL SELECT 'X',N'鑂'
UNION ALL SELECT 'Y',N'韻'
UNION ALL SELECT 'Z',N'咗') T
WHERE WORD >= @Word COLLATE Chinese_PRC_CS_AS_KS_WS
ORDER BY PY ASC)
ELSE @Word
END)
SET @Str = RIGHT(@Str, LEN(@Str) - 1)
END
RETURN @PY
END
調用這個函數就可以
❺ SQL 獲取名字拼音
做兩個函數。一個取漢字拼音首字母，一個取完整拼音
下面代碼是晚上搜到的：
--取漢字首字母函數
create function f_GetPy(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @strlen int,@re nvarchar(4000)
declare @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1))
insert into @t(chr,letter)
select '吖','A' union all select '八','B' union all
select '嚓','C' union all select '咑','D' union all
select '妸','E' union all select '發','F' union all
select '旮','G' union all select '鉿','H' union all
select '丌','J' union all select '咔','K' union all
select '垃','L' union all select '嘸','M' union all
select '拏','N' union all select '噢','O' union all
select '妑','P' union all select '七','Q' union all
select '呥','R' union all select '仨','S' union all
select '他','T' union all select '屲','W' union all
select '夕','X' union all select '丫','Y' union all
select '帀','Z'
select @strlen=len(@str),@re=''
while @strlen>0
begin
select top 1 @re=letter+@re,@strlen=@strlen-1
from @t a where chr<=substring(@str,@strlen,1)
order by chr desc
if @@rowcount=0
select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1
end
return(@re)
end
go
--使用
select id,name,dbo.f_GetPy(name) from mytable
取完整拼音的跟上述f_GetPy函數類似，只是臨時表要多寫很多行，拼音有多少種組合就寫多少行，大概六七百行吧。你自己找找相關的其他語言轉拼音全碼的代碼，實現方式是相通的。
❻ 如何用db2的sql寫一個獲取漢字拼音首字母的
DECLARE @str VARCHAR(100)
SET @str = '漢字的首字母'
SELECT @str AS A, dbo.fun_getPY(@str) AS B
先執行上面的那個函數，然後在執行下面的那個語句，就可以得到你要的結果了。
❼ sql 漢字轉拼音，若有重復的在拼音加1
題主意思，表a已經有欄位hz，現在要更新py這列？這樣的話，只要update就行了，為什麼說最好是函數實現呢？還是說題主想要selecthz,func(hz)froma這樣的呢？
函數。
首先需要一個解析漢字到拼音的方法，這里有一個包，你可以復制過去直接執行。
腳本地址：
http://www.cnblogs.com/mellowsmile/p/4601288.html
附代碼：
--不是我不貼，代碼太多貼不上，題主還是去上面那個地址復制吧，注意復制全部。
在SQL窗口執行。
我這里建了一個測試表test，數據顯示如下：
select a.* from test a;
題主，如此，可否？
❽ SQL Server 如何提取漢字首字母
代碼如下：
USE [database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[getPinYin] (@str varchar(500) = '')
RETURNS varchar(500) AS
BEGIN
Declare @strlen int,
@return varchar(500),
@ii int,
@c char(1),
@chn nchar(1)
--//初始化變數
Declare @pytable table(
chn char(2) COLLATE Chinese_PRC_CS_AS NOT NULL,
py char(1) COLLATE Chinese_PRC_CS_AS NULL,
PRIMARY KEY (chn)
)
insert into @pytable values('吖', 'A')
insert into @pytable values('八', 'B')
insert into @pytable values('嚓', 'C')
insert into @pytable values('咑', 'D')
insert into @pytable values('妸', 'E')
insert into @pytable values('發', 'F')
insert into @pytable values('旮', 'G')
insert into @pytable values('鉿', 'H')
insert into @pytable values('丌', 'I')
insert into @pytable values('丌', 'J')
insert into @pytable values('咔', 'K')
insert into @pytable values('垃', 'L')
insert into @pytable values('嘸', 'M')
insert into @pytable values('拏', 'N')
insert into @pytable values('噢', 'O')
insert into @pytable values('妑', 'P')
insert into @pytable values('七', 'Q')
insert into @pytable values('呥', 'R')
insert into @pytable values('仨', 'S')
insert into @pytable values('他', 'T')
--insert into @pytable values('屲', 'U')
--insert into @pytable values('屲', 'V')
insert into @pytable values('屲', 'W')
insert into @pytable values('夕', 'X')
insert into @pytable values('丫', 'Y')
insert into @pytable values('帀', 'Z')
select @strlen = len(@str), @return = '', @ii = 0
//循環整個字元串,用拼音的首字母替換漢字
while @ii < @strlen
begin
select @ii = @ii + 1, @chn = substring(@str , @ii, 1)
if @chn > 'z' --//檢索輸入的字元串中有中文字元
SELECT @c = max(py)
FROM @pytable
where chn <= @chn
else
set @c=@chn
set @return=@return+@c
end
return @return
END
❾ SQL SERVER 獲取字元串拼音函數，求優化
按你這種寫法看起來優化程度有限。可以考慮吧substring改成left。每次循環完把第一個字元去掉。可能比substring 快點。
另外有個思路。就是需要替換成字母的字已經是確認的了。我數了一下是23個字
那你循環23遍 吧對應的字替換成對應的字母。 這樣循環次數就固定下來，應該比傳進來的字元串一個個循環次數少 。效率肯定會高！
❿ sql存儲過程獲取漢字拼音頭字母函數
復制代碼
代碼如下:
--函數
CREATE
function
fn_GetPy(@str
nvarchar(4000))
returns
nvarchar(4000)
--WITH
ENCRYPTION
as
begin
declare
@intLenint
declare
@strRetnvarchar(4000)
declare
@temp
nvarchar(100)
set
@intLen
=
len(@str)
set
@strRet
=
''
while
@intLen
>
0
begin
set
@temp
=
''
select
@temp
=
case
when
substring(@str,@intLen,1)
>=
'帀'
then
'Z'
when
substring(@str,@intLen,1)
>=
'丫'
then
'Y'
when
substring(@str,@intLen,1)
>=
'夕'
then
'X'
when
substring(@str,@intLen,1)
>=
'屲'
then
'W'
when
substring(@str,@intLen,1)
>=
'他'
then
'T'
when
substring(@str,@intLen,1)
>=
'仨'
then
'S'
when
substring(@str,@intLen,1)
>=
'呥'
then
'R'
when
substring(@str,@intLen,1)
>=
'七'
then
'Q'
when
substring(@str,@intLen,1)
>=
'妑'
then
'P'
when
substring(@str,@intLen,1)
>=
'噢'
then
'O'
when
substring(@str,@intLen,1)
>=
'拏'
then
'N'
when
substring(@str,@intLen,1)
>=
'嘸'
then
'M'
when
substring(@str,@intLen,1)
>=
'垃'
then
'L'
when
substring(@str,@intLen,1)
>=
'咔'
then
'K'
when
substring(@str,@intLen,1)
>=
'丌'
then
'J'
when
substring(@str,@intLen,1)
>=
'鉿'
then
'H'
when
substring(@str,@intLen,1)
>=
'旮'
then
'G'
when
substring(@str,@intLen,1)
>=
'發'
then
'F'
when
substring(@str,@intLen,1)
>=
'妸'
then
'E'
when
substring(@str,@intLen,1)
>=
'咑'
then
'D'
when
substring(@str,@intLen,1)
>=
'嚓'
then
'C'
when
substring(@str,@intLen,1)
>=
'八'
then
'B'
when
substring(@str,@intLen,1)
>=
'吖'
then
'A'
else
rtrim(ltrim(substring(@str,@intLen,1)))
end
--對於漢字特殊字元，不生成拼音碼
if
(ascii(@temp)>127)
set
@temp
=
''
--對於英文中小括弧，不生成拼音碼
if
@temp
=
'('
or
@temp
=
')'
set
@temp
=
''
select
@strRet
=
@temp
+
@strRet
set
@intLen
=
@intLen
-
1
end
return
lower(@strRet)
end
go
--調用
select
dbo.fn_getpy('張三')
--返回：zs
答！：
2:
取漢字拼音首字母的存儲過程
Create
function
fun_getPY
(
@str
nvarchar(4000)
)
returns
nvarchar(4000)
as
begin
declare
@word
nchar(1),@PY
nvarchar(4000)
set
@PY=''
while
len(@str)>0
begin
set
@word=left(@str,1)
--如果非漢字字元，返回原字元
set
@PY=@PY+(case
when
unicode(@word)
between
19968
and
19968+20901
then
(
select
top
1
PY
from
(
select
'A'
as
PY,N'驁'
as
word
union
all
select
'B',N'簿'
union
all
select
'C',N'錯'
union
all
select
'D',N'鵽'
union
all
select
'E',N'樲'
union
all
select
'F',N'鰒'
union
all
select
'G',N'腂'
union
all
select
'H',N'夻'
union
all
select
'J',N'攈'
union
all
select
'K',N'穒'
union
all
select
'L',N'鱳'
union
all
select
'M',N'旀'
union
all
select
'N',N'桛'
union
all
select
'O',N'漚'
union
all
select
'P',N'曝'
union
all
select
'Q',N'囕'
union
all
select
'R',N'鶸'
union
all
select
'S',N'蜶'
union
all
select
'T',N'籜'
union
all
select
'W',N'鶩'
union
all
select
'X',N'鑂'
union
all
select
'Y',N'韻'
union
all
select
'Z',N'咗'
)
T
where
word>=@word
collate
Chinese_PRC_CS_AS_KS_WS
order
by
PY
ASC
)
else
@word
end)
set
@str=right(@str,len(@str)-1)
end
return
@PY
end