SQL字符串函数

 SQL字符串函数

SQL字符串截取函数：left(), right(), substring(), substring_index()。还有 mid(), substr()。其中，mid(), substr() 等价于 substring() 函数，substring() 的功能非常强大和灵活。

1. 左截取字符串：left(str, length)

 例：select u_ID as 用户名,left(u_Name,1) as 用户姓氏 from t_Users

2.右截取字符串：right(str,length)

例：select u_ID,right(u_Name,2) as 用户名 from t_Users

3.字符串截取：

substring(str,start_pos)－－从字符串指定位置起直截取到串的结尾

substring(str,start_pos,length)－－从指定位置截取指定长度的子串，当length为负数时，表示向前倒取。

4.截取函数：substring_index(str,delim,count)，具体含义是：表示字符串中截取第count指定的delim符号前的子串，例：

select substring_index('www.sina.com.cn','.',2)   其结果应是：www.sina，表示截取第二个“.”以前的所有字符串。

 

以下内容来自 http://book.51cto.com/art/200908/145902.htm

 

6.7.2  CHARINDEX()和PATINDEX()函数

CHARINDEX()是原始的SQL函数，用于寻找在一个字符串中某子字符串第一次出现的位置。如函数名所示，这个函数返回一个整型值，表示某子字符串的第一个字符在整个字符串中的位置索引。以下脚本用于在字符串Washington中寻找子字符串sh的出现位置:

   
   
SELECT CHARINDEX('sh', 'Washington') 

返回的结果是3，表明s是字符串Washington中的第3个字符。在这个例子中使用两个字符作为子字符串并没有特别意义，但是如果字符串包含多个s字符，就有意义了。

PATINDEX()函数和CHARINDEXO函数类似，它执行相同的操作，但方法稍许不同，该函数增加了对通配符(即Like运算符中使用的字符)的支持。顾名思义，它将返回一个字符模式的索引。这个函数也可以和ntext、nchar(max)和nvarchar(max)等大字符类型一起使用。注意，如果和这些大字符类型一起使用，PATINDEX()函数将返回bigint类型的值，而不是int类型的值。以下是一个例子：

   
   
