常用sql语句汇集 | 红颜丽人

常用sql语句汇集。

–1，SQL Server Split函数

–Author:zc_0101

–说明：

–支持分割符多字节

–使用方法

–Select * FROM DBO.F_SQLSERVER_SPLIT(’1203401230105045′,’0′)

–select * from DBO.F_SQLSERVER_SPLIT(‘abc1234a12348991234′,’1234′)

–Select * from DBO.F_SQLSERVER_SPLIT(‘ABC’,',’)

CREATE FUNCTION F_SQLSERVER_SPLIT(@Long_str varchar(8000),@split_str varchar(100))

RETURNS @tmp TABLE(

ID inT IDENTITY PRIMARY KEY,

short_str varchar(8000)

)

BEGIN

DECLARE @long_str_Tmp varchar(8000),@short_str varchar(8000),@split_str_length int

SET @split_str_length = LEN(@split_str)

IF CHARINDEX(@split_str,@Long_str)=1

SET @long_str_Tmp=SUBSTRING(@Long_str,@split_str_length+1,LEN(@Long_str)-@split_str_length)

ELSE

SET @long_str_Tmp=@Long_str

IF CHARINDEX(REVERSE(@split_str),REVERSE(@long_str_Tmp))>1

SET @long_str_Tmp=@long_str_Tmp+@split_str

ELSE

SET @long_str_Tmp=@long_str_Tmp

IF CHARINDEX(@split_str,@long_str_Tmp)=0

Insert INTO @tmp select @long_str_Tmp

ELSE

BEGIN

WHILE CHARINDEX(@split_str,@long_str_Tmp)>0

BEGIN

SET @short_str=SUBSTRING(@long_str_Tmp,1,CHARINDEX(@split_str,@long_str_Tmp)-1)

DECLARE @long_str_Tmp_LEN INT,@split_str_Position_END int

SET @long_str_Tmp_LEN = LEN(@long_str_Tmp)

SET @split_str_Position_END = LEN(@short_str)+@split_str_length

SET @long_str_Tmp=REVERSE(SUBSTRING(REVERSE(@long_str_Tmp),1,@long_str_Tmp_LEN-@split_str_Position_END))

IF @short_str<>” Insert INTO @tmp select @short_str

END

RETURN

END

二读取数据库结构

SELECT

表名 = Case When A.colorder=1 Then D.name Else ” End,

表说明 = Case When A.colorder=1 Then isnull(F.value,”) Else ” End,

字段序号 = A.colorder,

字段名 = A.name,

字段说明 = isnull(G.[value],”),

标识 = Case When COLUMNPROPERTY( A.id,A.name,’IsIdentity’)=1 Then ‘√’Else ” End,

主键 = Case When exists(SELECT 1 FROM sysobjects Where xtype=’PK’ and parent_obj=A.id and name in (

SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = A.id AND colid=A.colid))) then ‘√’ else ” end,

类型 = B.name,

占用字节数 = A.Length,

长度 = COLUMNPROPERTY(A.id,A.name,’PRECISION’),

小数位数 = isnull(COLUMNPROPERTY(A.id,A.name,’Scale’),0),

允许空 = Case When A.isnullable=1 Then ‘√’Else ” End,

默认值 = isnull(E.Text,”)

FROM

syscolumns A

Left Join

systypes B

A.xusertype=B.xusertype

Inner Join

sysobjects D

A.id=D.id and D.xtype=’U’ and D.name<>’dtproperties’

Left Join

syscomments E

A.cdefault=E.id

Left Join

sys.extended_properties G

A.id=G.major_id and A.colid=G.minor_id

Left Join

sys.extended_properties F

D.id=F.major_id and F.minor_id=0

–where d.name=’OrderInfo’ –如果只查询指定表,加上此条件

Order By

A.id,A.colorder

三分页函数（sql server 2005）以上

ALTER PROCEDURE [dbo].[sp_common_Paging]

@Tables nvarchar(1000), –表名,多表时请使用 tA a inner join tB b On a.AID = b.AID

