sql字符函数练习题

sql字符函数练习题

一1.设数据库中有学生成绩表sc,enochar,grade

tinyint),设id个函数Count_course,返回指定学生的选

修的课程门数.注意：没有选修时应返回0；create

functionCountcourse)returnsintasbegin

returnfromscwheresno=@sno)end

--2.写一个T-sql函数reversion,完成颠倒一个字

符串，即：selectdbo.reversion输出的结果为deba；

createfunctionreversion)returnsvarcharasbegin

declare@retvarchardeclare@iint

set@ret=''

set@i=lenwhile@i>0begin

set@ret=@ret+substringset-lend

return@retend

-selectdbo.reversion

一对照：下面的算法有错误，当字符串长度超过2000

后不能得到正确结果createalterfunctionreversion)

returnsvarcharasbegin

declare@iint,@jintset@i=lenset@j=lenwhile

@i>0begin

set@str=@str+substringset@i=@i-lend

set@str=right

1

return@str

end

-selectdbo.reversion2

一3.写一个函数T-sql函数elimination将出现在第

一个字符串中的第二个字符串中的所有字符删除，即：

selectdbo.elimination输出为：acefgl345；

createfunctionelimination,@bvarchar)

returnsvarchar

asbegin

-declare@retvarchardeclare@iint-set

@ret=''

set@i=l

while@i@a=replace,'')set@i=@i+1

end

return@aend

一selectdbo.elimination-4.写一个函数

Output_RMB,完成将人民币数值转换为汉字大写，即：select

dbo.Output_RMB输出为：肆仟伍佰陆拾柒元壹角贰分；

/*方法二：推荐使用的方法，支撑两种转换类型

selectdbo.Convertmoneyselect

dbo.Convertjnoney*/

CreatefunctionConvertmoney,@v_TransType

int)returnsvarcharASbegin

Declare@v_LowerStrVARCHAR一小写金额

Declare@v_UpperPartVARCHAR

Declare@v_UpperStrVARCHAR一大写金额

Declare@i_Iintselect@v_LowerStr=

LTRIM))一四舍五入为指定的精度并删除数据左右

select@v_UpperStr=@v_UpperPart+@v_UpperStr

select@i_I=@i_I+lend

-printV/v_UpperStr='+@v_UpperStr

+'IPif空格

select@i_I=1

select@v_UpperStr=''

while)begin

select@v_UpperPart=case

c，

substring@i__I+1,1)WHENTHEN兀

WHENTHEN，零，WHENTTHEN旦

WHENTHEN或WHEN，3'THEN，叁，

WHENTHEN，肆，WHEN5THEN，伍，

7

WHEN，6‘THEN'陆'WHENTHEN，柒，

WHEN，8'THEN'捌'WHEN'9，THEN，玖'

END

+case@iI

WHEN1THEN'分'WHENTHEN'角'WHEN

THEN''WHENTHEN''WHENTHEN'拾'WHEN

THEN'佰'WHENTHEN'仟'WHENTHEN

'万'WHENTHEN'拾'WHEN10THEN

'佰'WHEN11THEN，仟，WHEN1THEN，亿，

WHEN1THEN'拾'WHEN1THEN'佰'WHEN1THEN

'仟'WHEN1THEN'万，ELSE''END

begin

select@v_UpperStr=

REPLACEselect@v_UpperStr=

REPLACEselect@v_UpperStr=

-REPLACEselect@v_UpperStr=

REPLACEselect@v_UpperStr=

REPLACEselect@v_UpperStr=

REPLACEselect@v_UpperStr=

REPLACEselect@v_UpperStr=

REPLACEselect@v_UpperStr=

REPLACE

select@v_UpperStr=

REPLACEselect@v_UpperStr=

REPLACEselect@v_UpperStr=

REPLACEselect@v_UpperStr=

REPLACEselect@v_UpperStr=

REPLACEselect@v_UpperStr=

REPLACEselect@v_UpperStr=

REPLACEend

~对壹元以下的金额的处理

2

if='元')begin

select@v_UpperStr=

substring-1))

end

if=’零')beginselect@v_UpperStr=

substring-1))

end

if='角')beginselect@v_UpperStr=

