(3.2)常用知识-字符串处理
一、字符转换函数
1、ASCII()
返回字符表达式最左端字符的ASCII 码值。在ASCII()函数中,纯数字的字符串可不用‘’括起来,但含其它字符的字符串必须用‘’括起来使用,否则会出错。
2、CHAR()
将ASCII 码转换为字符。如果没有输入0 ~ 255 之间的ASCII 码值,CHAR() 返回NULL 。
3、LOWER()和UPPER()
LOWER()将字符串全部转为小写;
UPPER()将字符串全部转为大写。
4、STR()
把数值型数据转换为字符型数据。
STR (<float_expression>[,length[, <decimal>]])
length 指定返回的字符串的长度,decimal 指定返回的小数位数。如果没有指定长度,缺省的length 值为10, decimal 缺省值为0。
当length 或者decimal 为负值时,返回NULL;
当length 小于小数点左边(包括符号位)的位数时,返回length 个*;
先服从length ,再取decimal ;
当返回的字符串位数小于length ,左边补足空格。
二、去空格函数
1、LTRIM() 把字符串头部的空格去掉。
2、RTRIM() 把字符串尾部的空格去掉。
三、取子串函数
1、left()
LEFT (<character_expression>, <integer_expression>)
返回character_expression 左起 integer_expression 个字符。
2、RIGHT()
RIGHT (<character_expression>, <integer_expression>)
返回character_expression 右起 integer_expression 个字符。
3、SUBSTRING()
SUBSTRING (<expression>, <starting_ position>, length)
返回从字符串左边第starting_ position 个字符起length个字符的部分。
四、字符串比较函数
1、CHARINDEX()
返回字符串中某个指定的子串出现的开始位置。
CHARINDEX (<’substring_expression’>, <expression>)
其中substring _expression 是所要查找的字符表达式,expression 可为字符串也可为列名表达式。如果没有发现子串,则返回0 值。
此函数不能用于TEXT 和IMAGE 数据类型。
2、PATINDEX()
返回字符串中某个指定的子串出现的开始位置。
PATINDEX (<’%substring _expression%’>, <column_ name>)其中子串表达式前后必须有百分号“%”否则返回值为0。
与CHARINDEX 函数不同的是,PATINDEX函数的子串中可以使用通配符,且此函数可用于CHAR、 VARCHAR 和TEXT 数据类型。
五、字符串操作函数
1、QUOTENAME()
返回被特定字符括起来的字符串。
QUOTENAME (<’character_expression’>[, quote_ character]) 其中quote_ character 标明括字符串所用的字符,缺省值为“[]”。
2、REPLICATE()
返回一个重复character_expression 指定次数的字符串。
REPLICATE (character_expression integer_expression) 如果integer_expression 值为负值,则返回NULL 。
3、REVERSE()
将指定的字符串的字符排列顺序颠倒。
REVERSE (<character_expression>) 其中character_expression 可以是字符串、常数或一个列的值。
4、REPLACE()
返回被替换了指定子串的字符串。
REPLACE (<string_expression1>, <string_expression2>, <string_expression3>) 用string_expression3 替换在string_expression1 中的子串string_expression2。
4、SPACE()
返回一个有指定长度的空白字符串。
SPACE (<integer_expression>) 如果integer_expression 值为负值,则返回NULL 。
5、STUFF()
用另一子串替换字符串指定位置、长度的子串。
STUFF (<character_expression1>, <start_ position>, <length>,<character_expression2>)
如果起始位置为负或长度值为负,或者起始位置大于character_expression1 的长度,则返回NULL 值。
如果length 长度大于character_expression1 中 start_ position 以右的长度,则character_expression1 只保留首字符。
6、parsename()
第1个参数是字符串,第2个参数是倒叙第n个','号分隔符后面的值,默认以 '.' 做分割
select parsename(replace('1:5:3',':','.'),1)
执行结果是:3
select parsename(replace('1:5:3',':','.'),2)
执行结果是:5
select parsename(replace('1:5:3',':','.'),3)
执行结果是:1
示例:
已知: 字段A='F:\photo\Winter Leaves.jpg'
要求:分段截取每段字符[字段A不能为TEXT类型,否则报错]
解决方法:
+++++++++++++++++++++++++++++++++++++++++++++++++++
---截取字符串A的第一个\左边的字符串
select left(A,charindex('/',A)-1)
输出结果:F:
++++++++++++++++++++++++++++++++++++++++++++++++++++
---截取\中间的字符串
select left(stuff(A,1,charindex('/',A),''),charindex('/',stuff(A,1,charindex('/',A),''))-1)
输出结果:photo
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
---截取最后一个\后面的字符串
select reverse(left(reverse(A),charindex('/',reverse(A))-1))
输出结果:Winter Leaves.jpg
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
---截取字符串A的首字幕
select STUFF(A,1, 1, '')
输出结果::\photo\Winter Leaves.jpg
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
六、数据类型转换函数
1、CAST()
CAST (<expression> AS <data_ type>[ length ])
2、CONVERT()
CONVERT (<data_ type>[ length ], <expression> [, style])
1)data_type为SQL Server系统定义的数据类型,用户自定义的数据类型不能在此使用。
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 数据转换为字符串时所选用的由SQL Server 系统提供的转换
样式编号,不同的样式编号有不同的输出格式。
七、日期函数
1、day(date_expression)
返回date_expression中的日期值
2、month(date_expression)
返回date_expression中的月份值
3、year(date_expression)
返回date_expression中的年份值
4、DATEADD()
DATEADD (<datepart>, <number>, <date>)
返回指定日期date 加上指定的额外日期间隔number 产生的新日期。
5、DATEDIFF()
DATEDIFF (<datepart>, <date1>, <date2>)
返回两个指定日期在datepart 方面的不同之处,即date2 超过date1的差距值,其结果值是一个带有正负号的整数值。
6、DATENAME()
DATENAME (<datepart>, <date>)
以字符串的形式返回日期的指定部分此部分。由datepart 来指定。
7、DATEPART()
DATEPART (<datepart>, <date>)
以整数值的形式返回日期的指定部分。此部分由datepart 来指定。
DATEPART (dd, date) 等同于DAY (date)
DATEPART (mm, date) 等同于MONTH (date)
DATEPART (yy, date) 等同于YEAR (date)
8、GETDATE()
以DATETIME 的缺省格式返回系统当前的日期和时间。
实践:
数据类型
字符串常量用单引号括起来,表示Unicode时前面要加一个N。
按类型分
普通字符(单字节):CHAR、VARCHAR
Unicode字符(双字节):NCHAR、NVARCHAR
按是否变长分
固定长度:CHAR、NCHAR
(对于固定长度的,定义是多长就会留出多长的空间)
可变长度:VARCHAR、NVARCHAR
(对于变长的,按字符串实际长度保存数据,外加两个额外字节保存数据的偏移值,定义长度只代表最大不超过这么长)
变长的消耗存储空间更少,所以读操作更快,但对其更新时可能要进行扩展导致数据移动,故更新效率较低。
排序规则
注意排序规则不仅印象排序,也影响比较时是否认为是同一个东西。可以在四种级别上定义排序规则:SQL Server实例、数据库、列、表达式。
实例的排序规则在安装时决定,数据库的排序规则可以在创建时用COLLATE子句指定,列的排序规则可以在定义时用COLLATE子句指定,表达式的排序规则可以用COLLATE子句修改。否则都使用实例的排序规则。
查看所有的排序规则及其描述:
1
2
|
SELECT name ,description FROM sys.fn_helpcollations(); |
表达式测试(比较时默认不区分大小写):
1
2
3
4
|
USE MyDB; SELECT firstname,myid FROM dbo.ok WHERE firstname=N 'eMmm尔美' ; |
用COLLATE子句修改表达式排序规则后:
1
2
3
4
5
6
|
USE MyDB; SELECT firstname,myid FROM dbo.ok WHERE firstname COLLATE Latin1_General_CS_AS =N 'eMmm尔美' ; |
因为区分了大小写所以得到了空表。
运算符和函数
串联字符串
1
2
3
4
|
USE MyDB; SELECT myid, firstname+N '的' +lastname AS fullname FROM dbo.ok; |
接下来新建一张表做实验:
将NULL变成空字符串
如果对这张表尝试四个列字符串合并输出:
1
2
3
|
USE MyDB; SELECT myChar+myVarChar+myNChar+myNVarChar AS sumChar FROM dbo.CharTest; |
出现NULL值的整个都是NULL了。
可以使用COALESCE函数,这个函数接受一系列输入值,返回第一个不为NULL的值:
1
2
3
|
USE MyDB; SELECT myChar+myVarChar+ COALESCE (myNChar,N '' )+myNVarChar AS sumChar FROM dbo.CharTest; |
可以看到这一类的NULL被替换成了空字符串。
另外一种不推荐的方式是,修改SQL Server的标准行为:
1
2
3
4
5
|
SET CONCAT_NULL_YIELDS_NULL OFF ; --改变处理串联的方式(执行后NULL视为空串) USE MyDB; SELECT myChar+myVarChar+myNChar+myNVarChar AS sumChar FROM dbo.CharTest; |
不建议任何修改SQL Server标准行为的行为!修改回来:
1
|
SET CONCAT_NULL_YIELDS_NULL ON ; --改回来 |
SUBSTRING和LEFT和RIGHT
1
|
SELECT SUBSTRING ( 'abcdefghi' ,2,4); --从第2个开始向后4个 |
1
2
|
SELECT LEFT ( 'abcdefghi' ,4) AS le; --从左4个 SELECT RIGHT ( 'abcdefghi' ,4) AS ri; --从右4个 |
LEN和DATALENGTH
1
2
|
SELECT LEN( '我lzh胖虎' ) AS l1; --字符数 SELECT LEN(N '我lzh胖虎' ) AS l2; --字符数 |
1
2
|
SELECT DATALENGTH( '我lzh胖虎' ) AS l1; --字节数 SELECT DATALENGTH(N '我lzh胖虎' ) AS l2; --字节数 |
可以看到Unicode里英文字母也要占2个字节。
CHARINDEX
在第二个参数中找第一个参数第一次出现的位置,第三个参数指定从哪里开始查(默认从头部查)。
1
|
SELECT CHARINDEX( 'ok' , 'e啊哇ok但也不ok吗' ); |
1
|
SELECT CHARINDEX( 'ok' , 'e啊哇ok但也不ok吗' ,5); |
PATINDEX
在第二个参数中找第一个参数指定的模式第一次出现的位置。
1
|
SELECT PATINDEX( '%ok%' , 'e啊哇ok但也不ok吗' ); |
REPLACE
将字符串中出现的所有某个字符串替换为另一个字符串。
1
|
SELECT REPLACE ( 'e啊哇ok但也不ok吗' , 'ok' , '喵喵' ); |
可用来计算某个子串出现的次数:
1
2
3
4
5
|
SELECT ( LEN( 'e啊哇ok但也不ok吗' ) -LEN( REPLACE ( 'e啊哇ok但也不ok吗' , 'ok' , '' )) )/ LEN( 'ok' ); |
REPLICATE
以指定的次数复制字符串。
1
|
SELECT REPLICATE( '喵a' ,5); |
STUFF
在第一个参数串中删除从第二个参数开始,第三个参数长度的子串,然后将第四个参数串插到这个位置。
1
|
SELECT STUFF( '123456789' ,5,3, '替换串' ); |
UPPER和LOWER
大小写转换。
1
2
|
SELECT UPPER ( 'Wo PangHu DaQian' ) AS UP; SELECT LOWER ( 'Wo PangHu DaQian' ) AS LOW; |
LIKE谓词使用的通配符
在我的Linux笔记里学了Shell是少有的具有通配符这个概念的语言,这里SQL也是一个具有通配符概念的语言。注意通配符是完全匹配,在学Linux时强调过这点!因为之前学过了,用法都是一样的,具体通配符是哪个可能不同,这里就只画个表不实验了。
通配符表
通配符 | 意义 |
---|---|
% | 任意长度的字符串 |
_ | 任意一个字符 |
[] | 匹配其内任意一个字符 |
[-] | 匹配一个范围内任意一个字符 |
[^] | 匹配不属于其内的任意一个字符 |
ESCAPE转义
和Shell不同,SQL的转义字符是自己设定的。用ESCAPE子句设定一个确保不会在数据中出现的字符作转义字符,然后就可以用它后面跟着特殊字符(如% _ [ ])让它失去特殊意义以匹配其本身了。
1
2
3
4
|
USE MyDB; SELECT * FROM dbo.ok WHERE firstname LIKE N '%!_%' ESCAPE '!' ; --用'!'作为转义符对'_'转义 |
转自:https://blog.csdn.net/feng19821209/article/details/70556268