@Sort nvarchar(200) = ”, –排序字段

@PageNumber int = 1, –开始页码

@PageSize int = 10, –页大小

@Fields nvarchar(1000) = ‘*’,–读取字段

@Filter nvarchar(4000) = NULL,–Where条件

@Count int OUTPUT — –获得总记录数

DECLARE @strFilter nvarchar(2000)

declare @sql nvarchar(4000)

DECLARE @sortsql NVARCHAR(2000)

IF @Filter IS NOT NULL AND @Filter != ”

BEGIN

SET @strFilter = ‘ WHERE 1=1 ‘ + @Filter + ‘ ‘

END

ELSE

BEGIN

SET @strFilter = ”

END

SET @sql = N’ SET @p = (SELECT COUNT(*) FROM (select ‘ + @Fields + ‘ from ‘ +@Tables + @strFilter + ‘) as a)’;

EXEC sp_executesql

@stmt = @sql,

@params = N’@p AS INT OUTPUT’,

@p = @Count OUTPUT

–重置为空

SET @sql=”

BEGIN

if len(@Sort)>0

set @sortsql = ‘ORDER BY ‘ + @Sort

IF @PageNumber < 1

SET @PageNumber = 1

if @PageNumber = 1 –第一页提高性能

begin

set @sql = ‘select top ‘ + str(@PageSize) +’ ‘+@Fields+ ‘ from ‘ + @Tables + ‘ ‘ + @strFilter + ‘ ORDER BY ‘+ @Sort

end

else

begin

DECLARE @START_ID varchar(50)

DECLARE @END_ID varchar(50)

SET @START_ID = convert(varchar(50),(@PageNumber – 1) * @PageSize + 1)

SET @END_ID = convert(varchar(50),@PageNumber * @PageSize)

set @sql = ‘ SELECT * ‘ + –+@Fields+ ‘

‘FROM (SELECT ROW_NUMBER() OVER(ORDER BY ‘+@Sort+’) AS rownum,

‘+@Fields+ ‘

FROM ‘+@Tables+’ ‘ +@strFilter+’) AS D

WHERE rownum BETWEEN ‘+@START_ID+’ AND ‘ +@END_ID +@sortsql

END

–write sql for test

INSERT INTO t VALUES (@sql);

EXEC(@sql)

四，sql server padleft 函数

CREATE function [dbo].[PadLeft](@num varchar(16),@paddingChar char(1),@totalWidth int)

returns varchar(16) as

begin

declare @curStr varchar(16)

select @curStr = isnull(replicate(@paddingChar,@totalWidth – len(isnull(@num ,0))), ”) + @num

return @curStr

end

五.SQL 子带的LTrim()函数，只能清除空格，以下3个函数，可以随心所欲

CREATE FUNCTION dbo.Trim

(

@Source VARCHAR(MAX),

@Char CHAR(1)

)

RETURNS VARCHAR(MAX)

BEGIN

DECLARE @i int;

DECLARE @returnString VARCHAR(MAX)

SET @returnString = @Source

–清除前后空格

SET @returnString = LTRIM(RTRIM(@returnString))

–删除左侧字符

SET @i = 0

WHILE @i=0

BEGIN

IF LEFT(@returnString,1) = @Char

SET @returnString = RIGHT(@returnString,LEN(@returnString)-1)

ELSE

SET @i=1

END

–删除右侧字符

SET @i = 0

WHILE @i=0

BEGIN

IF RIGHT(@returnString,1) = @Char

SET @returnString = LEFT(@returnString,LEN(@returnString)-1)

ELSE

SET @i=1

END

–清除前后空格

SET @returnString = LTRIM(RTRIM(@returnString))

RETURN @returnString;

END

CREATE FUNCTION dbo.R_Trim

(

@Source VARCHAR(MAX),

@Char CHAR(1)

)

RETURNS VARCHAR(MAX)

BEGIN

DECLARE @i int;

DECLARE @returnString VARCHAR(MAX)