substring-1))end

if='分')begin

select@v_UpperStr=

substring-1))

end

if=，整，)beginselect@v_UpperStr=，零元

整'end

return@v_UpperStrend

go/*

/*5.写一个函数Custom_AVG模拟多名裁判打分时求

平均值的函数，去掉一个最高分、一个最低分、然后取平均。

设数据库表结构为：

createtableplayer)createtablescoreprimary

key,playerlDint,umpirelDint,

scoreintcheck)

3

*/

createfunctionCustom_AVGreturnsdecimalas

beginreturn

-max-min)*1.0/-2)fromscorewhere

playerID=@playerID)/*或者

return-max-min)asdecimal)/-2)

fromscorewhereplayerID=@playerID)*/endgo

createviewv_gradeas

selectplayerlD,CustomAVGfromplayergo/*

测试

createtablescoreprimarykey,playerlDint,

umpirelDint,

scoreintcheck)insertintoscorevaluesinsert

intoscorevaluesinsertintoscorevaluesinsertinto

scorevaluesinsertintoscorevaluesinsertintoscore

valuesselectdbo.Custom_AVG*/

1:创建一个自定义函数，完成将指定时间显示为：

xxxx年xx月xx日xx时xx分xx秒createfunction

FormatDatereturnsvarcharbegin

returncastvarchar)+'年'

+castvarchar)+?月'

asas

int,

+castvarchar)+'日'

+castvarchar)+?时'

+castvarchar)+?分'

+castvarchar)+'秒'end

asasasas

end

closePutOut

deallocatePutOut

4:创建一个存储过程sp_addCourse,完成课程信息的

添加；

createprocedurespaddCourse,@cnamechar,

©termtinyint,©hourstinyint,©credittinyint)as

insert

into

KC

selectdbo.FormatDate)

针对给定的数据库xscj,完成如下设计：：创建一个

自定义函数，实现判别学生是否具备毕业资格，返回值为bit

类型,毕业资格条件是:总学分＞=50;

createfunctionIsGrduate)returnsbit

begin

declare@retbit

ifexistsset@ret=lelse

set@ret=0return@retend

selectdbo.IsGrduateselectdbo.IsGrduate

3:用游标完成输出具备毕业资格的学生的选课情况：

门数、平均分、最高分、最低分declarePutOutcursor

forselect

sno

from

XS_KC

where

values

spaddCourse'23',English',4,32,3

5:创建一个存储过程sp_deleteStudent,删除指定学

号的学生；

createproceduresp_deleteStudent)

as

deletefromXSwheresno=@snospdeleteStudent

^00124r

6:创建一个存储过程，完成学生成绩的保存，

要求提供三个参数@sno,@cno,©grade；create

procedurespstoreGrade

,@cnochar,@gradetinyint)as

insertintoXSKC

select@sno,@cno,@grade,creditfromKCwhere

cno=@cno

spstoreGrade'001241'101',87

7:创建一个触发器完成课程的学时数只增不减；

createtriggertr_limitUpdateonKC

afterupdateasbegin

ifexists=lopenPutOut

declare@snochar

fetchnextfromPutOutinto@snowhile

@@FETCH_STATUS=O

begin

selectsno学号，count门数，avg平均分，max最高

分,min最低分

fromXSKCwheresno=@snogroupbysno

fetchnextfromPutOutinto@sno

inserted.hoursrollbacktransactionend

8:创建一个触发器，当输入学生的成绩或成绩发生改

变后，

将该学生所得的总学分实时修改；createtrigger

tr_autoCallTotalCreditonxs_kcafterinsert,updateas

begin

ifupdatebegin

declare@snochar,@totalCreditintselect

@sno=snofrominserted

select@totalCredit=sumfromxs_kcwhere

sno=@sno

updatexssettotalCredit=@totalCreditwhere

sno=@snoend

end

9:通过触发器，完成一个xs表回收站的功能：当删

除xs表的数据后，

将删除后的数据保存到recycle_xs表中。

createtriggertr_recycleonxsafterdeleteasbegin

ifexistsinsertintoxs_recycle

selectgetdatedelTimefromdeletedelse

select*,getdatexs_recyclefromdeleted

