Working with Functions, Parameters, and Data Types

Understanding Literal Values

A literal value is a constant used to place values into table columns with statements such as:

 INSERT INTO employees (emp_ID, name, hire_date, hourly_rate)
 VALUES (1, 'Konrad King', '08/20/2000', 52.75)

(which tells the DBMS to insert a row with the literal values listed in the VALUES clause into the EMPLOYEES table) and in expressions such as:

 UPDATE employees SET hourly_rate = 10.0 + hourly_rate * 1.2

(which tells the DBMS to multiply the value in the HOURLY_RATE column by the numeric literal value "1.2", add the numeric literal value "10.0" to the product, and store the new value back in the HOURLY_RATE column).

Every DBMS product has a type of literal value that can be used in expressions and column value assignments for each of the standard SQL data types supported by the DBMS. Table 224.1 shows examples of literal values for some of the data types supported by MS-SQL Server.

Table 224.1: MS-SQL Server Data Types and Example Literal Values

Data Type

Example Literal Values

BINARY, VARBINARY, UNIQUEIDENTIFIER

Oxab, 0x451245, 0x2586AB

CHAR, VARCHAR, NCHAR, NTEXT, NVARCHAR, TEXT, SYSNAME

'Konrad King', 'Konrad's Tips', 'Hello'

DATETIME, SMALLDATETIME

'April 15, 2000 05:25pm', '08/20/2000'

DECIMAL, MONEY, SMALLMONEY

458.878, 789.5698, -58.78, -785.12

FLOAT, NUMERIC, REAL

258.5E10, -258.45E5, -237E-5, 78.59

INT, INTEGER, SMALLINT

-258, 32767, -15789, -987, 478

TINYINT

1, 0, 255, 200, 75

  Note 

Single quotes are used to denote non-numeric literal values such as character strings and dates. To use a character string literal value that itself contains a single quote mark, write two single quotes in a row. For example, the INSERT statement

