Basics of SQL Data Operations (Intermediate) 9

Convert data

SQL Sever is powerful enough to convert most values ​​from one type to another when needed. For example, to compare the sizes of SMALLINT and INT data, you do not need to perform explicit type conversions. SQL Sever will do the work for you. However, when you want to convert between character data and other types of data, you do need to perform the conversion yourself. For example, suppose you want to extract all values ​​from a MONEY field and add the string "US" after the result. Dollars”. You need to use the function CONVERT() as shown in the following example:

SELECT CONVERT(CHAR(8),PRice)+’US Dollars’ FROM orders

Function CONVERT() takes two variables. The first variable specifies the data type and length. The second variable specifies the field to be converted. In this example, the price field is converted into a CHAR field with a length of 8 characters. The field price must be converted into a character type before the string 'US can be connected after it. Dollars’.

When adding strings to BIT, DATETIME, INT, or NUMERIC fields, you need to perform the same conversion operation. For example, the following statement adds the string 'The vote is’, this SELECT statement 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 perform explicit conversion, you will receive the following error message:

Implicit conversion from datatype ‘varchar’ to ‘bit’ is not allowec.

Use the CONVERT function to run this query.

Manipulate string data

SQL Sever has many functions and expressions that allow you to perform interesting operations on 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.

Match wildcards

Suppose you want to build an Internet directory with similar functionality to Yahoo. You can create a table that holds a list of site names, uniform resource locators (URLs), descriptions, and categories and allow visitors to Enter keywords into the form to retrieve these contents.

Suppose a visitor wants to get information from this directory whose description contains the keyword trading A list of card sites. To pull out the correct list of sites, you might try using a query like this:

SELECT site_name FROM site_directory WHERE site_desc=’trading card’

This query will work. However, it can only return those whose description only has trading The site of the string card. For example, a description of We have the greatest collection of trading cards in the World!'s sites will not be returned.

To match a string with part of another string, you need to use wildcard characters. You use wildcards and the keyword LIKE to implement pattern matching. The following statement rewrites the above query using wildcards and the keyword LIKE to return the names of all 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. Described as We have the greatest Collection of trading cards in the world!'s site was also returned. Of course, if a site's description contains I am trading cardboard boxes online , the name of the site is also returned.

Note the use of the percent sign in this example. The percent sign is one example of a wildcard character. It represents 0 or more characters. By putting trading card is enclosed in percent signs, and all has the string trading embedded in it. All card strings are matched.

Now, let’s say your site directory becomes too large to fit entirely on one page. You decide to split the directory into two parts. On the first page, you want to display all sites whose initials start with A through M. On the second page, you want to display all sites starting with N through Z. To get the site list 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 to extract only those sites whose first letters are between A and M. Square brackets ([]) are used to match a single character within a specified range. To get the site displayed on the second page, this statement should be used:

SELECT site_name FROM site_directory

WHERE site_name LIKE ‘[N-Z]%’

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

Suppose your site directory has become larger and you now need to divide the directory into more pages. If you want to display those sites that start with A, B or C, you can do it with the following query:

SELECT site_name FROM site_directory WHERE site_name LIKE ‘[ABC]%’

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

You can combine these two methods by including both a range and some specified characters in a bracketed expression. For example, with the following query, you can retrieve sites whose first letters are 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 sites named Collegescape and Yahoo will be selected, but the site named Magicw3 will not be selected.

You can also use the caret (^) to exclude specific characters. For example, to get the sites whose names do not 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 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 every site whose name has any letter as the second character:

SELECT site_name FROM site_directory WHERE site_name LIKE ‘M_crosoft’

This example returns both a site named Microsoft and a site named Macrosoft. However, sites named Moocrosoft are not returned. Unlike the wildcard character '%', the underscore represents only a single character.

Note:

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

SELECT site_name FROM site_directory WHERE site_desc LIKE ‘%[%]%’

Match pronunciation

Microsoft SQL There are two functions that allow you to match strings phonetically. The function SOUNDEX() assigns a phonetic symbol code to a string, and the function DIFFERENCE() compares two strings according to their pronunciation. When you don't know the exact spelling of a name, but have some idea of ​​its pronunciation, using these two functions will help you retrieve the record.

For example, if you create an Internet directory, you might want to add an option that allows visitors to search for sites by the pronunciation of their 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 sites whose names sound very similar to Microsoft. Function DIFFERENCE() returns a number between 0 and 4. If the function returns 4, it means that the pronunciation is very similar; if the function returns 0, it means that the pronunciation of the two strings is very different.

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

To learn more about how the 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 in the field site_name and its phonetic symbol code. Here are the results 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

(12 row(s) affected)

If you look at the phonetic symbol code carefully, you will notice that the first letter of the phonetic symbol code is the same as the first letter of the field value. For example, the phonetic symbols of Yahoo and Mahoo differ only in the first letter. You can also find that the phonetic symbols of Microsoft and Macrosoft are exactly the same.

Function DIFFERENDE() compares the first letter and all consonants of two strings. This function ignores any vowels (including y) unless a vowel is the first letter of a string.

Unfortunately, there is a drawback to using SOUNDEX() and DIFFERENCE(). Queries containing these two functions in the WHERE clause do not perform well. Therefore, you should use these two functions with caution.

Remove Spaces

There are two functions, TTRIM() and LTRIM(), which can be used to trim spaces from strings. The function LTRIM() removes all spaces at the front of a string; the function RTRIM() removes all spaces at the end of a string. Here is an example of any use of function RTRIM():

SELECT RTRIM(site_name) FROM site_directory

In this example, if any site name has extra spaces at the end, the extra spaces will be removed from the query results.

You can nest these two functions to delete the spaces before and after a string at the same time:

SELECT LTRIM(RTRIM(site_name) FROM site_directory

You will find these two functions very useful when trimming extra spaces from CHAR fields. Remember, if you store a string in a CHAR field, the string will be appended with extra spaces to match the length of the field. Using these two functions, you can remove useless spaces and thus solve this problem.

The above is the content of SQL Data Operation Basics (Intermediate) 9. For more related articles, please pay attention to the PHP Chinese website (www.php.cn)!