delTime

into

returnsint

asbegin

declare@nint,©Iintset@n=0set@1=1

while@1substring)=@str2set@n=@n+lset

@I=@I+lend

return@n

end

selectdbo.countstring

end

10：设计一个函数，计算一个字符串在另一个字符串

中出现的次数。

createfunctioncountString

,@strvarchar)

5

SQLServer字符串处理函数大全

select语句中只能使用sql函数对字段进行操作，

select字段1from表1where字段1.Index0f=l;

这条语句不对的原因是indexof函数不是sql函数,

改成sql对应的函数就可以了。left是sql函数。

select字段1from表1wherecharindex=l;字符

串函数对二进制数据、字符串和表达式执行不同的运算。此

类函数作用于CHAR、VARCHAR、BINARY、和VARBINARY数

据类型以及可以隐式转换为CHAR或VARCHAR的数据类型。

可以在SELECT语句的SELECT和WHERE子句以及表达式中

使用字符串函数。

常用的字符串函数有：

一、字符转换函数

1、ASCII

返回字符表达式最左端字符的ASCH码值。在ASCH

函数中，纯数字的字符串可不用''括起来，但含其它字符

的字符串必须用''括起来使用，否则会出错。

2、CHAR

将ASCH码转换为字符。如果没有输入0~5之间的

ASCII码值，CHAR返回NULL。

3、LOWER和UPPER

LOWER将字符串全部转为小写;UPPER将字符串全部转

为大写。

4、STR

把数值型数据转换为字符型数据。

STR

length指定返回的字符串的长度，decimal指定返

回的小数位数。如果没有指定长度，缺省的length值为10,

decimal缺省值为0。

当length或者decimal为负值时，返回NULL;

当length小于小数点左边的位数时，返回length个

*；

先服从length,再取decimal;

当返回的字符串位数小于length,左边补足空格。

二、去空格函数

1、LTRIM把字符串头部的空格去掉。

2、RTRIM把字符串尾部的空格去掉。

三、取子串函数

1、left

LEFT

返回characterexpression左起

integer_expression个字符。

2、RIGHT

RIGHT

返回character_expression右起

integer_expression个字符。

3、SUBSTRING

SUBSTRING

返回从字符串左边第starting,position个字符起

length个字符的部分。

四、字符串比较函数

1、CHARINDEX

返回字符串中某个指定的子串出现的开始位置。

CHARINDEX

其中substring_expression是所要查找的字符表达

式，expression可为字符串也可为列名表达式。如果没有

发现子串，则返回0值。

此函数不能用于TEXT和IMAGE数据类型。

2、PATINDEX

返回字符串中某个指定的子串出现的开始位置。

PATINDEX其中子串表达式前后必须有百分号“％”

否则返回值为Oo

与CHARINDEX函数不同的是，PATINDEX函数的子串

中可以使用通配符，且此函数可用于CHAR、VARCHAR和TEXT

数据类型。

五、字符串操作函数

1、QUOTENAME

返回被特定字符括起来的字符串。

QUOTENAME其中quote_character标明括字符串所

用的字符，缺省值为“口”。

2、REPLICATE

返回一个重复characterexpression指定次数

的字符串。

REPLICATE如果integer_expression值为负值，则

返回NULLo

3、REVERSE

将指定的字符串的字符排列顺序颠倒。

REVERSE其中characterexpression可以是字符

串、常数或一个列的值。

4、REPLACE

返回被替换了指定子串的字符串。

REPLACE用string_expression替换在

stringexpressionl中的子串string_expression2o

4、SPACE

返回一个有指定长度的空白字符串。

SPACE如果integer_expression值为负值，则返回

NULLo

5、STUFF

用另一子串替换字符串指定位置、长度的子串。

STUFF

如果起始位置为负或长度值为负，或者起始位置大于

character_expressionl的长度，则返回NULL值。

如果length长度大于character_expressionl中

start_position以右的长度，贝I]characterexpressionl

只保留首字符。

六、数据类型转换函数

1、CAST

CAST

2、CONVERT

CONVERT

1)data_type为SQLServer系统定义的数据类型，

用户自定义的数据类型不能在此使用。