SET @returnString = @Source

–清除前后空格

SET @returnString = LTRIM(RTRIM(@returnString))

–删除右侧字符

SET @i = 0

WHILE @i=0

BEGIN

IF RIGHT(@returnString,1) = @Char

SET @returnString = LEFT(@returnString,LEN(@returnString)-1)

ELSE

SET @i=1

END

–清除前后空格

SET @returnString = LTRIM(RTRIM(@returnString))

RETURN @returnString;

END

CREATE FUNCTION dbo.L_Trim

(

@Source VARCHAR(MAX),

@Char CHAR(1)

)

RETURNS VARCHAR(MAX)

BEGIN

DECLARE @i int;

DECLARE @returnString VARCHAR(MAX)

SET @returnString = @Source

–清除前后空格

SET @returnString = LTRIM(RTRIM(@returnString))

–删除左侧字符

SET @i = 0

WHILE @i=0

BEGIN

IF LEFT(@returnString,1) = @Char

SET @returnString = RIGHT(@returnString,LEN(@returnString)-1)

ELSE

SET @i=1

END

–清除前后空格

SET @returnString = LTRIM(RTRIM(@returnString))

RETURN @returnString;

END

六，sql函数：汉字转换为拼音

sql数据库

自定义一个函数

把下面代码写进去

功能是得到汉字拼音首字母

如下：

42 create function fun_getPY(@str nvarchar(4000))

returns nvarchar(4000)

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(‘中华人民共和国’)

结果都为：ZHRMGHG

IF OBJECT_ID(‘[fn_GetPinyin]‘) IS NOT NULL

DROP FUNCTION [fn_GetPinyin]

create function [dbo].[fn_GetPinyin](@words nvarchar(2000))

returns varchar(8000)

begin

declare @word nchar(1)

declare @pinyin varchar(8000)

declare @i int

declare @words_len int

declare @unicode int

set @i = 1

set @words = ltrim(rtrim(@words))

set @words_len = len(@words)

while (@i <= @words_len) –循环取字符

begin

set @word = substring(@words, @i, 1)

set @unicode = unicode(@word)

set @pinyin = ISNULL(@pinyin +SPACE(1),”)+