SELECT PATINDEX('%M_rs%', 'The stars 
near Mars are far from ours') 

注意，如果想找到一个字符串，在所比较的字符串的前后各有0个或者多个字符，则两个百分符都是必须的。下划线表明这个位置上的字符不必匹配，它可以是任意字符。

和使用相同字符串的CHARINDEX()函数作一下比较：

   
   
SELECT CHARINDEX('Mars', ' The stars near 
Mars are far from ours')  

这两个函数都返回索引值16。请注意这些函数的执行过程。下一节将把这两个函数和SUBSTRING()函数组合在一起，演示如何使用界定符解析字符串。

 

6.7.3  LEN()函数

LEN()函数用于返回一个代表字符串长度的整型值。这是一个简单、有用的函数，经常与其他函数一起使用，来应用业务规则。以下例子将月份和日期转换为字符类型，然后测试它们的长度。如果月份日期只有一个字符，就填充字符0，然后组合成一个8字符的美国格式的日期字符串(MMDDYYYY)。

   
   
DECLARE @MonthChar varchar(2), @DayChar varchar(2), @DateOut char(8)  
SET @MonthChar = CAST(MONTH(GETDATE()) AS varchar(2))  
SET @DayChar = CAST(DAY(GETDATE()) AS varchar(2))  
-- Make sure month and day are two char long:  
IF LEN(@MonthChar) = 1  
  SET @MonthChar = '0' + @MonthChar  
IF LEN(@DayChar) = 1  
  SET @DayChar = '0' + @DayChar  
-- Build date string:  
SET @DateOut = @MonthChar + @DayChar + CAST(YEAR(GETDATE()) AS char(4))  
SELECT @DateOut AS OutputDate 

这个脚本将返回代表日期的8个字符:

   
   
07012008 

 

6.7.4  LEFT()与RIGHT()函数

LEFT()与RIGHT()函数是相似的，它们都返回一定长度的子字符串。这两个函数的区别是，它们返回的分别是字符串的不同部分。LEFT()函数返回字符串最左边的字符，顺序从左数到右。RIGHT()函数正好相反，它从最右边的字符开始，以从右到左的顺序返回特定数量的字符。看一看使用这两个函数返回"GeorgeWashington"这个字符串的子字符串的例子。

如果使用LEFT()函数返回一个5字符的子字符串，则函数先定位最左边的字符，向右数5个字符，然后返回这个子字符串，如下所示。

   
   
DECLARE @FullName varchar(25)  
SET @FullName = 'George Washington' 
SELECT LEFT(@FullName, 5) 

如果使用RIGHT()函数返回一个5字符的子字符串，则函数先定位最右边的字符，向左数5个字符，然后返回这个子字符串，如下所示。

   
   
DECLARE @FullName varchar(25)  
SET @FullName = 'George Washington' 
SELECT RIGHT (@FullName, 5) 

要想返回字符串中有意义的部分，这两个函数都不是特别有用。如果想返回全名中的姓氏或者名字，该怎么办？这需要多做一点工作。如果能确定每个姓名中空格的位置，就可以使用LEFT()函数在全名中读取名字。在这种情况下，可以使用CHARINDEX()或者PATINDEX()函数来定位空格，然后使用LEFT()函数返回空格前的字符。下面是第一个用过程方法编写的例子，它将处理过程分解成以下步骤：

   
   
DECLARE @FullName varchar(25), @SpaceIndex tinyint  
SET @FullName = 'George Washington' 
-- Get index of the delimiting space:  
SET @SpaceIndex = CHARINDEX(' ' , @FullName)  
-- Return all characters to the left of the space:  
SELECT LEFT(@FullName, @SpaceIndex -  1) 

如果不想在结果中包含空格，就需要从@SpaceIndex值中减去1，这样结果中就只有名字了。

 

6.7.5  SUBSTRING()函数

SUBSTRING()函数能够从字符串的一个位置开始，往右数若干字符，返回一个特定长度的子字符串。和LEFT()函数不同之处是，该函数可以指定从哪个位置开始计数，这样就可以在字符串的任何位置摘取子字符串了。这个函数需要三个参数：要解析的字符串、起始位置索引、要返回的子字符串长度。如果要返回到所输入字符串尾部的所有字符，可以使用比所需长度更大的长度值。SUBSTRING()函数将返回最大可能长度的字符数，而不会将多出的长度以空格填充。

只要指定字符串最左边的字符(1)为起始索引，就可以用SUBSTRING()函数替代LEFT()函数。

继续上一节的例子。可以设置起始位置与长度，返回姓名字符串中间的值。在这个例子中，从位置4开始，返回一个6字符的子字符串"rge Wa"。

   
   
DECLARE @FullName varchar(25)  
SET @FullName = 'George Washington' 
SELECT SUBSTRING(@FullName, 4, 6) 

现在将上述各函数组合在一起，即可从名字+空格+姓氏格式的全名字符串中解析出名字和姓氏。使用先前的逻辑，通过函数嵌套来减少脚本的行数，并去掉@SpaceIndex变量。下面用SUBSTRING()函数替代LEFT()函数：

   
   
DECLARE @FullName varchar(25)  
SET @FullName = 'George Washington' 
-- Return first name:  
SELECT SUBSTRING(@FullName, 1, CHARINDEX(' ', @FullName) - 1) 

类似的逻辑可以用于解析姓氏，但是必须将起始位置更改为空格后的那个字符。如果空格在第7个位置上，那么姓氏将从第8个位置开始。这就意味着起始位置是CHARINDEX()的返回结果加上1。

   
   
DECLARE @FullName varchar(25)  
SET @FullName = 'George Washington' 
--Return last name:  
SELECT SUBSTRING(@FullName, CHARINDEX(' ', 
@FullName) + 1, LEN(@FullName)) 

把上述步骤组合在一起，就可以运行下面的查询，从全名变量中提取出名字和姓氏：

   
   
DECLARE @FullName varchar(25)  
SET @FullName = 'George Washington' 
-- Return first name:  
SELECT SUBSTRING(@FullName, 1, CHARINDEX(' ',
@FullName) - 1) AS FirstName,   
       SUBSTRING(@FullName, CHARINDEX(' ',
@FullName) + 1, LEN(@FullName))   
       AS LastName 

传递给SUBSTRING()函数的值是空格所在位置加上1，并将该值作为起始位置，这将是姓氏的第1个字母。由于不可能总是知道名字的长度，所以将LEN()函数的结果作为子字符串长度参数传递进来，当SUBSTRING()函数到达这个位置时，就到达了字符串的末尾，这样就可以将字符串中从空格后面开始的所有字符都包含进来了。

为了举例方便，先创建并填充一个临时表:

   
   
CREATE TABLE #MyNames (FullName varchar(50))  
GO  
INSERT INTO #MyNames (FullName) SELECT 'Fred Flintstone' 
INSERT INTO #MyNames (FullName) SELECT 'Wilma Flintstone' 
INSERT INTO #MyNames (FullName) SELECT 'Barney Rubble' 
INSERT INTO #MyNames (FullName) SELECT 'Betty Rubble' 
INSERT INTO #MyNames (FullName) SELECT 'George Jetson' 
INSERT INTO #MyNames (FullName) SELECT 'Jane Jetson' 

下面执行一个使用函数调用来解析名字和姓氏值的单行查询表达式。这里对@FullName变量的引用被表中的FullName列所替代:

   
   
SELECT 
    SUBSTRING(FullName, 1, CHARINDEX(' ' , 
FullName) - 1) AS FirstName  
  , SUBSTRING(FullName, CHARINDEX(' ' , 
FullName) + 1, LEN(FullName)) AS LastName  
FROM #MyNames 

在图6-7所示的结果中，显示了两个不同的列，分别是名字和姓氏。

图  6-7