2)length用于指定数据的长度，缺省值为30。

3)把CHAR或VARCHAR类型转换为诸如INT或SAMLLINT

这样的INTEGER类型、结果必须是带正号或负号的数值。

4)TEXT类型到CHAR或VARCHAR类型转换最多为8000

个字符，即CHAR或VARCHAR数据类型是最大长度。

5)IMAGE类型存储的数据转换到BINARY或VARBINARY

类型,最多为8000个字符。

6)把整数值转换为MONEY或SMALLMONEY类型，按定

义的国家的货币单位来处理，如人民币、美元、英镑等。

7)BIT类型的转换把非零值转换为1,并仍以BIT类

型存储。

8）试图转换到不同长度的数据类型，会截短转换值

并在转换值后显示“+”，以标识发生了这种截断。

9）用CONVERT函数的style选项能以不同的格式显

示日期和时间。style是将DATATIME和SMALLDATETIME数

据转换为字符串时所选用的由SQLServer系统提供的转换

样式编号，不同的样式编号有不同的输出格式。

七、日期函数

1、day

返回date_expression中的日期值

2、month

返回date_expression中的月份值

3、year

返回date_expression中的年份值

4、DATEADD

DATEADD

返回指定日期date加上指定的额外日期间隔number

产生的新日期。

5、DATEDIFF

DATEDIFF

返回两个指定日期在datepart方面的不同之处，即

date超过datel的差距值，其结果值是一个带有正负号的整

数值。

6、DATENAME

DATENAME

以字符串的形式返回日期的指定部分此部分。山

datepart来指定。

7、DATEPART

DATEPART

以整数值的形式返回日期的指定部分。此部分由

datepart来指定。

DATEPART等同于DAY

DATEPART等同于MONTH

DATEPART等同于YEAR

8、GETDATE

以DATETIME的缺省格式返回系统当前的日期和时

间。

一聚合函数

usepubs

go

selectavg一算平均数

fromtitles

wheretype='business'

go

usepubs

go

selectmax--最大数

fromtitles

go

usepubs

go

selectmin--最小数

fromtitles

go

usepubs

go

selecttype,sum,sum―求和

fromtitles

groupbytype

orderbytype

go

usepubs

go

selectcount--求个数

fromauthors

go

usepubs

go

selectstdev--返回给定表达式中所有值的统计标

准偏差

fromtitles

go

usepubs

go

selectstdevp--返回表达式中所有制的填充统计标

准偏差

fromtitles

go

usepubs

go

selectvar一返回所有值的统计方差

fromtitles

go

usepubs

go

selectvarp--返回所有值的填充的统计方差

fromtitles

go

一数学函数

selectsin,atan,rand,PI,sign-其中rand是获

得一个随机数

一配置函数

SELECT@@VERSION一获取当前数据库版本

SELECT©©LANGUAGE一当前语言

一时间函数

selectgetdateas'wawa_getdate'-当前时间

selectgetutcdateas'wawa_getutcdate'一获

取utc时间

selectday)as'wawa_day'-取出天

selectmonth)as'wawa_month'一取出月

selectyear)as'wawa_year'一取出年

selectdateadd)aswawa_dateadd一力口三天，注

意'广表示天，'m'表示月，'yy'表示年，下面一样

selectdatediffaswawa_datediff--计算两个时

间的差

selectdatenameaswawadatename一取出时间的

某一部分

selectdatepart)aswawadatepart一取出时间

的某一部分,和上面的那个差不多

SQLServer字符串处理函数大全

select语句中只能使用sql函数对字段进行操作，

select字段1from表1where字段1.IndexOf=l;

这条语句不对的原因是indexof函数不是sql函数，

改成sql对应的函数就可以了。left是sql函数。

select字段1from表1wherecharindex=l;字符

串函数对二进制数据、字符串和表达式执行不同的运算。此

类函数作用于CHAR、VARCHAR、BINARY、和VARBINARY数

据类型以及可以隐式转换为CHAR或VARCHAR的数据类型。

可以在SELECT语句的SELECT和WHERE子句以及表达式中

使用字符串函数。

常用的字符串函数有：

一、字符转换函数

1、ASCII

