This “workbench” on string handling and manipulation in SQL Server is a companion to my previous one on dates and times. Rather than rehash what is readily available on SQL Server Books Online, I’ve once again tried to provide a starting point for your own experiments.<\/p>\n
You can download the article as a SQL script to paste in its entirety into the Query Analyser, SSMS or other GUI and execute the individual examples (and it is available, as an attachment to the article). You’ll find the download at the bottom of the article.<\/p>\n
The main difficulty in dealing with Strings in SQL Server is that the techniques are rather open-ended. There are often a number of different ways to achieve the same end result. The String functions such as As with the previous ‘workbench’, my advice is to download the .sql file (see the Code Download link to the right of the article title) open it up in SQL Server, and start experimenting!<\/p>\n Ideally, you’ll also have Books online open in a browser, to provide supplementary and background information.<\/p>\n I’ve added a few questions at the end just so you can check on your progress. Overall, I hope that this workbench illustrates how easy string handling is in SQL Server once the basic ideas are grasped.<\/p>\n As a practice table for this workbench we will create a temporary table and stock it with string data.<\/p>\n Note the way that one inserts the ‘ delimiter (as in “I can play ‘Chopsticks’ on the Wurlitzer”) by putting in a second ‘ character.<\/p>\n –you can, of course, select according to strings, or partial strings<\/p>\n SELECT line FROM #poem WHERE line LIKE ‘I Was%’–‘I Was’ at<\/p>\n –the start of the line (‘%’ means ‘any number 0-n of any character)<\/p>\n SELECT line FROM #poem WHERE line LIKE ‘%sam%’–‘sam’ anywhere<\/p>\n SELECT line FROM #poem WHERE line LIKE ‘%?%’–? anywhere<\/p>\n SELECT line FROM #poem WHERE line BETWEEN ‘a’ AND ‘e’–returns<\/p>\n –all lines starting with a,b,c or d<\/p>\n SELECT line FROM #poem WHERE line < ‘D’ –returns one line<\/p>\n SELECT line FROM #poem WHERE ‘ ‘+line LIKE ‘% g_tsam%’<\/p>\n –here we want only words starting with g?tsam. the underscore<\/p>\n –character means ‘one character, anything you like’. The leading<\/p>\n –space makes the logic simpler as it allows for occurences of the<\/p>\n –word at the beginning of the line<\/p>\n SELECT line FROM #poem WHERE ‘ ‘+line LIKE ‘%[aeiou][aeiou]%’<\/p>\n –any line with two consecutive vowels in it<\/p>\n –the ‘[]’ delimiters contain a range of characters<\/p>\n –and mean ‘one character, anything in the range’<\/p>\n –here, it is a list of vowels<\/p>\n SELECT line FROM #poem WHERE ‘ ‘+line LIKE ‘%[^a-z][aeiou][aeiou]%’<\/p>\n — returns any line containing a word beginning with two vowels<\/p>\n –the [^a-z] will mean a whitespace character in European<\/p>\n –languages as long as you set your collation accordingly!<\/p>\n SQL Server inherited from its Sybase ancestors a limit to the size of string. This complicated the manipulation of large quantities of text. However, this limit has been remedied since SQL Server 2005 with the special datatype, There are three basic string types (Unicode equivalents shown in brackets):<\/p>\n The nearest equivalents between the new 2005 string variables and previous versions is as follows:<\/p>\n (If replicating from a SQL Server 2005 publisher to a SQL Server 2000 subscriber, this mapping is done automatically but it’s well to be aware of what is going on).<\/p>\n Most European languages can be represented by the eight-bit character sets. For a ‘global’ system that can represent all languages, one must opt for Unicode, and use Unicode constants are interpreted as Unicode data, and are not evaluated using a code page. Unicode constants do have a collation, though, which determines comparisons and case sensitivity. Unicode data is stored using two bytes per character.<\/p>\n You’ll see that the first string needed twice the storage of the second Unicode string constants support enhanced collations.<\/p>\n Collations determine the result of sorts, and string comparisons. Constants are assigned the default collation of the current database, unless the To see what are available, use:<\/p>\n The name of the collation can include the language, the country or region, and the case, accent, and width sensitivity. Windows collations that designate a country or region name in addition to the language name are usually distinct because they have different sort orders than other collations in the same language and map to a different code page.<\/p>\n So any function or stored procedure that is intended to be portable across databases must be explicit about collation where necessary. Collations can be selected at Server, Database, column or expression, but we’ll only illustrate its selection in an expression.*\/<\/p>\n Some of the jargon and abbreviations used in the names for the collations require explanation<\/p>\n Binary is the fastest sorting order. It sorts and compares data based on the bit patterns defined for each character.<\/p>\n Binary sort order is case-sensitive (lowercase precedes uppercase), and accent-sensitive.<\/p>\n If one chooses a language-based sort rather than a binary sort, SQL Server follows sorting and comparison rules as defined in dictionaries for the associated language or alphabet.<\/p>\n<\/dd>\n Case-sensitive collation means that the uppercase and lowercase versions of letters are considered different.<\/p>\n Accent-Sensitive collation means that, For example, ‘a’ is not equal to ‘\u00e2’. and will sort strings so that strings beginning with a but with different accents, will not be sorted together.<\/p>\n Specifies that the two types of Japanese kana characters: Hiragana and Katakana, are different<\/p>\n<\/dd>\n Specifies that a single-byte (half-width) ‘hankaku’ character and the same character represented as a double-byte (full-width) ‘zenkaku’ character are different Half-width characters has a glyph image that occupies half of the character display cell.<\/p>\n<\/dd>\n<\/dl>\n String variables work similarly to string data in tables except for the way SQL Server behaves if an attempt is made to assign a string that is longer than the variable’s length.<\/p>\n One has to be very careful to watch out for truncation when assigning to string variables. Assigning to a string variable causes truncation without causing an error. This is done in order to achieve consistency with the behaviour of the So, where necessary, it is wise to check the string inputs for possible overflow. Here is a fragment of a stored procedure that checks for overflow. I’ve been caught out many times so I advise you to put in a precaution like this:<\/p>\n The …because the length of strings in SQL Server do not include trailing spaces this means that, if you want the true length of a string it must be done by:<\/p>\n In the first example, we substitute a different character for the space (it doesn’t matter what), whereas, in the second case we add a non-space character so the spaces aren’t trailing.<\/p>\n The ASCII function returns the ASCII code of the first character of a char or Varchar string it returns the ASCII value of So let’s use a simple bit of code, illustrating the use of ASCII, to display the character values of the characters in a string, (I’ve used this in an emergency in the past)<\/p>\n This will give you the character represented by the Unicode. Note how one can represent character values as hex strings. Here, to illustrate its use, are some useful Unicode currency symbols!<\/p>\n You may need to set your results pane to Unicode to see these properly!<\/p>\n Returns the ASCII character represented by the integer code. In this example we’ll put a CR\/Linefeed sequence into a string.<\/p>\n We could, for example, show the part of the string with the first occurrence of a word that starts with two or more vowels.<\/p>\n SELECT ‘…’+SUBSTRING(line,PATINDEX(‘% [aeiou][aeiou]%’,line),10)<\/p>\n +’…’<\/p>\n FROM #poem<\/p>\n WHERE ‘ ‘+line LIKE ‘% [aeiou][aeiou]%’<\/p>\n The usefulness of patindex is fundamentally lessened by the fact that there is no way of detecting the end of the sequence in the original string that matched the wildcard.<\/p>\n Charindex provides a standard way of searching within strings to find a substring, and returning the starting position of the string. It has the added versatility of allowing you to specify the starting location of the search. This is especially useful in places where you must find all occurrences of a string. Consider the following simple routine which splits delimited strings (such as you might find in ‘serialised’ data) into a table.<\/p>\n You should see all the items from the list in a table. Once you have a function like this, you can then use it for such esoteric tasks as, for example, stripping tags out of HTML or XML!<\/p>\n Naturally, the technique works just as easily stripping bracketed text from strings or any other delimiter!<\/p>\n So with just three of the built-in functions used in a user-defined function, you have a powerful tool<\/p>\n We have seen the For example…<\/p>\n But the simpleset way of stripping out unwanted extra spaces from a column would be to use something like this, even if it looks a bit awkward.<\/p>\n This version is safe for blocks of spaces up to 461 characters long, which should suffice.<\/p>\n The huge advantage is its speed, as it requires no UDF to clean up text with space in it.<\/p>\n Even neater, (thanks to Mladen Prajdic):<\/p>\n One can even use it for awkward operations like deleting part of the string, as I will show later on in the article.<\/p>\n There are three functions that are generally used for slicing strings into substrings. These are Here is another string-slicer based on using So we can use this routine to get a word frequency count of the poem.<\/p>\n Just occasionally, the REPLICATE function is very handy, though mainly in formatting fixed-width text. It creates a string, using whatever character you specify, to whatever length you specify.<\/p>\n Here, we’ll demonstrate its use:<\/p>\n Which draws a box! As an exercise, what about writing the poem within a box?<\/p>\n The How about this trick for getting just the last part of a url?<\/p>\n There are two useful functions, SELECT UPPER(‘i have drunk too much caffeine’),<\/p>\n LOWER(‘I MUST CALM DOWN’)<\/p>\n To do capitalisation, you may want a function like this, which shows a more complex use of So now we try it out…<\/p>\n There are two functions that can be used to trim either the leading spaced or trailing spaces from strings.<\/p>\n For doing fuzzy searches, there are two functions based on the old ‘soundex’ algorithm These are of no more than historical interest and they seem to be in there purely for historical reasons but I’d be interested if anyone can point out a use for them. Even if they worked in one language, which they don’t, they aren’t even internationally valid.<\/p>\n The functions are e.g.<\/p>\n For the deprecated If you liked reading this workbench, then take a look at Robyn Page and Phil Factor’s subsequent series on string functions.<\/p>\n String searching and manipulation in SQL Server can be error-prone and tedious…unless you’re armed with the techniques described in Robyn’s string manipulation workbench……<\/p>\n","protected":false},"author":221812,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143525],"tags":[4498,4149,4150,4151,4497,4499,4494,4496,4495,4460],"coauthors":[6814],"class_list":["post-179","post","type-post","status-publish","format-standard","hentry","category-learn","tag-collations","tag-learn-sql-server","tag-sql","tag-sql-server","tag-string-data-types","tag-string-functions","tag-string-handling","tag-string-manipulation","tag-string-searching","tag-workbench"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/179","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221812"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=179"}],"version-history":[{"count":10,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/179\/revisions"}],"predecessor-version":[{"id":81283,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/179\/revisions\/81283"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=179"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=179"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=179"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=179"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}STUFF<\/code> or REVERSE<\/code> are of little use by themselves, but when used in conjunction with others, they become extremely useful. Other functions are there as ‘legacy items’ meaning that it is difficult to remove functions such as SOUNDEX<\/code> as there are still a few die-hards still using them<\/p>\nContents<\/h2>\n
\n
CREATE TABLE #Poem (line VARCHAR(255), theOrder INT IDENTITY(1,1))\r\n \r\n INSERT INTO #poem(line)\r\n SELECT 'I will pen me my memoirs.'\r\n INSERT INTO #poem(line) \r\n SELECT 'Ah, youth, youth! What euphorian days them was!'\r\n INSERT INTO #poem(line) \r\n SELECT 'I wasn''t much of a hand for the boudoirs,'\r\n INSERT INTO #poem(line) \r\n SELECT 'I was generally to be found where the food was.'\r\n INSERT INTO #poem(line) \r\n SELECT 'Does anybody want any flotsam?'\r\n INSERT INTO #poem(line) \r\n SELECT 'I''ve gotsam.'\r\n INSERT INTO #poem(line) \r\n SELECT 'Does anybody want any jetsam?'\r\n INSERT INTO #poem(line) \r\n SELECT 'I can getsam.'\r\n INSERT INTO #poem(line) \r\n SELECT 'I can play ''Chopsticks'' on the Wurlitzer,'\r\n INSERT INTO #poem(line) \r\n SELECT 'I can speak Portuguese like a Berlitzer.'\r\n \/*from Odgen Nash's wonderful poem 'No Doctors Today, Thank-you' \r\n <\/pre>\n
Selecting from a table<\/h3>\n
<\/pre>\n
The String Datatypes<\/h3>\n
Varchar(MAX)<\/code>. TEXT<\/code> is now deprecated as a datatype but is used sufficiently in versions previous to SQL Server 2005 to make it relevant.<\/p>\n<\/code><\/code><\/p>\n\n
<\/code><\/code><\/p>\n\n
\n<\/code><\/p>\nNVarchar<\/code>, or NChar<\/code> or NText<\/code>. Peculiarly, the method of representing Unicode constants is case-sensitive, being the uppercase N prefix (N stands for National Language in the SQL-92 standard).<\/p>\n SELECT DATALENGTH(N'This one is a unicode string'),\r\n DATALENGTH('This is not a unicode string')\r\n \/* ----------- ----------- \r\n 56 28\r\n <\/pre>\nStrings and Collations<\/h3>\n
COLLATE<\/code> clause is used to override it.<\/p>\n SELECT * FROM ::fn_helpcollations()\r\n \/*... which produces a list of many collations, including the following ...\r\n \r\n Latin1_General_BIN \r\n Latin1_General_CI_AI \r\n Latin1_General_CI_AI_WS \r\n Latin1_General_CI_AI_KS\r\n Latin1_General_CI_AI_KS_WS\r\n \r\n Which you can then try out in these expressions\/*\r\n SELECT CASE WHEN 'A'<>'a' collate Latin1_General_CI_AI \r\n THEN 'Different' ELSE 'same' END\r\n -- same\r\n SELECT CASE WHEN 'A'<>'a' collate Latin1_General_CS_AI \r\n THEN 'Different' ELSE 'same' END\r\n -- different\r\n <\/pre>\n
\n
SELECT CASE WHEN 'A'<>'a' collate Latin1_General_CS_AI \r\n THEN 'Different' ELSE 'same' END\r\n <\/pre>\n<\/dd>\n
SELECT CASE WHEN 'a'<>'\u00e2' collate Latin1_General_CI_AS \r\n THEN 'Different' ELSE 'same' END\r\n <\/pre>\n<\/dd>\n
Assignment and Truncation<\/h3>\n
CHAR<\/code> datatype.<\/p>\n DECLARE @message VARCHAR(20)\r\n SELECT @Message=\r\n 'This is a long string which will get truncated without you knowing'\r\n SELECT @Message\r\n -----------------------------------------------\r\n -- This is a long string\r\n \r\n --..whereas inserting into a table triggers an error\r\n DECLARE @messageTable TABLE (message VARCHAR(20))\r\n INSERT INTO @MessageTable(Message)\r\n SELECT 'This is a very long long string which will overflow'\r\n ------------------------------------------------\r\n -- String or binary data would be truncated.\r\n -- The statement has been terminated.\r\n \r\n --if you are passing a variable to a stored procedure or function, \r\n --again it truncates without telling you!\r\n CREATE PROCEDURE #spTestStringParameter\r\n @message VARCHAR(20)\r\n AS\r\n SELECT @message\r\n GO\r\n EXECUTE #spTestStringParameter \r\n 'This is a string which will get truncated without you knowing'\r\n <\/pre>\n
ALTER PROCEDURE #spTestStringParameter\r\n @message VARCHAR(21)\r\n AS\r\n IF LEN(@message)=21\r\n RAISERROR(\r\n 'input parameter @message, beginning ''%s...'' truncated!',\r\n 16,1,@message)\r\n SELECT @message\r\n GO \r\n <\/pre>\n
The string Functions<\/h3>\n
LEN<\/h4>\n
LEN<\/code> function returns the length of the string. Finding the length of a string is not always straightforward.<\/p>\n SELECT LEN('Who would have thought this was shorter ')--39\r\n SELECT LEN(' ...than this')--51\r\n <\/pre>\n SELECT LEN(REPLACE(\r\n 'This string has trailing spaces ',' ','|'))--45\r\n --or\r\n SELECT LEN(\r\n 'This string has trailing spaces '+'.')-1--45\r\n <\/pre>\n
ASCII and UNICODE<\/h4>\n
?<\/code> if it can’t do so!<\/p>\n SELECT CHAR(ASCII('P'))\r\n <\/pre>\n ---------------------------------------------------------------------\r\n DECLARE @ASCIIValues VARCHAR(8000)\r\n DECLARE @originalString VARCHAR(80)\r\n SELECT @originalString=' What\r\n is here?'\r\n WHILE LEN(@originalString)>0\r\n BEGIN\r\n SELECT @ASCIIValues=COALESCE(@ASCIIValues+',','')\r\n +CAST(ASCII(@OriginalString) AS VARCHAR)\r\n SELECT @originalString=SUBSTRING(@originalString,2,80)\r\n END\r\n SELECT @AsciiValues\r\n ---------------------------------------------------------------------\r\n \/*\r\n 9,87,104,97,116,13,10,105,115,32,104,101,114,101,63\r\n <\/pre>\n
UNICODE<\/code> does the same thing for a Unicode string that ASCII does for CHARs<\/code> or VARCHARs<\/code>.<\/p>\nNChar<\/h4>\n
SELECT NCHAR(0x20AB),'Vietnamese Dong'\r\n SELECT NCHAR(0x20AA),'Shequel'\r\n SELECT NCHAR(0xA3),'pound sign'\r\n SELECT NCHAR(0x20A3),'French Franc'\r\n SELECT NCHAR(0x20Ac),'Euro'\r\n SELECT NCHAR(0x20A8),'Rupee'\r\n SELECT NCHAR(0x20A7),'Peseta'\r\n SELECT NCHAR(0x20A6),'Naira'\r\n <\/pre>\n
CHAR<\/h4>\n
SELECT 'first line'+CHAR(13)+CHAR(10)+ 'second line'\r\n ----------------------- \r\n -- first line\r\n -- second line\r\n <\/pre>\n
PATINDEX<\/h4>\n
PATINDEX<\/code> provides you with a great deal of versatility in finding strings in TEXT<\/code> data. It also allows you to search by wildcard.<\/p>\n <\/pre>\n
PatIndex<\/code> is great if, for example, you want to extract the first number from a string.<\/p>\n CREATE FUNCTION dbo.ufsFirstNumberFrom (@String VARCHAR(MAX))\r\n RETURNS VARCHAR(40)\r\n AS BEGIN\r\n DECLARE @numberStart INT,\r\n @numberEnd INT\r\n SELECT @numberStart = PATINDEX('%[0-9]%',\r\n @String COLLATE Latin1_General_Ci_AI)\r\n SELECT @numberEnd = PATINDEX('%[0-9][^0-9.]%',\r\n @String + '|' COLLATE Latin1_General_Ci_AI)\r\n RETURN CASE WHEN @numberStart = 0 OR @numberend = 0\r\n THEN ''\r\n ELSE SUBSTRING(@String, @numberStart,\r\n 1 + @numberEnd - @numberStart)\r\n END\r\n END\r\n go\r\n SELECT dbo.ufsFirstNumberFrom('valve no. 345 open')\r\n SELECT dbo.ufsFirstNumberFrom('valve no. 345.23 is open')\r\n SELECT dbo.ufsFirstNumberFrom('18 people required out of 34')\r\n SELECT dbo.ufsFirstNumberFrom(NULL)\r\n SELECT dbo.ufsFirstNumberFrom('How many? about 45')\r\n <\/pre>\nCHARINDEX<\/h4>\n
CREATE FUNCTION dbo.uftSplitVarcharToTable \r\n (\r\n @StringArray VARCHAR(8000),\r\n @Delimiter VARCHAR(10)\r\n )\r\n RETURNS\r\n @Results TABLE\r\n (\r\n SeqNo INT IDENTITY(1, 1), Item VARCHAR(8000)\r\n )\r\n AS\r\n BEGIN\r\n DECLARE @Next INT\r\n DECLARE @lenStringArray INT\r\n DECLARE @lenDelimiter INT\r\n DECLARE @ii INT\r\n --initialise everything\r\n SELECT @ii=1, @lenStringArray=LEN(REPLACE(@StringArray,' ','|')),\r\n @lenDelimiter=LEN(REPLACE(@Delimiter,' ','|'))\r\n --notice we have to be cautious about LEN with trailing spaces!\r\n \r\n --while there is more of the string...\r\n WHILE @ii<=@lenStringArray\r\n BEGIN--find the next occurrence of the delimiter in the stringarray\r\n SELECT @next=CHARINDEX(@Delimiter, @StringArray + @Delimiter, @ii)\r\n INSERT INTO @Results (Item)\r\n SELECT SUBSTRING(@StringArray, @ii, @Next - @ii)\r\n --note that we can get all the items from the list by appeending a \r\n --delimiter to the final string\r\n SELECT @ii=@Next+@lenDelimiter\r\n END\r\n RETURN\r\n END\r\n ---------------------------------------------------------------------\r\n --and the routine can be used simply like this...\r\n SELECT * FROM dbo.uftSplitVarcharToTable(\r\n 'First|second|third|fourth|fifth|sixth','|')\r\n <\/pre>\n
DECLARE @HTMLString VARCHAR(8000),@Stripped VARCHAR(8000)\r\n SELECT @HTMLString=\r\n '<?xml version=\"1.0\" encoding=\"us-ascii\"?>\r\n <!DOCTYPE html PUBLIC \"-\/\/W3C\/\/DTD XHTML 1.0 Strict\/\/EN\"\r\n \"http:\/\/www.w3.org\/TR\/xhtml1\/DTD\/xhtml1-strict.dtd\">\r\n <html xmlns=\"http:\/\/www.w3.org\/1999\/xhtml\">\r\n <head>\r\n <title><\/title>\r\n <\/head>\r\n <body>\r\n <div style=\"float left: width:300px;\">\r\n <p style=\"font-size:larger\">\r\n <strong><em>Song of the Open Road<\/em><\/strong>\r\n <\/p>\r\n I think that I shall never see<br \/>\r\n A billboard Lovely as a tree<br \/>\r\n Perhaps unless the billboards fall,<br \/>\r\n I''ll never see a tree at all<br \/>\r\n <\/div>\r\n <\/body>\r\n <\/html>'\r\n SELECT @Stripped = COALESCE(@Stripped,'') \r\n + thetext FROM\r\n (SELECT \r\n [thetext]=SUBSTRING(Item, CHARINDEX('>', Item) + 1, 8000),\r\n seqno\r\n FROM dbo.uftSplitVarcharToTable(@HTMLString, '<'))f\r\n WHERE theText <>CHAR(13)+CHAR(10)\r\n ORDER BY SeqNo\r\n \r\n SELECT @Stripped\r\n \r\n \/* which will yield the following poem....\r\n Song of the Open Road\r\n I think that I shall never see\r\n A billboard lovely as a tree\r\n Perhaps unless the billboards fall,\r\n I'll never see a tree at all \r\n <\/pre>\nREPLACE<\/h4>\n
REPLACE<\/code> function being used already a a work-around for LEN<\/code>‘s quirks. It is one of the most useful of the String functions. It’ll replace all occurrences of one string with another.<\/p>\n SELECT REPLACE(REPLACE(REPLACE(REPLACE(\r\n 'Dear %1, you are considerably overdrawn to the tune of %2\r\n in your %3 account. \r\n Please phone our %4 for suggestions on debt management.'\r\n ,'%1','Miss Page'),'%2','\u00c2\u00a3345.67'),'%3','current'),'%4','Mr Gross')\r\n \/*\r\n which will give...\r\n Dear Miss Page, you are considerably overdrawn to the tune of \u00c2\u00a3345.67\r\n in your current account. \r\n Please phone our Mr Gross for suggestions on debt management.\r\n \r\n or*\/\r\n SELECT LTRIM(REPLACE\r\n (REPLACE\r\n (REPLACE\r\n (REPLACE\r\n (REPLACE\r\n (REPLACE(\r\n ' '+line+' ',\r\n ' was ',' were '),\r\n ' wasn''t',' weren''t'),\r\n ' me ',' you '),\r\n ' my ',' your '),\r\n ' I ',' You '),\r\n ' I''ve ',' You''ve '))\r\n FROM #poem\r\n \/*which changes the meaning entirely!\r\n \r\n or you can do it this way*\/\r\n CREATE FUNCTION ufsStubstitute (@String VARCHAR(MAX))\r\n RETURNS VARCHAR(MAX)\r\n AS BEGIN\r\n DECLARE @substitutions TABLE\r\n (\r\n before VARCHAR(12),\r\n After VARCHAR(12)\r\n )\r\n INSERT INTO @substitutions (before, after)\r\n SELECT ' was ',' were '\r\n UNION SELECT ' wasn''t',' weren''t'\r\n UNION SELECT ' me ',' you '\r\n UNION SELECT ' my ',' your '\r\n UNION SELECT ' I ',' You '\r\n UNION SELECT ' I''ve ',' You''ve '\r\n SELECT @string = LTRIM(REPLACE(' ' + @String + ' ',\r\n before, after))\r\n FROM @substitutions\r\n RETURN @String\r\n END\r\n go\r\n SELECT dbo.ufsStubstitute(line) FROM #poem\r\n \r\n \/* it is great for taking out unwanted spaces *\/\r\n DECLARE @trimmed VARCHAR(255) ,\r\n @LastTrimmed INT\r\n SELECT @trimmed = 'this has too many spaces' ,\r\n @LastTrimmed = 0\r\n \r\n WHILE LEN(@Trimmed) <> @LastTrimmed \r\n SELECT @LastTrimmed = LEN(@Trimmed) ,\r\n @trimmed = REPLACE(@trimmed, ' ', ' ')\r\n SELECT @Trimmed\r\n \r\n \/* or if you have a numbers table already, you could use that\r\n We'll do a little numbers table *\/\r\n DECLARE @Numbers TABLE ( number INT)\r\n INSERT INTO @Numbers (number)\r\n SELECT 2 UNION SELECT 3 --\r\n UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8\r\n \r\n DECLARE @trimmed VARCHAR(500)\r\n SELECT @trimmed = 'this has too many spaces' \r\n \r\n SELECT @Trimmed = REPLACE(@Trimmed, REPLICATE(' ', number), ' ')\r\n FROM @numbers ORDER BY number desc\r\n \r\n SELECT @Trimmed\r\n<\/pre>\n SELECT \r\n REPLACE\r\n (REPLACE\r\n (REPLACE\r\n (REPLACE\r\n (REPLACE\r\n (\r\n 'this has too many spaces' ,\r\n REPLICATE(CHAR(32), 21),CHAR(32)),\r\n REPLICATE(CHAR(32), 7), CHAR(32)),\r\n REPLICATE(CHAR(32), 4), CHAR(32)),\r\n REPLICATE(CHAR(32), 3), CHAR(32)),\r\n REPLICATE(CHAR(32), 2), CHAR(32))\r\n <\/pre>\n
SELECT REPLACE(\r\n REPLACE(\r\n REPLACE(\r\n 'this has too many spaces',\r\n CHAR(32), CHAR(32) + CHAR(160)),\r\n CHAR(160) + CHAR(32), ''),\r\n CHAR(160), '')\r\n <\/pre>\n
STUFF<\/h4>\n
STUFF<\/code> is the Swiss army knife of string substitution. You can insert any number of characters at a particular point in a string, with the option of deleting existing characters at that point. With apologies for repeating myself, here is a good example of the use of STUFF<\/code>, which inserts the ordinal suffix into a date. It is difficult to do it as concisely any other way.<\/p>\n SELECT \r\n DATENAME(dw,GETDATE())+', '\r\n + STUFF(CONVERT(CHAR(11),GETDATE(),106),3,0,\r\n SUBSTRING(\r\n 'stndrdthththththththththththththththththstndrdthththththththst '\r\n ,(DATEPART(DAY,GETDATE())*2)-1,2))\r\n \/*Thursday, 02nd Nov 2006\r\n \r\n \r\n and here is an amusing use of Stuff and Patindex to turn a camelCase variable into readable text.*\/\r\n \r\n DECLARE @camelVariable VARCHAR(255), @NextPlace INT,@Wildcard VARCHAR(80),\r\n @ch CHAR(1)\r\n SELECT \r\n @camelVariable = 'thisIsCamelCase',\r\n @wildCard ='%[abcdefghijklmnopqrstuvwxyz][ABCDEFGHIJKLMNOPQRSTUVWXYZ]%'\r\n \r\n WHILE 1=1\r\n BEGIN \r\n SELECT @nextPlace =PATINDEX(\r\n @wildCard, @camelVariable collate Latin1_General_CS_AI )\r\n IF @nextPlace=0 BREAK\r\n --SELECT @ch=LOWER(SUBSTRING(@camelVariable,@Nextplace+1,1))\r\n SELECT @camelVariable=STUFF(@camelVariable,@nextPlace+1,1,' '\r\n +LOWER(SUBSTRING(@camelVariable,@Nextplace+1,1)))\r\n end\r\n SELECT @CamelVariable\r\n \/*\r\n gives..\r\n this is camel case\r\n <\/pre>\n
Slicing Strings Up: LEFT RIGHT and SUBSTRING<\/h4>\n
LEFT<\/code>, RIGHT<\/code> and SUBSTRING<\/code>. LEFT<\/code> gives however many characters you specify from the left, or start, of the string and RIGHT<\/code> gives however many characters you specify from the right, or end, of the string. SUBSTRING<\/code> works like LEFT<\/code> but allows you to specify the start position.<\/p>\nCHARINDEX<\/code>, LEFT<\/code> and STUFF<\/code> which, likes the previous example, slices a series of delimited strings into a table.<\/p>\n CREATE FUNCTION dbo.uftSecondSplitVarcharToTable\r\n (\r\n @StringArray VARCHAR(8000),\r\n @Delimiter VARCHAR(10)\r\n )\r\n RETURNS\r\n @Results TABLE\r\n (\r\n SeqNo INT IDENTITY(1, 1), Item VARCHAR(8000)\r\n )\r\n AS\r\n BEGIN\r\n DECLARE @Splitpoint INT\r\n DECLARE @lenDelimiter INT\r\n \r\n --initialise everything\r\n SELECT @lenDelimiter=LEN(REPLACE(@Delimiter,' ','|'))\r\n --notice we have to be cautious about LEN with trailing spaces!\r\n \r\n --while there is more of the string\r\n WHILE 1=1\r\n BEGIN\r\n SELECT @splitpoint=CHARINDEX(@Delimiter,@StringArray)\r\n IF @SplitPoint=0 \r\n BEGIN\r\n INSERT INTO @Results (Item) SELECT @StringArray\r\n BREAK\r\n END\r\n INSERT INTO @Results (Item)\r\n SELECT LEFT(@StringArray,@Splitpoint-1)\r\n --use STUFF to delete the first x characters of the string!\r\n SELECT @StringArray=\r\n STUFF(@StringArray,1,@Splitpoint+@lenDelimiter-1,'')\r\n END\r\n RETURN\r\n END\r\n <\/pre>\n
DECLARE @LongString VARCHAR(8000)\r\n SELECT @LongString\r\n =COALESCE(@longString+' ','')+REPLACE(line,',','')+' ' \r\n FROM #poem\r\n \r\n SELECT COUNT(*), item\r\n FROM dbo.uftSecondSplitVarcharToTable(@LongString,' ')\r\n WHERE item<> ''\r\n GROUP BY item\r\n ORDER BY COUNT(*),item DESC\r\n \r\n \/* RIGHT returns the rightmost characters of a string as with: *\/\r\n SELECT RIGHT('Robyn Page',4)\r\n <\/pre>\nREPLICATE<\/h4>\n
SELECT '+'+REPLICATE('-',10)+'+'+CHAR(13)+CHAR(10)\r\n +REPLICATE('|'+REPLICATE(' ',10)+'|'+CHAR(13)+CHAR(10),8)\r\n +'+'+REPLICATE('-',10)+'+'+CHAR(13)+CHAR(10)\r\n <\/pre>\n +----------+\r\n | |\r\n | |\r\n | |\r\n | |\r\n | |\r\n | |\r\n | |\r\n | |\r\n +----------+\r\n <\/pre>\n
SPACE<\/h4>\n
SPACE(10)<\/code> (return a string consisting of ten spaces) is equivalent to REPLICATE(' ',10)<\/code>. The SPACE<\/code> function just returns a string with however many spaces you specify. It was more popular in the days of printed reports on fixed-width fonts where the results had to be printed in decimal point alignment, or right-aligned. eg:<\/p>\n SELECT SPACE(10-CHARINDEX('.',item+'.'))+item\r\n FROM dbo.uftSecondSplitVarcharToTable(\r\n '123.56,45.873,4.5,4.0,45768.9,354.67,12.0,66.97,45,4.5672',',')\r\n \/*-------------\r\n 123.56\r\n 45.873\r\n 4.5\r\n 4.0\r\n 45768.9\r\n 354.67\r\n 12.0\r\n 66.97\r\n 45\r\n 4.5672\r\n <\/pre>\nREVERSE<\/h4>\n
REVERSE<\/code> function, which merely returns the string backwards. Execute this to discover the message…<\/p>\n SELECT REPLACE(REVERSE(\r\n 'evil ot sah eh|hcihw ni|pmaws a ylno sa|nam a fo skniht|mreg a tub|\r\n nem ot elbanoitcejbo|yrev era smreg'),'|','\r\n ')\r\n <\/pre>\n
REVERSE <\/code>is occasionally very useful, and on those occasions nothing else will do. In this example, we find the last occurrence of a substring in a string and delete it.<\/p>\n SELECT \r\n REVERSE(STUFF(REVERSE(line),\r\n CHARINDEX(REVERSE('There be '),REVERSE(line))\r\n ,9,''))\r\n FROM \r\n (\r\n SELECT\r\n [line]='There be no truth in that there be and that is what I say'\r\n )f\r\n --which yields...\r\n --There be no truth in that and that is what I say\r\n <\/pre>\n SELECT RIGHT(URL, CHARINDEX('\/',REVERSE(URL) +'\/')-1)\r\n FROM \r\n (\r\n SELECT\r\n [URL]='http:\/\/www.simple-talk.com\/content\/article.aspx?article=495'\r\n )f\r\n <\/pre>\nChanging case: LOWER and UPPER<\/h4>\n
LOWER <\/code>and UPPER<\/code>, which are pretty self-explanatory:<\/p>\n <\/pre>\n
UPPER<\/code>:<\/p>\n CREATE FUNCTION [dbo].[ufsCapitalize] \r\n ( \r\n @string VARCHAR(8000)\r\n )\r\n RETURNS VARCHAR(8000)\r\n AS \r\n BEGIN\r\n \r\n DECLARE @Next INT\r\n WHILE 1=1\r\n BEGIN\r\n --find word space followed by lower case letter\r\n --This makes assumptions about the language\r\n SELECT @next= \r\n PATINDEX('%[^a-zA-Z][abcdefghijklmnopqurstuvwzyz]%',\r\n ' '+@string collate Latin1_General_CS_AI)\r\n IF @next =0 BREAK\r\n SELECT @String = \r\n STUFF(@String,@Next,1,UPPER(SUBSTRING(@String,@Next,1)))\r\n END\r\n RETURN @string\r\n END\r\n <\/pre>\n SELECT dbo.ufsCapitalize('leonard j poops jnr')\r\n \/*\r\n -- which results in...\r\n -- Leonard J Poops Jnr\r\n <\/pre>\nRemoving leading or trailing spaces RTRIM & LTRIM<\/h4>\n
SELECT LTRIM(' this has leading spaces, ')\r\n +RTRIM('this has trailing spaces ') \r\n --or both!\r\n SELECT '\"'\r\n +LTRIM(RTRIM(' This string has spaces fore and aft '))\r\n +'\"'\r\n <\/pre>\nFuzzy searches, SOUNDEX and DIFFERENCE<\/h4>\n
SOUNDEX<\/code> and DIFFERENCE<\/code><\/p>\n Select line FROM #poem WHERE DIFFERENCE(line,'I was')=4\r\n <\/pre>\n
Manipulating TEXT and NTEXT<\/h3>\n
TEXT<\/code> and NText<\/code> datatype, there are a only a few functions that will work with them. These are PATINDEX<\/code>, TEXTVALID<\/code>, SUBSTRING<\/code>, DATALENGTH<\/code> and TEXTPTR<\/code> As these are either covered elsewhere, or too esoteric to be within the scope of the workbench, I’d like to refer you to Books Online, which covers them very well.<\/p>\nSome questions<\/h3>\n
\n
\n