搜索字符串和操作简单
简介
quot; workbenchquot;在SQL Server字符串处理和操纵的是一个我以前的日期和时间的同伴。而不是老调重弹随时行SQL Server联机丛书的是什么，我再次试图为你自己的实验AA起点。它的结构，因此它可以粘贴在其全部进入查询分析器，SSMS中或其他GUI和执行个别的例子（，它可作为附件的文章，压缩）。
在处理SQL Server中的字符串的主要困难是技术，而不限成员名额。经常有一些不同的方法来达到相同的最终结果。如东西或反向的字符串函数有自己的用处不大，但在与其他人一起使用时，他们变得非常有用的。其他功能有作为"遗留项目"的含义，这是很难如soundex删除等功能，由于尚有少数顽固派仍然使用
与以前的"工作台"，我的建议是下载。sql文件在SQL Server中打开它，并开始试验！
理想的情况下，你还会有联机丛书打开一个浏览器，提供补充和背景资料。
我在末尾增加了几个问题，所以您可以检查你的进步。总的来说，我希望这个工作台说明如何方便的字符串处理在SQL Server的基本思路是抓住一次。目录从表中选择字符串数据类型字符串和校对分配和截断字符串函数莱恩ASCII和UnicodeNCHARCHARPATINDEXCHARINDEX更换东西REPLICATE空间反向删除前导或尾随空格RTRIM放大器; LTRIM更改案例UPPER和LOWER模糊搜索，SOUNDEX和差异操作文本和NTEXT几个问题
这个工作台作为实践表，我们将创建一个临时表和股票的字符串数据。CREATE TABLE #Poem (line VARCHAR(255), theOrder INT IDENTITY(1,1))
INSERT INTO #poem(line)
SELECT 'I will pen me my memoirs.'
INSERT INTO #poem(line)
SELECT 'Ah, youth, youth! What euphorian days them was!'
INSERT INTO #poem(line)
SELECT 'I wasn''t much of a hand for the boudoirs,'
INSERT INTO #poem(line)
SELECT 'I was generally to be found where the food was.'
INSERT INTO #poem(line)
SELECT 'Does anybody want any flotsam?'
INSERT INTO #poem(line)
SELECT 'I''ve gotsam.'
INSERT INTO #poem(line)
SELECT 'Does anybody want any jetsam?'
INSERT INTO #poem(line)
SELECT 'I can getsam.'
INSERT INTO #poem(line)
SELECT 'I can play ''Chopsticks'' on the Wurlitzer,'
INSERT INTO #poem(line)
SELECT 'I can speak Portuguese like a Berlitzer.'
/*from Odgen Nash's wonderful poem 'No Doctors Today, Thank-you'
注意的方式之一插入第二把"字的"分隔符（如quot;我可以玩"筷子"上Wurlitzerquot;）
SQL Server的继承从它的Sybase祖先的字符串的大小限制。这复杂的操纵大量的文字。但是，这种限制已纠正SQL Server 2005中具有特殊的数据类型，VARCHAR（MAX）。文本现在已过时作为数据类型，但充分利用到SQL Server 2005以前的版本，它相关。从表中选择{C}字符串数据类型
有三个基本的字符串类型（在括号中的Unicode等值）:CHAR（NCHAR）
VARCHAR（NVARCHAR）
（n文字）
2005年新的字符串变量和以前的版本之间最近的等值如下:
的XML - GT;
n文字VARCHAR（MAX）- GT;文字
为nvarchar（max）- GT; n文字（从SQL Server 2005发布服务器复制到SQL Server 2000用户，如果这个映射是自动，但它也知道是怎么回事）。
大多数欧洲语言可以由8位字符集。一个"全球性"的系统，可以代表所有的语言，我们必须选择为Unicode，使用nvarchar或nchar或NTEXT。奇怪的是，代表Unicode常量的方法是区分大小写的，大写的N前缀（N代表SQL - 92标准的国家语言）SELECT '锟斤拷锟?,N'锟斤拷锟? --??? 锟斤拷锟?
在第一种情况的字符不能代表（音符，但在第二种情况下，他们可以
Unicode常量被解释为Unicode数据，并没有评估使用的代码页。 Unicode常量确实有整理，虽然，这决定了比较，并区分大小写。 Unicode数据存储每个字符使用两个字节SELECT DATALENGTH(N'This one is a unicode string'),
DATALENGTH('This is not a unicode string')
/*
----------- -----------
56 28
*/
，你会看到第一个字符串需要两次第二Unicode字符串常量存储，支持增强的排序规则。字符串和校对
校对确定排序，字符串比较的结果。常量分配当前数据库的默认排序规则，除非使用COLLATE子句来覆盖它。
看看有哪些可用，使用...SELECT * FROM ::fn_helpcollations()
...这产生了许多归类，包括以下列表...Latin1_General_BIN
Latin1_General_CI_AI
Latin1_General_CI_AI_WS
Latin1_General_CI_AI_KS
Latin1_General_CI_AI_KS_WS
... ...然后你就可以尝试在这些表达式出来* /SELECT CASE WHEN 'A'<>'a' collate Latin1_General_CI_AI
THEN 'Different' ELSE 'same' END
-- same
SELECT CASE WHEN 'A'<>'a' collate Latin1_General_CS_AI
THEN 'Different' ELSE 'same' END
-- different
因此，必须明确有关排序规则在必要时，目的是要跨数据库移植的任何函数或存储过程。校对可以选择服务器，数据库，列或表达式，但我们将只说明在表达其选择。
的排序规则名称中使用的术语和缩写的一些需要解释
二进制的BIN
二进制是最快的排序顺序。排序和比较的基础上为每个字符定义位模式的数据。二进制排序顺序区分大小写（小写之前大写），并区分重音。
如果一个人选择的语言为基础的排序，而比二进制排序，SQL Server将遵循相关语言或字母表的字典中定义的排序和比较规则。
敏感案例，政务司司长
大小写的排序规则是指字母的大写和小写版本被认为是不同的。SELECT CASE WHEN 'A'<>'a' collate Latin1_General_CS_AI
THEN 'Different' ELSE 'same' END
重音敏感，因为
区分重音整理手段，例如，'A'是不等于"。排序字符串，使字符串开头，但不同的口音，不会进行排序在一起* /选择时，"a'lt; GT"然后整理Latin1_General_CI_AS"不同的"ELSE"同"结束/
假名敏感的KS
指定日文假名字符的两种类型:平假名和片假名，是不同的
宽度敏感
指定一个单字节（半角）半角字符相同的字符作为一个双字节（全宽）代表zenkaku性格不同的半角字符，字形图像占用的字符显示细胞的一半。分配和截断
String变量的工作同样表中的字符串数据，但如果试图分配一个字符串变量的长度比SQL Server的行为方式。
已分配给字符串变量时，必须非常小心观察截断。分配给一个字符串变量原因截断而导致错误。这样做是为了实现与CHAR数据类型的行为的一致性。DECLARE @message VARCHAR(20)
SELECT @Message=
'This is a long string which will get truncated without you knowing'
SELECT @Message
-----------------------------------------------
-- This is a long string
--..whereas inserting into a table triggers an error
DECLARE @messageTable TABLE (message VARCHAR(20))
INSERT INTO @MessageTable(Message)
SELECT 'This is a very long long string which will overflow'
------------------------------------------------
-- String or binary data would be truncated.
-- The statement has been terminated.
--if you are passing a variable to a stored procedure or function,
--again it truncates without telling you!
CREATE PROCEDURE #spTestStringParameter
@message VARCHAR(20)
AS
SELECT @message
GO
EXECUTE #spTestStringParameter
'This is a string which will get truncated without you knowing'
因此，在必要的情况下，这是明智的检查可能溢出的字符串输入。这里是一个存储过程，溢出检查的一个片段。我被抓了很多次，所以我劝你在这样一种防范措施ALTER PROCEDURE #spTestStringParameter
@message VARCHAR(21)
AS
IF LEN(@message)=21
RAISERROR(
'input parameter @message, beginning ''%s...'' truncated!',
16,1,@message)
SELECT @message
GO
Len函数返回查找字符串的长度的字符串的长度并不总是直截了当。SELECT LEN('Who would have thought this was shorter ')--39
SELECT LEN(' ...than this')--51
...因为在SQL Server中的字符串的长度不包括尾随空格，这意味着，如果你想在一个字符串的真实长度，它必须通过
在第一个例子，我们代替一个不同的字符的空间（它不会不管什么），而在第二种情况下，我们添加了一个非空格字符，所以没有尾随空格ASCII和Unicode
ASCII函数的返回返回ASCII值char或varchar字符串的第一个字符的ASCII码？如果它不能这样做！SELECT CHAR(ASCII('P'))
所以让我们用一个简单的代码位，说明使用的ASCII字符显示在一个字符串中的字符值，（我在紧急情况下使用这在过去）DECLARE @ASCIIValues VARCHAR(8000)
DECLARE @originalString VARCHAR(80)
SELECT @originalString=' What
is here?'
WHILE LEN(@originalString)>0
BEGIN
SELECT @ASCIIValues=COALESCE(@ASCIIValues+',','')
+CAST(ASCII(@OriginalString) AS VARCHAR)
SELECT @originalString=SUBSTRING(@originalString,2,80)
END
SELECT @AsciiValues
---------------------------------------------------------------------
/*
9,87,104,97,116,13,10,105,115,32,104,101,114,101,63
*/
的UNICODE ASCII CHARS或VARCHAR同样的事情，一个Unicode字符串NCHAR
这将会给你的Unicode表示的字符。请注意如何能代表十六进制字符串的字符值。在这里，来说明它的使用，是一些有用的Unicode货币符号！SELECT NCHAR(0x20AB),'Vietnamese Dong'
SELECT NCHAR(0x20AA),'Shequel'
SELECT NCHAR(0xA3),'pound sign'
SELECT NCHAR(0x20A3),'French Franc'
SELECT NCHAR(0x20Ac),'Euro'
SELECT NCHAR(0x20A8),'Rupee'
SELECT NCHAR(0x20A7),'Peseta'
SELECT NCHAR(0x20A6),'Naira'
，您可能需要设置你的结果窗格中，以Unicode来看到这些！CHAR
返回的整数代码所代表的ASCII字符。在这个例子中，我们将投入一个字符串CR /换行序列SELECT 'first line'+CHAR(13)+CHAR(10)+ 'second line'
-----------------------
-- first line
-- second line
PATINDEX中提供了一个很大的通用性，在查找文本数据的字符串。它还允许您通过通配符搜索。
我们可以，例如，用一个词第一次出现两个或两个以上的元音开始显示字符串的一部分SELECT '...'+SUBSTRING(line,PATINDEX('% [aeiou][aeiou]%',line),10)
+'...'
FROM #poem
WHERE ' '+line LIKE '% [aeiou][aeiou]%'
PATINDEX实用性是从根本上减少的事实，有没有办法在原来的字符串通配符匹配的检测序列的末尾。CHARINDEX
CHARINDEX提供了一个标准的方法搜索字符串中找到一个子字符串，并返回字符串的开始位置。它允许你指定搜索的起始位置增加了多功能性。这是特别有用的地方，你必须找到一个字符串出现的所有。考虑下面这个简单的程序表分割成分隔的字符串（例如，您可能会发现在"序列化"的数据）。CREATE FUNCTION dbo.uftSplitVarcharToTable
(
@StringArray VARCHAR(8000),
@Delimiter VARCHAR(10)
)
RETURNS
@Results TABLE
(
SeqNo INT IDENTITY(1, 1), Item VARCHAR(8000)
)
AS
BEGIN
DECLARE @Next INT
DECLARE @lenStringArray INT
DECLARE @lenDelimiter INT
DECLARE @ii INT
--initialise everything
SELECT @ii=1, @lenStringArray=LEN(REPLACE(@StringArray,' ','|')),
@lenDelimiter=LEN(REPLACE(@Delimiter,' ','|'))
--notice we have to be cautious about LEN with trailing spaces!
--while there is more of the string锟?/span>
WHILE @ii<=@lenStringArray
BEGIN--find the next occurrence of the delimiter in the stringarray
SELECT @next=CHARINDEX(@Delimiter, @StringArray + @Delimiter, @ii)
INSERT INTO @Results (Item)
SELECT SUBSTRING(@StringArray, @ii, @Next - @ii)
--note that we can get all the items from the list by appeending a
--delimiter to the final string
SELECT @ii=@Next+@lenDelimiter
END
RETURN
END
和常规，可以用这样的简单... ...SELECT * FROM dbo.uftSplitVarcharToTable(
'First|second|third|fourth|fifth|sixth','|')
你应该看到从表中的列表中的所有项目。一旦你有一个这样的功能，您可以使用这些深奥的任务，例如，HTML或XML标签剥离出！
这将产生以下的诗句.... Song of the Open Road
I think that I shall never see
A billboard lovely as a tree
Perhaps unless the billboards fall,
I'll never see a tree at all
当然，技术工程一样容易剥离括号内的文字字符串或任何其他分隔符！
，所以只用一个用户定义的函数中使用的内置函数，你有一个强大的工具更换
我们已经看到了REPLACE函数已经被使用AA Len的怪癖变通。这是最有用的字符串函数之一。它将取代所有出现的一个字符串与另一个。例如
？？/ P>SELECT REPLACE(REPLACE(REPLACE(REPLACE(
'Dear %1, you are considerably overdrawn to the tune of %2 in your %3 account.
Please phone our %4 for suggestions on debt management.'
,'%1','Miss Page'),'%2','锟?45.67'),'%3','current'),'%4','Mr Gross')
，这将给...
或SELECT LTRIM(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE(
' '+line+' ',
' was ',' were '),
' wasn''t',' weren''t'),
' me ',' you '),
' my ',' your '),
' I ',' You '),
' I''ve ',' You''ve '))
FROM #poem
这完全改变的意义！东西
的东西是字符串替换的瑞士军刀。您可以插入任意数量的字符在字符串中的特定点，在这一点上删除现有的字符选项。重复自己的歉意，这里是一个很好的例子使用的东西，插入序号后缀为日期。凝炼任何其他方式，是很难做到的。SELECT
DATENAME(dw,GETDATE())+', '
+ STUFF(CONVERT(CHAR(11),GETDATE(),106),3,0,
SUBSTRING(
'stndrdthththththththththththththththththstndrdthththththththst '
,(DATEPART(DAY,GETDATE())*2)-1,2))
Thursday, 02nd Nov 2006
之一，甚至可以使用它笨拙的操作，如删除字符串的一部分，我将显示在文章后面。
切片字符串:左右和SUBSTRING
有三个切片成子字符串，一般都是使用的功能。这是左，右和SUBSTRING。左翼给出了字符串和权利，然而，许多字符指定从左边，或启动，但是给您的权利，或结束的字符串，从指定的字符。 SUBSTRING如离去，但允许您指定的开始位置。
下面是另一个字符串切片机基础上，使用CHARINDEX，左，东西，喜欢前面的例子，这片分隔的字符串到一个表系列。CREATE FUNCTION dbo.uftSecondSplitVarcharToTable
(
@StringArray VARCHAR(8000),
@Delimiter VARCHAR(10)
)
RETURNS
@Results TABLE
(
SeqNo INT IDENTITY(1, 1), Item VARCHAR(8000)
)
AS
BEGIN
DECLARE @Splitpoint INT
DECLARE @lenDelimiter INT
--initialise everything
SELECT @lenDelimiter=LEN(REPLACE(@Delimiter,' ','|'))
--notice we have to be cautious about LEN with trailing spaces!
--while there is more of the string
WHILE 1=1
BEGIN
SELECT @splitpoint=CHARINDEX(@Delimiter,@StringArray)
IF @SplitPoint=0
BEGIN
INSERT INTO @Results (Item) SELECT @StringArray
BREAK
END
INSERT INTO @Results (Item)
SELECT LEFT(@StringArray,@Splitpoint-1)
--use STUFF to delete the first x characters of the string!
SELECT @StringArray=
STUFF(@StringArray,1,@Splitpoint+@lenDelimiter-1,'')
END
RETURN
END
---------------------------------------------------------------------
--So we can use this routine to get a word frequency count of the
--poem
DECLARE @LongString VARCHAR(8000)
SELECT @LongString
=COALESCE(@longString+' ','')+REPLACE(line,',','')+' '
FROM #poem
SELECT COUNT(*), item
FROM dbo.uftSecondSplitVarcharToTable(@LongString,' ')
WHERE item<> ''
GROUP BY item
ORDER BY COUNT(*),item DESC
/* RIGHT returns the rightmost characters of a string as with: */
SELECT RIGHT('Robyn Page',4)
只是偶尔，复制功能是非常有用的，但主要集中在固定宽度的文本格式化。它创建了一个字符串，使用任何字符指定，任何你指定的长度。在这里，我们将演示其使用* /SELECT '+'+REPLICATE('-',10)+'+'+CHAR(13)+CHAR(10)
+REPLICATE('|'+REPLICATE(' ',10)+'|'+CHAR(13)+CHAR(10),8)
+'+'+REPLICATE('-',10)+'+'+CHAR(13)+CHAR(10)
+----------+
| |
| |
| |
| |
| |
| |
| |
| |
+----------+
空间（10）（返回一个字符串组成十个空格），相当于复制（''，10）。空间功能，然而，许多的空间，您指定一个字符串返回。在固定宽度的字体结果印在小数点对齐或右对齐打印报告日子里，它更受欢迎
e.gSELECT SPACE(10-CHARINDEX('.',item+'.'))+item
FROM dbo.uftSecondSplitVarcharToTable(
'123.56,45.873,4.5,4.0,45768.9,354.67,12.0,66.97,45,4.5672',',')
/*-------------
123.56
45.873
4.5
4.0
45768.9
354.67
12.0
66.97
45
4.5672
*/
反向功能，它只是返回字符串向后执行此发现的消息... * /SELECT REPLACE(REVERSE(
'evil ot sah eh|hcihw ni|pmaws a ylno sa|nam a fo skniht|mreg a tub|
nem ot elbanoitcejbo|yrev era smreg'),'|','
')
反向偶尔是非常有用的，在这些场合没有其他人会做。在这个例子中，我们找到一个子字符串最后一次出现，并删除它SELECT
REVERSE(STUFF(REVERSE(line),
CHARINDEX(REVERSE('There be '),REVERSE(line))
,9,''))
FROM
(
SELECT
[line]='There be no truth in that there be and that is what I say'
)f
--which yields...
--There be no truth in that and that is what I say
有两个有用的功能，下和上，这是不言自明:SELECT UPPER('i have drunk too much caffeine'),
LOWER('I MUST CALM DOWN')
要做到资本，你可能想这样的功能，这显示了一个更复杂的上层使用CREATE FUNCTION [dbo].[ufsCapitalize]
(
@string VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Next INT
WHILE 1=1
BEGIN
--find word space followed by lower case letter
--This makes assumptions about the language
SELECT @next=
PATINDEX('%[^a-zA-Z][abcdefghijklmnopqurstuvwzyz]%',
' '+@string collate Latin1_General_CS_AI)
IF @next =0 BREAK
SELECT @String =
STUFF(@String,@Next,1,UPPER(SUBSTRING(@String,@Next,1)))
END
RETURN @string
END
---------------------------------------------------------------------
--so now we try it out锟?/span>
SELECT dbo.ufsCapitalize('leonard j poops jnr')
结果英寸..Leonard J Poops Jnr
有两个功能，可用于修剪领先的间距或尾随空格从字串SELECT LTRIM(' this has leading spaces, ')
+RTRIM('this has trailing spaces ')
--or both!
SELECT '"'
+LTRIM(RTRIM(' This string has spaces fore and aft '))
+'"'
来进行模糊搜索，有两个老"SOUNDEX"算法这些功能没有比历史的兴趣更是和他们似乎是在那里纯粹由于历史的原因，但我有兴趣，如果任何人都可以指出对他们的使用。即使他们在一种语言，他们不工作，他们甚至没有有效的国际
功能的SOUNDEX和差异
例如:Select line FROM #poem WHERE DIFFERENCE(line,'I was')=4
对于过时的文本和ntext数据类型，有一个只有几个功能，将与他们合作。这些PATINDEX，TEXTVALID，子串，DATALENGTH和TEXTPTR由于这些是覆盖在其他地方，或太深奥工作台范围内，我想指你上线，其中包括他们很好地预订有些问题当你一个字符串赋给一个VARCHAR变量，其长度较短的字符串，然后会发生什么事当复制从SQL 2005发布到SQL 2000用户，是一个为nvarchar（max）如何映射？您是如何指定的字符串的排序顺序？什么是宽灵敏度在整理？你会如何，一个功能，发现在一个字符串，以小写字符开始的第一个字开始。你如何去小数点对齐在一个固定宽度的的字体的数字？怎么可能去括号中剥离的所有文字，从一个VARCHAR变量？什么整理将是一个不错的选择ID您正在编写一个SQL Server数据库，将在几个欧洲国家使用* /