返回字符表达式最左端字符的ASCH码值。在ASCH

函数中，纯数字的字符串可不用''括起来，但含其它字符

的字符串必须用''括起来使用，否则会出错。

2、CHAR

将-ASCH码转换为字符。如果没有输入0~5之间的

ASCII码值，CHAR返回NULL。

3、LOWER和UPPER

LOWER将字符串全部转为小写;UPPER将字符串全部转

为大写。

4、STR

把数值型数据转换为字符型数据。

STR

length指定返回的字符串的长度,decimal指定返

回的小数位数。如果没有指定长度，缺省的length值为10,

decimal缺省值为0。

当length或者decimal为负值时，返回NULL;

当length小于小数点左边的位数时，返回length个

*；先服从length,再取decimal;

当返回的字符串位数小于length,左边补足空格。

二、去空格函数

1、LTRIM把字符串头部的空格去掉。

2、RTRIM把字符串尾部的空格去掉。

三、取子串函数

1、left

LEFT

返回characterexpression左起

integer_expression个字符。

2、RIGHT

RIGHT

返回character_expression右起

integer_expression个字符。

3、SUBSTRING

SUBSTRING

返回从字符串左边第starting,position个字符起

length个字符的部分。

四、字符串比较函数

1、CHARINDEX

返回字符串中某个指定的子串出现的开始位置。

CHARINDEX

其中substring_expression是所要查找的字符表达

式，expression可为字符串也可为列名表达式。如果没有

发现子串，则返回0值。

此函数不能用于TEXT和IMAGE数据类型。

2、PATINDEX

返回字符串中某个指定的子串出现的开始位置。

PATINDEX其中子串表达式前后必须有百分号“％”

否则返回值为Oo

与CHARINDEX函数不同的是，PATINDEX函数的子串

中可以使用通配符，且此函数可用于CHAR、VARCHAR和TEXT

数据类型。

五、字符串操作函数

1、QUOTENAME

返回被特定字符括起来的字符串。

QUOTENAME其中quote_character标明括字符串所

用的字符，缺省值为“口”。

2、REPLICATE

返回一个重复character_expression指定次数的字

符串。

REPLICATE如果integer_expression值为负值，则

返回NULLo

3、REVERSE

将指定的字符串的字符排列顺序颠倒。

REVERSE其中characterexpression可以是字符

串、常数或一个列的值。

4、REPLACE

返回被替换了指定子串的字符串。

REPLACE用string_expression替换在

stringexpressionl中的子串string_expression2o

4、SPACE

返回一个有指定长度的空白字符串。

SPACE如果integer_expression值为负值，则返回

NULLo

5、STUFF

用另一子串替换字符串指定位置、长度的子串。

STUFF如果起始位置为负或长度值为负，或者起始

位置大于character_expressionl的长度，则返回NULL值。

如果length长度大于characterexpressionl中

start_position以右的长度，贝characterexpressionl

只保留首字符。

六、数据类型转换函数

1、CAST

CAST

2、CONVERT

CONVERT

1)data_type为SQLServer系统定义的数据类型，

用户自定义的数据类型不能在此使用。

2)length用于指定数据的长度，缺省值为30。

3)把CHAR或VARCHAR类型转换为诸如INT或SAMLLINT

这样的INTEGER类型、结果必须是带正号或负号的数值。

4)TEXT类型到CHAR或VARCHAR类型转换最多为8000

个字符，即CHAR或VARCHAR数据类型是最大长度。

5)IMAGE类型存储的数据转换到BINARY或VARBINARY

类型,最多为8000个字符。

6)把整数值转换为MONEY或SMALLMONEY类型，按定

义的国家的货币单位来处理，如人民币、美元、英镑等。

7)BIT类型的转换把非零值转换为1,并仍以BIT类

型存储。

8)试图转换到不同长度的数据类型，会截短转换值

并在转换值后显示“+”，以标识发生了这种截断。

9）用CONVERT函数的style选项能以不同的格式显

示日期和时间。style是将DATATIME和SMALLDATETIME数

据转换为字符串时所选用的由SQLServer系统提供的转换

样式编号，不同的样式编号有不同的输出格式。

七、日期函数