SQL Data Operations Basics (intermediate) 9

Data Transformation Data

The SQL sever is strong enough to convert most of the values from one type to another when needed. For example, to compare the size of smallint and int data, you do not need an explicit type conversion. The SQL sever will do the work for you. However, when you want to convert between character data and other types of data, you really need to do the conversion. For example, suppose you want to take all the values out of a money field and add the string "US dollars" after the result. You need to use the function convert (), as shown in the following example:

SELECT CONVERT (CHAR (8), price) + ' US dollars ' from orders

The function convert () has two variables. The first variable specifies the data type and length. The second variable specifies the field to be converted. In this example, the field price is converted to a char type field of 8 characters in length. Field price is converted to a character type before it can be concatenated with the string ' US dollars '.

When you add a string to a bit, datetime, int, or numeric field, you need to do the same conversion. For example, the following statement adds the string ' The vote is ' in the query result of a SELECT statement, which returns the value of a bit field:

SELECT ' The vote is ' +convert (CHAR (1), vote) from opinion

The following is an example of the result of this statement:

The vote is 1

The vote is 1

The vote is 0

(3 row (s) affected)

If you do not make an explicit conversion, you receive an error message such as the following:

Implicit conversion from datatype ' varchar ' to ' bit ' are not ALLOWEC.

Use the CONVERT function to run this query.

Manipulating string data

SQL Sever has many functions and expressions that allow you to do interesting things with strings, including a variety of pattern matching and character conversions. In this section, you will learn how to use the most important character functions and expressions.

Matching wildcard characters

Suppose you want to build an Internet directory that is similar to the Yahoo feature. You can create a table to hold a series of site names, Uniform Resource Locator (URL), description, and category, and allow visitors to retrieve the content by entering keywords in the HTML form.

If a visitor wants to get a list of the sites in this directory that contain the keyword trading card in their description. To remove the correct list of sites, you may be trying to use a query like this:

SELECT site_name from Site_directory WHERE site_desc= ' trading card '

This query can work. However, it can only return those sites whose description has only the trading card string. For example, a site described as we have the greatest collection of trading cards in the world! will not be returned.

To match a string with a part of another string, you need to use a wildcard character. You use wildcard characters and keyword like to implement pattern matching. The following statement uses the wildcard character and the keyword like to rewrite the query above to return the names of all the correct sites:

SELECT site_name from Site_directory

WHERE site_desc like '%trading cark% '

In this example, all sites whose descriptions contain the expression trading card are returned. The site described as we have the greatest collection of trading cards in the world! is also returned. Of course, if the description of a site contains the I am trading cardboard boxes online, the name of the site is also returned.

Note the use of the percent semicolon in this example. A percent semicolon is one example of a wildcard character. It represents 0 or more characters. By enclosing the trading card in the percent sign, all strings embedded with the string trading card are matched.

Now, suppose your site directory becomes too large to be fully displayed on a single page. You decide to divide the catalogue into two parts. On the first page, you want to display all the initials between A and M sites. On the second page, you want to display all the first letters between N and Z in the site. To get a list of sites on the first page, you can use the following SQL statement:

SELECT site_name from Site_directory WHERE site_name like ' [a-m]% '

In this example, the expression [a-m] is used, and only the site with the first letter between A and M is removed. The brackets ([]) are used to match a single character in a specified range. To get the site displayed on the second page, use this statement:

SELECT site_name from Site_directory

WHERE site_name like ' [n-z]% '

In this example, the expression in parentheses represents any single character that is between N and Z.

Assuming your site directory becomes larger, you now need to divide the directory into more pages. If you want to show those sites that start with a,b or C, you can use the following query to implement:

SELECT site_name from Site_directory WHERE site_name like ' [abc]% '

In this example, the expression in parentheses no longer specifies a range, but gives some characters. Any site whose name begins with any of these characters will be returned.

You can combine the two methods by including both a range and some of the specified characters in the expression in parentheses. For example, with the following query, you can take out a site with the first letters between C and F, or start with the letter y:

SELECT site_name from Site_directory WHERE site_name like ' [c-fy]% '