(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 ‘ai’,N’靉’

union all select ‘an’,N’黯’

union all select ‘ang’,N’醠’

union all select ‘ao’,N’驁’

union all select ‘ba’,N’欛’

union all select ‘bai’,N’瓸’ –韛兡瓸

union all select ‘ban’,N’瓣’

union all select ‘bang’,N’鎊’

union all select ‘bao’,N’鑤’

union all select ‘bei’,N’鐾’

union all select ‘ben’,N’輽’

union all select ‘beng’,N’鏰’

union all select ‘bi’,N’鼊’

union all select ‘bian’,N’變’

union all select ‘biao’,N’鰾’

union all select ‘bie’,N’彆’

union all select ‘bin’,N’鬢’

union all select ‘bing’,N’靐’

union all select ‘bo’,N’蔔’

union all select ‘bu’,N’簿’

union all select ‘ca’,N’囃’

union all select ‘cai’,N’乲’ –縩乲

union all select ‘can’,N’爘’

union all select ‘cang’,N’賶’

union all select ‘cao’,N’鼜’

union all select ‘ce’,N’簎’

union all select ‘cen’,N’笒’

union all select ‘ceng’,N’乽’ –硛硳岾猠乽

union all select ‘cha’,N’詫’

union all select ‘chai’,N’囆’

union all select ‘chan’,N’顫’

union all select ‘chang’,N’韔’

union all select ‘chao’,N’觘’

union all select ‘che’,N’爡’

union all select ‘chen’,N’讖’

union all select ‘cheng’,N’秤’

union all select ‘chi’,N’鷘’

union all select ‘chong’,N’銃’

union all select ‘chou’,N’殠’

union all select ‘chu’,N’矗’

union all select ‘chuai’,N’踹’

union all select ‘chuan’,N’鶨’

union all select ‘chuang’,N’愴’

union all select ‘chui’,N’顀’

union all select ‘chun’,N’蠢’

union all select ‘chuo’,N’縒’

union all select ‘ci’,N’嗭’ –賜嗭

union all select ‘cong’,N’謥’

union all select ‘cou’,N’輳’

union all select ‘cu’,N’顣’

union all select ‘cuan’,N’爨’

union all select ‘cui’,N’臎’

union all select ‘cun’,N’籿’

union all select ‘cuo’,N’錯’

union all select ‘da’,N’橽’

union all select ‘dai’,N’靆’

union all select ‘dan’,N’饏’

union all select ‘dang’,N’闣’

union all select ‘dao’,N’纛’

union all select ‘de’,N’的’

union all select ‘den’,N’扽’

union all select ‘deng’,N’鐙’

union all select ‘di’,N’螮’

union all select ‘dia’,N’嗲’

union all select ‘dian’,N’驔’

union all select ‘diao’,N’鑃’

union all select ‘die’,N’嚸’ –眰嚸

union all select ‘ding’,N’顁’

union all select ‘diu’,N’銩’

union all select ‘dong’,N’霘’

union all select ‘dou’,N’鬭’

union all select ‘du’,N’蠹’

union all select ‘duan’,N’叾’ –籪叾

union all select ‘dui’,N’譵’

union all select ‘dun’,N’踲’

union all select ‘duo’,N’鵽’

union all select ‘e’,N’鱷’

union all select ‘en’,N’摁’

union all select ‘eng’,N’鞥’

union all select ‘er’,N’樲’

union all select ‘fa’,N’髮’

union all select ‘fan’,N’瀪’

union all select ‘fang’,N’放’

union all select ‘fei’,N’靅’

union all select ‘fen’,N’鱝’

union all select ‘feng’,N’覅’

union all select ‘fo’,N’梻’

union all select ‘fou’,N’鴀’

union all select ‘fu’,N’猤’ –鰒猤

union all select ‘ga’,N’魀’

union all select ‘gai’,N’瓂’

union all select ‘gan’,N’灨’

union all select ‘gang’,N’戇’

union all select ‘gao’,N’鋯’

union all select ‘ge’,N’獦’

union all select ‘gei’,N’給’

union all select ‘gen’,N’搄’

union all select ‘geng’,N’堩’ –亙堩啹喼嗰

union all select ‘gong’,N’兣’ –熕贑兝兣

union all select ‘gou’,N’購’

union all select ‘gu’,N’顧’

union all select ‘gua’,N’詿’

union all select ‘guai’,N’恠’

union all select ‘guan’,N’鱹’

union all select ‘guang’,N’撗’

union all select ‘gui’,N’鱥’

union all select ‘gun’,N’謴’

union all select ‘guo’,N’腂’

union all select ‘ha’,N’哈’

union all select ‘hai’,N’饚’

union all select ‘han’,N’鶾’

union all select ‘hang’,N’沆’

union all select ‘hao’,N’兞’

union all select ‘he’,N’靏’

union all select ‘hei’,N’嬒’

union all select ‘hen’,N’恨’

union all select ‘heng’,N’堼’ –堼囍

union all select ‘hong’,N’鬨’

union all select ‘hou’,N’鱟’

union all select ‘hu’,N’鸌’

union all select ‘hua’,N’蘳’

union all select ‘huai’,N’蘾’

union all select ‘huan’,N’鰀’

union all select ‘huang’,N’鎤’

union all select ‘hui’,N’顪’

union all select ‘hun’,N’諢’

union all select ‘huo’,N’夻’

union all select ‘ji’,N’驥’

union all select ‘jia’,N’嗧’

union all select ‘jian’,N’鑳’

union all select ‘jiang’,N’謽’

union all select ‘jiao’,N’釂’

union all select ‘jie’,N’繲’

union all select ‘jin’,N’齽’

union all select ‘jing’,N’竸’

union all select ‘jiong’,N’蘔’

union all select ‘jiu’,N’欍’

union all select ‘ju’,N’爠’

union all select ‘juan’,N’羂’

union all select ‘jue’,N’钁’

union all select ‘jun’,N’攈’

union all select ‘ka’,N’鉲’

union all select ‘kai’,N’乫’ –鎎乫

union all select ‘kan’,N’矙’

union all select ‘kang’,N’閌’

union all select ‘kao’,N’鯌’

union all select ‘ke’,N’騍’

union all select ‘ken’,N’褃’

union all select ‘keng’,N’鏗’ –巪乬唟厼怾

union all select ‘kong’,N’廤’

union all select ‘kou’,N’鷇’

union all select ‘ku’,N’嚳’

union all select ‘kua’,N’骻’

union all select ‘kuai’,N’鱠’

union all select ‘kuan’,N’窾’

union all select ‘kuang’,N’鑛’

union all select ‘kui’,N’鑎’

union all select ‘kun’,N’睏’

union all select ‘kuo’,N’穒’

union all select ‘la’,N’鞡’

union all select ‘lai’,N’籟’

union all select ‘lan’,N’糷’

union all select ‘lang’,N’唥’

union all select ‘lao’,N’軂’

union all select ‘le’,N’餎’

union all select ‘lei’,N’脷’ –嘞脷

union all select ‘leng’,N’睖’

union all select ‘li’,N’瓈’

union all select ‘lia’,N’倆’

union all select ‘lian’,N’纞’

union all select ‘liang’,N’鍄’

union all select ‘liao’,N’瞭’

union all select ‘lie’,N’鱲’

union all select ‘lin’,N’轥’ –轥拎

union all select ‘ling’,N’炩’

union all select ‘liu’,N’咯’ –瓼甅囖咯

union all select ‘long’,N’贚’

union all select ‘lou’,N’鏤’

union all select ‘lu’,N’氇’

union all select ‘lv’,N’鑢’

union all select ‘luan’,N’亂’

union all select ‘lue’,N’擽’

union all select ‘lun’,N’論’

union all select ‘luo’,N’鱳’

union all select ‘ma’,N’嘛’

union all select ‘mai’,N’霢’

union all select ‘man’,N’蘰’

union all select ‘mang’,N’蠎’

union all select ‘mao’,N’唜’

union all select ‘me’,N’癦’ –癦呅

union all select ‘mei’,N’嚜’

union all select ‘men’,N’們’

union all select ‘meng’,N’霥’ –霿踎

union all select ‘mi’,N’羃’

union all select ‘mian’,N’麵’

union all select ‘miao’,N’廟’

union all select ‘mie’,N’鱴’ –鱴瓱

union all select ‘min’,N’鰵’

union all select ‘ming’,N’詺’

union all select ‘miu’,N’謬’

union all select ‘mo’,N’耱’ –耱乮

union all select ‘mou’,N’麰’ –麰蟱

union all select ‘mu’,N’旀’

union all select ‘na’,N’魶’

union all select ‘nai’,N’錼’

union all select ‘nan’,N’婻’

union all select ‘nang’,N’齉’

union all select ‘nao’,N’臑’

union all select ‘ne’,N’呢’

union all select ‘nei’,N’焾’ –嫩焾

union all select ‘nen’,N’嫩’

union all select ‘neng’,N’能’ –莻嗯鈪銰啱

union all select ‘ni’,N’嬺’

union all select ‘nian’,N’艌’

union all select ‘niang’,N’釀’

union all select ‘niao’,N’脲’

union all select ‘nie’,N’钀’

union all select ‘nin’,N’拰’

union all select ‘ning’,N’濘’

union all select ‘niu’,N’靵’

union all select ‘nong’,N’齈’

union all select ‘nou’,N’譳’

union all select ‘nu’,N’搙’

union all select ‘nv’,N’衄’

union all select ‘nue’,N’瘧’

union all select ‘nuan’,N’燶’ –硸黁燶郍

union all select ‘nuo’,N’桛’

union all select ‘o’,N’鞰’ –毮夞乯鞰

union all select ‘ou’,N’漚’

union all select ‘pa’,N’袙’

union all select ‘pai’,N’磗’ –鎃磗

union all select ‘pan’,N’鑻’

union all select ‘pang’,N’胖’

union all select ‘pao’,N’礮’

union all select ‘pei’,N’轡’

union all select ‘pen’,N’喯’

union all select ‘peng’,N’喸’ –浌巼闏乶喸

union all select ‘pi’,N’鸊’

union all select ‘pian’,N’騙’

union all select ‘piao’,N’慓’

union all select ‘pie’,N’嫳’

union all select ‘pin’,N’聘’

union all select ‘ping’,N’蘋’

union all select ‘po’,N’魄’

union all select ‘pou’,N’哛’ –兺哛

union all select ‘pu’,N’曝’

union all select ‘qi’,N’蟿’

union all select ‘qia’,N’髂’

union all select ‘qian’,N’縴’

union all select ‘qiang’,N’瓩’ –羻兛瓩

union all select ‘qiao’,N’躈’

union all select ‘qie’,N’籡’

union all select ‘qin’,N’藽’

union all select ‘qing’,N’櫦’

union all select ‘qiong’,N’瓗’

union all select ‘qiu’,N’糗’

union all select ‘qu’,N’覻’

union all select ‘quan’,N’勸’

union all select ‘que’,N’礭’

union all select ‘qun’,N’囕’

union all select ‘ran’,N’橪’

union all select ‘rang’,N’讓’

union all select ‘rao’,N’繞’

union all select ‘re’,N’熱’

union all select ‘ren’,N’餁’

union all select ‘reng’,N’陾’

union all select ‘ri’,N’馹’

union all select ‘rong’,N’穃’

union all select ‘rou’,N’嶿’

union all select ‘ru’,N’擩’

union all select ‘ruan’,N’礝’

union all select ‘rui’,N’壡’

union all select ‘run’,N’橍’ –橍挼

union all select ‘ruo’,N’鶸’

union all select ‘sa’,N’栍’ –櫒栍

union all select ‘sai’,N’虄’ –簺虄

union all select ‘san’,N’閐’

union all select ‘sang’,N’喪’

union all select ‘sao’,N’髞’

union all select ‘se’,N’飋’ –裇聓

union all select ‘sen’,N’篸’

union all select ‘seng’,N’縇’ –閪縇

union all select ‘sha’,N’霎’

union all select ‘shai’,N’曬’

union all select ‘shan’,N’鱔’

union all select ‘shang’,N’緔’

union all select ‘shao’,N’潲’

union all select ‘she’,N’欇’

union all select ‘shen’,N’瘮’

union all select ‘sheng’,N’賸’

union all select ‘shi’,N’瓧’ –鰘齛兙瓧

union all select ‘shou’,N’鏉’

union all select ‘shu’,N’虪’

union all select ‘shua’,N’誜’

union all select ‘shuai’,N’卛’

union all select ‘shuan’,N’腨’

union all select ‘shuang’,N’灀’

union all select ‘shui’,N’睡’

union all select ‘shun’,N’鬊’

union all select ‘shuo’,N’鑠’

union all select ‘si’,N’乺’ –瀃螦乺

union all select ‘song’,N’鎹’

union all select ‘sou’,N’瘶’

union all select ‘su’,N’鷫’

union all select ‘suan’,N’算’

union all select ‘sui’,N’鐩’

union all select ‘sun’,N’潠’

union all select ‘suo’,N’蜶’

union all select ‘ta’,N’襨’ –躢襨

union all select ‘tai’,N’燤’

union all select ‘tan’,N’賧’

union all select ‘tang’,N’燙’

union all select ‘tao’,N’畓’ –討畓

union all select ‘te’,N’蟘’

union all select ‘teng’,N’朰’ –霯唞朰

union all select ‘ti’,N’趯’

union all select ‘tian’,N’舚’

union all select ‘tiao’,N’糶’

union all select ‘tie’,N’餮’

union all select ‘ting’,N’乭’ –濎乭

union all select ‘tong’,N’憅’

union all select ‘tou’,N’透’

union all select ‘tu’,N’鵵’

union all select ‘tuan’,N’褖’

union all select ‘tui’,N’駾’

union all select ‘tun’,N’坉’

union all select ‘tuo’,N’籜’

union all select ‘wa’,N’韤’

union all select ‘wai’,N’顡’

union all select ‘wan’,N’贎’

union all select ‘wang’,N’朢’

union all select ‘wei’,N’躛’

union all select ‘wen’,N’璺’

union all select ‘weng’,N’齆’

union all select ‘wo’,N’齷’

union all select ‘wu’,N’鶩’

union all select ‘xi’,N’衋’

union all select ‘xia’,N’鏬’

union all select ‘xian’,N’鼸’

union all select ‘xiang’,N’鱌’

union all select ‘xiao’,N’斆’

union all select ‘xie’,N’躞’

union all select ‘xin’,N’釁’

union all select ‘xing’,N’臖’

union all select ‘xiong’,N’敻’

union all select ‘xiu’,N’齅’

union all select ‘xu’,N’蓿’

union all select ‘xuan’,N’贙’

union all select ‘xue’,N’瀥’

union all select ‘xun’,N’鑂’

union all select ‘ya’,N’齾’

union all select ‘yan’,N’灩’

union all select ‘yang’,N’樣’

union all select ‘yao’,N’鑰’

union all select ‘ye’,N’岃’ –鸈膶岃

union all select ‘yi’,N’齸’

union all select ‘yin’,N’檼’

union all select ‘ying’,N’譍’

union all select ‘yo’,N’喲’

union all select ‘yong’,N’醟’

union all select ‘you’,N’鼬’

union all select ‘yu’,N’爩’

union all select ‘yuan’,N’願’

union all select ‘yue’,N’鸙’

union all select ‘yun’,N’韻’

union all select ‘za’,N’雥’

union all select ‘zai’,N’縡’

union all select ‘zan’,N’饡’

union all select ‘zang’,N’臟’

union all select ‘zao’,N’竈’

union all select ‘ze’,N’稄’

union all select ‘zei’,N’鱡’

union all select ‘zen’,N’囎’

union all select ‘zeng’,N’贈’

union all select ‘zha’,N’醡’

union all select ‘zhai’,N’瘵’

union all select ‘zhan’,N’驏’

union all select ‘zhang’,N’瞕’

union all select ‘zhao’,N’羄’

union all select ‘zhe’,N’鷓’

union all select ‘zhen’,N’黮’

union all select ‘zheng’,N’證’

union all select ‘zhi’,N’豒’

union all select ‘zhong’,N’諥’

union all select ‘zhou’,N’驟’

union all select ‘zhu’,N’鑄’

union all select ‘zhua’,N’爪’

union all select ‘zhuai’,N’跩’

union all select ‘zhuan’,N’籑’

union all select ‘zhuang’,N’戅’

union all select ‘zhui’,N’鑆’

union all select ‘zhun’,N’稕’

union all select ‘zhuo’,N’籱’

union all select ‘zi’,N’漬’ –漬唨

union all select ‘zong’,N’縱’

union all select ‘zou’,N’媰’

union all select ‘zu’,N’謯’

union all select ‘zuan’,N’攥’

union all select ‘zui’,N’欈’

union all select ‘zun’,N’銌’

union all select ‘zuo’,N’咗’) t

where word >= @word collate Chinese_PRC_CS_AS_KS_WS

order by word collate Chinese_PRC_CS_AS_KS_WS ASC) else @word end)

set @i = @i + 1

end

return @pinyin

END

SELECT dbo.fn_GetPinyin(‘中华人民共和国’)

——————————————-

zuo zhong hua ren min gong he guo

本文链接地址: 常用sql语句汇集http://www.hongyanliren.com/2014m11/20933.html

相关文章