INSERT INTO books (author, title)
 VALUES ('Konrad King', 'Konrad's Tips')

adds a row to the BOOKS table with "Konrad King" in the AUTHOR column and "Konrad's Tips" in the TITLE column.

Understanding Variables

A variable is a named memory location you can use to store a value temporarily. Unlike the constants used as literal values that you learned about in Tip 224, "Understanding Literal Values," the values in variables can change. MS-SQL Server supports two types of variables: local and global.

Local variables are created by a DECLARE statement and can be used only within the statement batch or stored procedure in which they are declared. Suppose, for example, that you wanted to display information about an INVENTORY table in a more readable format than that provide by a standard SELECT statement. You could execute the statement batch

 DECLARE @item_count INTEGER, @avg_value MONEY,
 @total_value MONEY

 SELECT @item_count = COUNT(*), @avg_value = AVG(cost),
 @total_value = SUM(cost) FROM inventory

 PRINT 'There are ' + RTRIM(CONVERT(CHAR(6), @item_count)) +
 ' items in inventory.'
 PRINT 'The total value of the inventory is $' +
 RTRIM(LTRIM(CONVERT(CHAR(8), @total_value))) + '.'
 PRINT 'Each item has an average value of $' +
 RTRIM(LTRIM(CONVERT(CHAR(8), @avg_value))) + '.'

which will display three lines of output similar to:

There are 16 items in inventory.
The total value of the inventory is $8020.62.
Each item has an average value of $501.29.

At this point, the important things to know are that:

  • You use the DECLARE statement to create a local variable.
  • Each local variable name must start with an at sign (@).
  • You use a SELECT statement to place a value into a local variable.

Global variables are useful when you need to use the same value in multiple stored procedures or in several SQL statement batches. Suppose, for example, that you run a store and want to base the sales price of your goods on your cost plus a fixed percentage based on the type of customer making a purchase. If you have a three-tier price schedule where your best customers pay 10 percent over your cost, the next best pay 20 percent, and the remainder pay 30 percent over cost, you could use the UPDATE statement

 UPDATE cost_sheet SET wholesale = cost * 1.1,
 discount = cost * 1.2, retail = cost * 1.3

to set your prices. Now, suppose it is holiday season or that new competition comes into town, and you want to reduce your margins by 5 percent to be more competitive. You could search your code and change all instances if * 1.1 to * 1.05, all instances of * 1.20 to * 1.15, and all instances of * 1.3 to * 1.25. However, the more places you use the same value and the more often you change your target profit margins, the more tedious and error-prone the process becomes.

A better alternative is to declare three global variables, such as @@wholesale_margin, @@discount_margin, and @@retail_rnargin. You then need only execute three SET statements, such as

 SET @@wholesale_margin = 1.05
 SET @@discount_margin = 1.15
 SET @@retail_margin = 1.25

to change the values in the three global variables. After you change the values in the global variables, the DBMS will automatically use the new values wherever you reference the variables in your code. As such, statements such as

 UPDATE cost_sheet SET wholesale = cost * @wholesale_margin,
 discount = cost * @@retail_margin,
 retail = cost * @@list_margin

can remain unchanged, no matter how often you change your price structure.

Understanding Parameters

Parameters are variables (named memory locations) you can use to pass values to and from stored procedures. By using parameters, you can create a general-purpose stored procedure and execute its statements using different values in clauses, without having to rewrite the procedure's SQL code.

Suppose, for example, that you have a stored procedure such as:

 CREATE PROCEDURE sp_call_history (@phone_number INTEGER,
 @start_date DATETIME, @stop_date DATETIME,
 @message VARCHAR(90) OUTPUT) AS

 IF @phone_number = 0 AND
 (@stop_date = '' OR @start_date = '')
 SET @message = 'Call this routine with either ' +
 '(, '' '', '' '' or '+
 '(0,,)'
 ELSE
 BEGIN
 IF @phone_number = 0
 SELECT * FROM oldcalls
 WHERE date_called >= @start_date
 AND date_called <= @stop_date
 ORDER BY date_called, call_time, hangup_time
 ELSE
 SELECT * FROM oldcalls
 WHERE phone_number = @phone_number
 ORDER BY date_called, call_time, hangup_time
 END 

To get a list of calls made to a particular phone number, execute a statement batch similar to:

 DECLARE @msg_ret VARCHAR(255)
 EXEC sp_call_history 3320144,'', '', @message=@msg_ret OUTPUT
 PRINT @msg_ret

which uses the @PHONE_NUMBER parameter to pass the phone number 3320144 and the @START_DATE and @STOP_DATE parameters to pass blank dates to the stored procedure. The stored procedure uses 3320144 in statement references to @PHONE_NUMBER and blank dates for @START_DATE and @STOP_DATE.

You can also use the same stored procedure to get a list of calls made to all phone numbers within a date range by executing a statement batch similar to

 DECLARE @msg_ret VARCHAR(255)
 EXEC sp_call_history 0, '08/01/2000','08/05/2000 ',
 @message=@msg_ret OUTPUT
 PRINT @msg_ret

which passes a zero (0) to the stored procedure through the @PHONE_NUMBER parameter and uses the @START_DATE and @STOP_DATE parameters to pass the first and last date in the date range. The stored procedure uses 08/01/2000 in statement references to @START_DATE, 08/05/2000 in statement references to @STOP_DATE, and zero (0) for @PHONE_NUMBER references.

If you supply neither a phone number nor a date range, the stored procedure uses the @MESSAGE parameter to put a message prompting you to specify one or the other when calling the procedure into the variable @MSG_RET. The DBMS uses the PRINT statement to display the value of the @MSG_RET parameter to the screen. Notice that the keyword OUTPUT must follow the name of the parameter (both in the stored procedure declaration and the EXEC statement that executes it) if the stored procedure is to use the parameter to pass a value back to the host (calling) program.

  Note 

The current example shows how to declare a stored procedure and how to pass values to and from the procedure using MS-SQL Server Transact-SQL. Each DBMS product will have its own methods for creating procedures, declaring variables, and passing parameter values to and from stored procedures. Check your system manual for the specific statement syntax for your DBMS. The important thing to understand now is that you can use parameters to pass values between the host program (such as MS-SQL Server Query Analyzer or an application written in a programming language such as C or Visual Basic) and the SQL statements in a stored procedure.

Understanding User Session and Date Time Functions and Variables

Each SQL Server either provides functions you can call or maintains several variables (registers on DB2) you can reference to determine user ID/session and date/time information. The SQL-92 specification codifies the requirement for the system to make the data available but (as with many things) leaves the specific mechanics of retrieving the information up to each DBMS vendor. As a result, each DBMS has its own way of letting you retrieve user, session, date, and time information.

Commonly implemented user/session functions and values include:

  • USER. (System-supplied value.) The username used to connect to the database. The username used to connect to the database may be different than the login name used to log in to the network fileserver.
  • USER_ID([]). Returns the INTEGER database user identification number for the CHARACTER string username (value from USER or returned by USER_NAME()) passed to the function as a parameter. If called without a parameter, the USER_ID() function returns the database identification number of the current user.
  • USER_NAME([]). Returns the CHARACTER string username for the INTEGER database identification number passed as a parameter. When called without a parameter, the USER_NAME() function returns the CHARACTER string username of the current user-the same as that maintained in the USER variable.
  • CURRENT_USER. The CHARACTER string username of the current user. (Equivalent to USER_NAME().)
  • SUSER_ID([]). Returns the INTEGER login ID number for the CHARACTER string login (vs. user) name passed to the function as a parameter. If called without a parameter, the SUSER_ID() function returns the login ID number of the current user.
  • SUSER_NAME([]). Returns the CHARACTER string login name for the INTEGER login ID number passed as a parameter. When called without a parameter, the SUSER_NAME() function turns the login name for the current user.
  • SUSER_SID([]). Returns the BINARY security identification number (SID) for the CHARACTER string login name passed as a parameter. When called without a parameter, the SUSER_ID() function returns the SID current user. (The SID is not the binary representation of the login ID number.)
  • SUSER_SNAME([]). Returns the CHARACTER string login name for the BINARY SID number passed as a parameter. When called without a parameter, the SUSER_SNAME() function returns the login name of the current user.
  • SESSION_USER. Niladic function that returns the current session's username as a value of type NCHAR. You can use the value returned by the SESSION_USER function as a column's DEFAULT constraint, or as an item in a SELECT clause, WHERE clause, or PRINT statement, and so on.
  • SYSTEM_USER. Niladic function that returns the login name (vs. the username found in SESSION_USER) as a value of type CHAR. You can use the value returned by the SYSTEM_USER function as a column's DEFAULT constraint, or as an item in a SELECT clause, WHERE clause, or PRINT statement, and so on.
  • APP_NAME(). Returns the NVARCHAR(128) string application name if one was set by the current session.

Commonly implemented date/time functions and values include:

  • CURRENT_DATE. (SQL) System-maintained variable with the value of the date on which the current statement was executed. Implemented as the CURRENT DATE register, which holds a DATE data type value on IBM DB2.
  • GETDATE(). Function that returns the current date and time in DATETIME format on MS-SQL Server.
  • CURRENT_TIME. (SQL) System-maintained variable that contains the time of day at which the current SQL statement was executed. Implemented as the CURRENT TIME register, which holds a TIME data type value on IBM DB2.
  • CURRENT_TIMESTAMP. Niladic function that returns the current date and time as a DATETIME data type value. Implemented as the CURRENT TIMESTAMP register with a TIMESTAMP data type on IBM DB2.
  • CURRENT TIMEZONE. Register value of data type DECIMAL(6,0) on IBM DB2, which contains the difference between Coordinated Universal Time (also known as Greenwich Mean Time [GMT]) and the local time on the server. The difference is expressed as a signed six-digit number with the first two digits signifying hours, the next two signifying minutes, and the final two digits signifying seconds.

Please check your system manual for the specific calling syntax and usage restrictions for user/session and date/time functions and values on your DBMS implementation.

Understanding Column References

In an SQL database, the data values are stored in table columns, with each column holding one value in each row of a table. To use a column's values in SQL statements, simply type the column names whose values you want the DBMS to use.

If an SQL statement references a single table in its FROM clause, such as

 SELECT employee_ID, first_name, last_name, office_ID
 FROM employees WHERE office_ID = 1

you need only use the column names. The DBMS will assume that the column names in the statement refer to the values in columns from the table in the FROM clause-even if more than one table in the DBMS has columns with the same names. As such, in the current example, the DBMS will display the values in the EMPLOYEE_ID, FIRST_NAME, LAST_NAME, and OFFICE number columns from the rows in the EMPLOYEES table in which the value in the OFFICE column is 1.

If you are working with a single table, you can also use fully qualified column references that consist of the table name a period (.) and the name of the column. For example, the SELECT statement

 SELECT employees.employee_ID, employees.first_name,
 employees.last_name, employees.office_ID
 FROM employees WHERE employees.office_ID = 1

is equivalent to the previous example SELECT statement using only (unqualified) column names.

When combining data from two or more tables, use fully qualified column names to tell the DBMS which values to use from which table. For example, the SELECT statement

 SELECT employees.employee_ID, employees.first_name,
 employees.last_name, employees.manager,
 offices.office_ID, offices.location, offices.manager,
 customers.customer_ID, customers, company_name
 FROM customers, employees, offices
 WHERE employees.office_ID = 1
 AND (employees.office_ID = offices.office_ID)
 AND (customers.employee_ID = employees.employee_ID

combines data from the CUSTOMERS, EMPLOYEES, and OFFICES tables. Although the SELECT statement in the example makes it easy for a person reading the statement to see which column values are coming from which tables, the DBMS really requires only that you use fully qualified names for columns whose names appear in more than one of the tables in the statement's FROM clause. In the current example, the SELECT statement

 SELECT employees.employee_ID, first_name, last_name,
 employees.manager, offices.office_ID, location,
 offices.manager, customer_ID, company_name
 FROM customers, employees, offices
 WHERE employees.office_ID = 1
 AND (employees.office_ID = offices.office_ID)
 AND (customers.employee_ID = employees.employee_ID

will generate the same results table as the previous SELECT statement. Only the EMPLOYEE_ID, MANAGER, OFFICE_ID, and EMPLOYEE_ID columns need fully qualified column references because the remainder of the columns appear in only one of the tables listed in the statement's FROM clause and the DBMS.

Using the SUBSTRING Function to Retrieve a Portion of a String

Use the SUBSTRING function when you need to extract a portion of the characters from a binary string, a character string value in a table column, or a character string expression. For example, to extract characters 7-12 from a character string, use the SUBSTRING function syntax

 SUBSTRING(, , )

in a SELECT statement such as:

 SELECT SUBSTRING ('King, Konrad',7, 6)

which will display six characters from the string literal "King, Konrad" starting at character 7:

 ------
 Konrad

Although extracting a portion of a string literal (such as "King, Konrad," in the current example), makes for an easy example, the real power of the SUBSTRING function becomes evident when you use it to extract a portion of a string found in a table column and use the resulting string as a conditional in a WHERE clause. Suppose, for example, that you want a list of all employees with last names starting with KIN; you could produce the list using the SUBSTRING function in the WHERE clause of a SELECT statement such as:

 SELECT last_name, SUBSTRING(last_name,1,3) AS 'First_Three',
 SUBSTRING(last_name, 4, 15) AS 'Remainder'
 FROM employees WHERE SUBSTRING(last_name,1,3) = 'KIN'

If you do not know the actual length of a string, simply specify the maximum number of characters you want the SUBSTRING function to return. When the SUBSTRING function finds that the has less than characters from the character to the end of the string, the function simply returns the characters from to the end of the string (without reporting an error or warning). As such, the SELECT statement in the current example will produce the results table

 Last_name First Three Remainder
 --------- ----------- ---------
 King Kin g
 Kingsly Kin gsly
 Kingston Kin gston

if the EMPLOYEES table has three employee with last names KING, KINGSLY, and KINGSTON-even though the longest last name has only eight characters and the SUBSTRING parameters specify that the function return characters 4-18.

  Note 

If the is NULL, the SUBSTRING function will return a NULL value. Similarly, if you specify a character that is higher than the length of the , some DBMS products will return a NULL result. Others, such as MS-SQL Server, will return an empty string (a string of length 0).

Using the UPPER Function to Convert All of a String s Characters to Uppercase

The UPPER function converts the lowercase letters in a string to uppercase letters. For example, the SELECT statement

 SELECT UPPER('konrad '+' king''s'),
 UPPER(SUBSTRING('1001 SQL Tips Book',1,14))

will display the output:

 ------------- ------------
 KONRAD KING'S 1001 SQL TIPS

Notice that the UPPER function has no effect on any symbols, numbers, and uppercase letters in the target character string.

In addition to string literal expressions and function results, you can use the UPPER function to convert lowercase letters to upper case in table columns of data type CHARACTER (or VARCHAR) with statements such as

 SELECT UPPER(first_name), UPPER(last_name),
 UPPER(first_name+' '+last_name), first_name, last_name
 FROM employees

which will display

 first_name last_name
 ------ ---- ----------- ---------- ---------
 KONRAD KING KONRAD KING konrad king

if the EMPLOYEES table has a single row with konrad in the FIRST_NAME column and king in the LAST_NAME column.

As was the case with the SUBSTRING function, the UPPER function returns an object of data type character. As such, you can use the UPPER function not only in SELECT statements to display uppercase strings, but also in UPDATE statements such as

 UPDATE employees SET first_name = UPPER(first_name),
 last_name = UPPER(last_name)

which converts all FIRST_NAME and LAST_NAME column values in the EMPLOYEES table to upper case.

Using the DISTINCT Clause to Eliminate Duplicates from a Set of Rows

Individual columns not constrained by a PRIMARY KEY or UNIQUE constraint can have duplicate values in two or more rows in a table. By adding the DISTINCT clause to a query, you can tell the DBMS to eliminate duplicate rows from the SELECT statement's results table. Suppose, for example, that you want to know how many customers placed orders during the past 90 days. Executing the query

 SELECT COUNT(customer_ID) FROM orders
 WHERE order_date >= GETDATE() - 90

will give you an incorrect customer count if any customers placed more than one order during the period. However, if you add the DISTINCT clause to the same query

 SELECT COUNT(DISTINCT customer_ID) FROM orders
 WHERE order_date >= GETDATE() - 90

the DBMS will eliminate duplicate customer_IDs from the results table and give you an accurate count of unique customer_IDs (and, therefore, customers) that placed orders during the past 90 days.

In addition to COUNT(), you can use the DISTINCT clause with the SUM, AVG, and COUNT(*) aggregate functions. For example, given the ITEM_COUNT values

 item_count
 ----------
 4
 5
 6
 4
 4
 5

executing the query shown in the top pane of Figure 231.1 will produce the results table shown in the figure's bottom pane.

click to expand
Figure 231.1: The MS-SQL Server Query Analyzer window showing a query using DISTINCT clauses in the upper (input) pane and the query's results table in the lower (output) pane

Notice that the DISTINCT COUNT(*) function produces what may at first appear to be an incorrect result. After all, there are only three unique ITEM_COUNT values (4, 5, 6), yet the DISTINCT COUNT(*) function returns a value of 6.

The reason for the disparity is that when you precede a column name list with a DISTINCT clause, the DBMS will eliminate only rows with duplicate values in the combined columns. For example, executing the query

 SELECT DISTINCT customer_ID, item_count FROM orders
 WHERE order_date >= '01/01/2000'
 AND order_date <= '01/31/2000'

can produce a results table similar to:

 customer_ID item_count
 ----------- ----------
 101 4
 102 4
 102 5
 102 6
 103 5

Although each of the two output columns (CUSTOMER_ID and ITEM_COUNT) contains duplicate values, the results table has no duplicate values for the combination of both columns (CUSTOMER_ID,ITEM_COUNT). Similarly, in the previous example, the DBMS expanded the COUNT(*) to mean "all columns." As such, the DISTINCT clause in DISTINCT COUNT(*) will eliminate only rows in which all columns values in one row are exact duplicates of all column values in another row.

Using the LOWER Function to Convert All of a String s Characters to Lowercase

In Tip 230, "Using the UPPER Function to Convert All of a String's Characters to Uppercase," you learned how to use the UPPER function to convert all lowercase letters in a character string to upper case. The LOWER function is the converse of the UPPER function in that the LOWER function converts all of the uppercase letters in a character string to lower case. For example, the SELECT statement

 SELECT LOWER('KONRAD '+'KING''S'),
 LOWER(SUBSTRING('601 SQL Tips Book',1,14))

will display the output:

 ------------- -------------
 konrad king's 601 sql tips

Notice that the LOWER function has no effect on any symbols, numbers, and lowercase letters in the target character string.

In addition to character string literal expressions and string function results, you can use the LOWER function to convert uppercase letters to lower case in table columns of data type CHARACTER (or VARCHAR). For example, the SELECT statement

 SELECT LOWER(first_name), LOWER(last_name),
 LOWER(first_name+' ' +last_name), first_name, last_name
 FROM employees

will display the results table

 first_name last_name
------ ---- ----------- ---------- ---------
konrad king konrad king KONRAD KING

if the EMPLOYEES table has a single row with KONRAD in the FIRST_NAME column and KING in the LAST_NAME column.

As was the case with the SUBSTRING and UPPER functions, the LOWER function returns an object of data type CHARACTER when passed a target string of data type CHARACTER (or VARCHAR when the target string is of data type VARCHAR). As such, you can use the LOWER function not only in SELECT statements to display lowercase strings, but also in UPDATE statements such as

 UPDATE employees SET first_name = LOWER(first_name),
 last_name = LOWER(last_name)

which changes the values in the FIRST_NAME and LAST_NAME columns of rows in the EMPLOYEES table to lower case.

Using the TRIM Function to Remove Leading and Trailing Blanks

The TRIM function lets you remove leading or trailing blanks (or other characters) from a character string. Table 233.1 shows the results of using the TRIM function on various example character literals.

Table 233.1: Example TRIM Function Calls and Results

TRIM Function Call

Returns

TRIM (LEADING 'SQL Tips')

'SQL Tips'

TRIM (TRAILING 'SQL Tips')

'SQL Tips'

TRIM (BOTH 'SQL Tips')

'SQL Tips'

TRIM ('SQL Tips')

" (empty string)

TRIM (BOTH 'S' FROM 'SQL Tips')

'QL Tips'

TRIM (LEADING 'S' FROM 'SSQL TIPS'

'SQL TIPS'

TRIM (BOTH 'S' FROM 'SSQL TIPS'

'SQL TIP'

As is the case with the SUBSTRING, UPPER, and LOWER functions (which you learned about in Tip 229, "Using the SUBSTRING Function to Retrieve a Portion of a String"; Tip 230, "Using the UPPER Function to Convert All of a String's Characters to Uppercase"; and Tip 232, "Using the LOWER Function to Convert All of a String's Characters to Lowercase," respectively), you can use CHARACTER (and VARCHAR) data type column references as the target string parameter for the TRIM function. For example, the SELECT statement

 SELECT '>' + TRIM(last_name) + ', ' + TRIM(first_name)
 FROM employees

will display the results table

 ------------
 KING, KONRAD

if the EMPLOYEES table has a single row with KONRAD in the FIRST_NAME column and KING in the LAST_NAME column.

By the way, the calls to the TRIM function in the current example show the most abbreviated form of the TRIM function call syntax:

 TRIM ([LEADING|TRAILING | BOTH] [] [FROM]
 )

When you omit the , the TRIM function assumes you want to remove spaces. Moreover, if you do not specify LEADING, TRAILING, or BOTH, the function defaults to BOTH. As such, in the current example, the TRIM function calls in the form of TRIM() tell the DBMS to remove BOTH leading and trailing spaces (blanks) on either side of the .

  Note 

Some DBMS products, such as MS-SQL Server, implement the RTRIM and LTRIM functions instead of the TRIM function. Table 233.2 shows the RTRIM and LTRIM function call equivalents of the TRIM function used to remove leading, trailing, and both leading and trailing spaces.

Table 233.2: The TRIM Function Call Syntax and Equivalent LTRIM and RTRIM Function Call Syntax

TRIM Function Call

RTRIM or LTRIM Function Call

TRIM (LEADING )

LTRIM ()

TRIM (TRAILING )

RTRIM ()

TRIM (BOTH )

LTRIM(RTRIM( ) )

Unlike the TRIM function, the syntax of the LTRIM and RTRIM function calls does not include a parameter. As such, you can use only LTRIM and RTRIM to strip blanks from the (while the TRIM function lets you specify the character to remove).

Using the Transact SQL STUFF Function to Insert One Character String into Another

MS-SQL Server lets you use the STUFF function to insert one character string into another. The syntax of the STUFF function call is:

 STUFF(, ,
 , )

As such, the PRINT statement

 PRINT STUFF('**Konrad King''s Tips**',16,0,' 1001 SQL')

will display the results

 **Konrad King's 1001 SQL Tips**

after the STUFF function inserts ("1001 SQL") into ("**Konrad King's Tips**") after the 16th character in .

If you want MS-SQL Server to remove a portion of before inserting , set the to something other than 0. For example, the PRINT statement

 PRINT STUFF('**Konrad King '' s Tips**',9,7, '' ' S 1001 SQL')

will display the results

 **Konrad's 1001 SQL Tips**

after the STUFF function removes seven characters from starting with character 9, (the space after "**Konrad") and then inserts in at the same character position (7).

  Note 

If the or is negative, or if the is a number larger than the length of , the STUFF function will return a NULL result.

As is the case with the other character string functions you learned about in Tips 229-230 and 232-233, you can use variables and columns of data type CHARACTER (or VARCHAR) as parameters to the STUFF function. For example, to display the first letter of the FIRST_NAME followed by the LAST_NAME you can use a SELECT statement similar to

 SELECT STUFF(first_name,2,40,'. ' + last_name)
 AS 'First Initial & Last Name' FROM employees

to produce output such as:

 First Initial & Last Name
 -------------------------
 K. KING
 S. FIELDS
 D. JAMSA
  Note 

If you specify a that is longer than the number of characters from to the end of , the STUFF function will not report an error or warning. Instead, the function will simply truncate with the character at , as illustrated in the current example.

Using the Transact SQL Concatenation Operator + to Add One String to the End of Another String

MS-SQL Server lets you use the plus (+) operator to append the results of one character or binary string expression onto the results of another character or binary string expression using the syntax:

  + 
 [...+ ]

For example, the PRINT statement

 SELECT 'He' + 'llo' + ' ' + world!'

will display the results table:

 ------------
 Hello world!

As usual, the real value of the concatenation operator is its ability to concatenate string literals, other character string function results, and CHARACTER (and VARCHAR) data type column values. For example, the SELECT statement

 SELECT first_name + ' ' + last_name + ' wrote: "' + title +
 '" for ' + publisher + ' circa ' +
 CONVERT(VARCHAR(11),publish_date) + '.'
 FROM authors, titles
 WHERE author_ID = author
 ORDER BY last_name, first_name

can be used to combine literal strings and column values from the AUTHORS and TITLES tables to form English sentences such as those in the results table:

 -----------------------------------------------------------
 Kris Jamsa wrote: "Java Programmer's Library" for Jamsa
 Press circa Jun 1 1996.
 Kris Jamsa wrote: "1001 Windows 98 Tips" for Jamsa Press
 circa Jun 1 1998.
 Konrad King wrote: "SQL Tips & Techniques" for Prima
 Publishing circa Feb 1 2002.
 Konrad King wrote: "Hands on PowerPoint 2000" for Jamsa
 Press circa Aug 1 1999.

Understanding the Transact SQL CONVERT Function

SQL requires that all operands in each expression and data values in each table column be of the same data type. You may not have noticed this restriction, however, because many DBMS products, like MS-SQL Server, perform most data type conversions automatically. On MS-SQL Server, for example, you can place a character string such as "'-123'" into an INTEGER or NUMERIC column because the system automatically changes the string literal into the equivalent INTEGER or NUMERIC value before inserting it into the table. (The only restriction is that the string literal must consist entirely of numbers, and optionally, a single decimal point and a minus sign (-). Similarly, MS-SQL Server lets you compare a CHARACTER string data type object or expression with a DATETIME data type object or expression without first performing a data type conversion.

For those instances in which MS-SQL Server will not perform an automatic data type conversion, you can use the Transact-SQL CONVERT function. You can also use the CONVERT function to specify the format of DATETIME, FLOATING and REAL, and MONEY and SMALLMONEY data type values when you want any of them displayed or stored in CHARACTER (or VARCHAR) columns in other than the default format.

The syntax of the CONVERT function call is

 CONVERT ( [()], 
 [,


SQL Tips & Techniques
SQL Tips & Techniques (Miscellaneous)
ISBN: 1931841454
EAN: 2147483647
Year: 2000
Pages: 632

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net