In this example, the site named Collegescape and Yahoo will be selected, and the site named MAGICW3 will not be selected.

You can also use the caret (^) to exclude certain characters. For example, to get sites with names that don't start with Y, you can use the following query:

SELECT site_name from Site_directory WHERE site_name like ' [^y]% '

You can use the caret character for a given character or range of characters.

Finally, by using the underscore character (_), you can match any single character. For example, the following query returns each site with a second character whose first name is any letter:

SELECT site_name from Site_directory WHERE site_name like ' M_crosoft '

This example returns both the site named Microsoft and the site named Macrosoft. However, a site with the name Moocrosoft is not returned. Unlike wildcard '% ', underscores only represent a single character.

Attention:

If you want to match the percent or underscore character itself, you need to enclose them in square brackets. If you want to match a hyphen (-), you should specify it as the first character in square brackets. If you want to match the brackets, you should enclose them in square brackets as well. For example, the following statement returns all sites whose description contains a percent semicolon:

SELECT site_name from Site_directory WHERE site_desc like '%[%]% '

Match pronunciation

Microsoft SQL has two functions that allow you to match a string by pronunciation. function Soundex () assigns a phonetic code to a string, and the function difference () compares two strings by pronunciation. When you don't know the exact spelling of a name, but how much you know about its pronunciation, using these two functions will help you take out the record.

For example, if you create an Internet directory, you might want to add an option that allows visitors to search the site by the sound of the site name, rather than by the spelling of the name. Consider the following statement:

SELECT site_name from Site_directory

WHERE difference (site_name, ' Microsoft ' >3

This statement uses the function defference () to get its name pronounced in a site that is very similar to Microsoft. function difference () returns a number from 0 to 4. If the function returns 4, the pronunciation is very similar, and if the function returns 0, the pronunciation of the two strings varies widely.

For example, the above statement will return the site name Microsoft and Macrosoft. The pronunciation of these two names is similar to that of Microsoft. If you change the previous statement to greater than 3 to greater than 2, then sites named Zicrosoft and Megasoft will also be returned. Finally, if you only need a difference level greater than 1, then the site named Picosoft and Minisoft will also be matched.

To get an insight into how function difference () works, you can use the function soundex () to return the phonetic code used by the function difference (). Here's an example:

SELECT site_name ' site name ', SOUNDEX (site_name) ' sounds like '

This statement selects all the data for the field site_name and its phonetic code. The following is the result of this query:

Site name sounds like

.........................................................................

Yahoo Y000

Mahoo M000

Microsoft M262

Macrosoft M262

Minisoft M521

Microshoft M262

Zicrosoft Z262

Zaposoft Z121

Millisoft M421

Nanosoft N521

Megasoft M221

Picosoft P221

(s) affected)

If you look at the phonetic code carefully, you will notice that the first letter of the phonetic code is the same as the first letter of the field value. For example, the phonetic codes for Yahoo and Mahoo have only the first letter. You can also find that the phonetic codes for Microsoft and Macrosoft are identical.

The function Differende () compares the first letter of the two string and all the consonant letters. The function ignores any vowels (including y) unless a vowel letter is the first letter of a string.

Unfortunately, there is a deficiency in the use of Soundex () and difference (). The query containing the two functions in the WHERE clause does not work well. Therefore, you should use these two functions with care.

Remove spaces

There are two functions, Ttrim () and LTrim (), which can be used to cut spaces from the string. function LTrim () Removes all spaces that should precede the string; function RTrim () Removes all spaces from the tail of a string. Here is an example of any use of the function RTrim ():

SELECT RTRIM (site_name) from Site_directory

In this example, if there is an extra space in the end of the name of any one site, the extra space will be deleted from the query result.

You can use these two functions in a nested way by deleting the spaces before and after a string:

SELECT LTRIM (RTRIM (site_name) from Site_directory

You'll find that these two functions are useful when you cut out extra spaces from a char field. Remember, if you save a string in a Char field, the string is appended with extra space to match the length of the field. With these two functions, you can solve the problem by removing the unused spaces.