SQL Server 2005 allows to concatenate two binary strings into a single string with the (+) operator. The following tutorial exercise shows you some binary string concatenation examples:
-- Concatenating two binary string literals
SELECT 0x57656C636F6D6520746F20
+ 0x46594963656E7465722E636F6D;
GO
0x57656C636F6D6520746F2046594963656E7465722E636F6D
-- Watch out: This is not a binary string concatenation
SELECT '0x57656C636F6D6520746F20'
+ '0x46594963656E7465722E636F6D';
GO
0x57656C636F6D6520746F200x46594963656E7465722E636F6D
-- Concatenating two binary strings
SELECT CONVERT(VARBINARY(40),'Welcome to ')
+ CONVERT(VARBINARY(40),'GlobalGuideLine.com');
GO
0x57656C636F6D6520746F2046594963656E7465722E636F6D
-- Binary strings can not be concatenated
with character strings
SELECT 'Welcome to '
+ 0x46594963656E7465722E636F6D;
GO
Msg 402, Level 16, State 1, Line 1
The data types varchar and varbinary are incompatible
in the add operator.
2-How To Locate and Take Substrings with CHARINDEX() and SUBSTRING() Functions?
Transact-SQL is not a language designed for manipulating strings, but it does have two simple functions to locate and take substrings: CHARINDEX() and SUBSTRING(). The tutorial exercise below assumes two given strings: 'Pages: 18' and 'Words: 3240'. The objective is to calculate the number of words per page. Read the script below to see how this is done by using CHARINDEX() and SUBSTRING() functions:
DECLARE @sPages VARCHAR(40), @sWords VARCHAR(40);
SET @sPages = 'Pages: 18';
SET @sWords = 'Words: 3240';
SET @sPages = SUBSTRING(@sPages, CHARINDEX(':', @sPages)+1, 20);
SET @sWords = SUBSTRING(@sWords, CHARINDEX(':', @sWords)+1, 20);
PRINT 'Number of words per page: '
+ CONVERT(VARCHAR(20), CONVERT(INT, @sWords)/CONVERT(INT, @sPages));
GO
Number of words per page: 180
If you are a PHP developer, you can get this done in a much quick way.
3-
| How To Insert New Line Characters into Strings? | |
|
If you want to break a string into multiple lines, you need to insert new line characters into the string. With some client tools like SQL Server Management Studio, it is not so easy to insert a new line character. One work around is to use the CHAR(int) function to generated new line character and other special characters with their code values:
* CHAR(9) - Generates the tab character.
* CHAR(10) - Generates the line feed (new line) character.
* CHAR(13) - Generates the carriage return character.
The tutorial examples below gives you a good example
PRINT 'Welcome to '+CHAR(10)+'GlobalGuideLine.com';
PRINT CHAR(10);
PRINT 'Current date and time is '
+CONVERT(VARCHAR(20), GETDATE());
GO
4-
| What Are the Character String Functions Supported by SQL Server 2005? | |
|
SQL Server 2005 supports 23 character string functions:
* ASCII(char) - Returning the code value of a non-Unicode character.
* CHAR(int) - Returning the non-Unicode character of a code value.
* CHARINDEX(word, string, start_location) - Returning the location of the searched "word" in a string.
* DIFFERENCE(string1, string2) - Returning an integer value that indicates the difference between the SOUNDEX values of two strings.
* LEFT(string, length) - Returning a substring of "length" character from the left hand side.
* LEN(string) - Returning the number of characters in the string.
* LOWER(string) - Returning the same string with all upper case characters converted to lower case.
* LTRIM(string) - Returning the same string with leading spaces removed.
* NCHAR(int) - Returning the Unicode character of a code value.
* PATINDEX(pattern, string) - Returning the location of the "pattern" in a string.
* QUOTENAME(string, quote) - Returning the same string enclosed in "quote".
* REPLICATE(string, number) - Returning the same string repeated "number" of times.
* REVERSE(string) - Returning the same string with all character positions reversed.
* RIGHT(string, length) - Returning a substring of "length" characters from the right hand side.
* RTRIM(string) - Returning the same string with trailing spaces removed.
* SOUNDEX(string) - Returning a four-character (SOUNDEX) code to evaluate the similarity of two strings.
* SPACE(length) - Returning a string of "length" space characters.
* STR(number, precision, scale) - Returning a string representation of a numeric value.
* STUFF(string, start, length, new) - Returning the same string with one part being replaced.
* SUBSTRING(string, start, length) - Returning a substring.
* UNICODE(nchar) - Returning the code value of a Unicode character.
* UPPER(string) - Returning the same string with all lower case characters converted to upper case.
5-
| How To Convert a Unicode Strings to Non-Unicode Strings? | |
|
Since Unicode character set is different than code page based (non-Unicode) character set, converting Unicode strings to non-Unicode strings may result in wrong characters or missing characters. So you should avoid converting Unicode strings to non-Unicode strings. If you really want to, there are 3 ways to convert a Unicode string to a non-Unicode string:
* Implicit conversion by assignment operations - When a Unicode string is assigned to a variable, a column, or a parameter of a non-Unicode string data type, SQL Server will implicitly convert the Unicode string to a non-Unicode string.
* Explicit conversion using the CAST() function - A Unicode string can be explicitly converted to non-Unicode string using the CAST(Unicode_string AS VARCHAR(size)) function.
* Explicit conversion using the CONVERT() function - A Unicode string can be explicitly converted to non-Unicode string using the CONVERT(VARCHAR(size), Unicdoe_string) function.
Some numeric data type conversion examples are provided in the tutorial exercise below:
-- Implicit conversion by an assignment operation
DECLARE @regcode VARCHAR(40);
SET @regcode = N'Some Unicode characters: '
+ NCHAR(9733)+NCHAR(9734)+NCHAR(9792)+NCHAR(9794);
SELECT @regcode;
GO
Some Unicode characters: ????
-- Explicit conversion by CAST()
SELECT CAST(N'Some Unicode characters: '
+ NCHAR(9733)+NCHAR(9734)+NCHAR(9792)+NCHAR(9794)
AS VARCHAR(40));
GO
Some Unicode characters: ????
-- Explicit conversion by CONVERT()
SELECT CONVERT(VARCHAR(40), N'Some Unicode characters: '
+ NCHAR(9733)+NCHAR(9734)+NCHAR(9792)+NCHAR(9794));
GO
Some Unicode characters: ????
6-
| What Happens When Unicode Strings Concatenate with Non-Unicode Strings? | |
|
If a Unicode string NVARCHAR is concatenated with a non-Unicode string VARCHAR, SQL Server will implicitly convert the non-Unicode string to Unicode string for concatenation.
DECLARE @regcode VARCHAR(40);
DECLARE @unicode NVARCHAR(40);
SET @regcode = 'Some Unicode characters: '
SET @unicode = NCHAR(9733)+NCHAR(9734)+NCHAR(9792)
+NCHAR(9794);
SELECT @regcode + @unicode;
SELECT DATALENGTH(@regcode);
SELECT DATALENGTH(@unicode);
SELECT DATALENGTH(@regcode + @unicode);
Some Unicode characters: ????
25
8
58
Note that the non-Unicode string @regcode has been converted to a Unicode string. The number of bytes of @regcode changed from 25 to 50. With 8 bytes from @unicode, the number of bytes of the concatenated string becomes 58.
7-
| How To Concatenate Two Character Strings Together? | |
|
Concatenating two character strings together is most commonly used string operation. SQL Server 2005 allows to concatenate two character strings into a single string with the (+) operator. The following tutorial exercise shows you some string concatenation examples:
DECLARE @site VARCHAR(40);
SET @site = 'GlobalGuideLine.com';
SELECT 'Welcome to '+@site;
SELECT 'Current date and time is '
+CONVERT(VARCHAR(20), GETDATE());
GO
Welcome to GlobalGuideLine.com
Current date and time is May 19 2007 5:18PM
DECLARE @start INT, @end INT, @total INT;
SET @start = 21;
SET @end = 30;
SET @total = 728;
SELECT 'Search result '
+ CONVERT(VARCHAR(20),@start)
+ ' - '
+ CONVERT(VARCHAR(20),@end)
+ ' of '
+ CONVERT(VARCHAR(20),@total);
GO
Search result 21 - 30 of 728
8-
| How To Generate Random Numbers with the RAND() Function in MS SQL Server? | |
|
Random numbers are very useful for generating test data, passwords, or other security related data. SQL Server 2005 offers you the random number generator function RAND in two format:
* RAND(seed) - Starting a new sequence of random numbers based on the given integer "seed" and returning the first random number in FLOAT(53) from the sequence.
* RAND() - Returning the next random number in FLOAT(53) from the current sequence. If there has been no current sequence, SQL Server will start a new sequence with a random "seed".
Note that calling RAND(seed) with the same seed will start the same sequence and return the same number. To avoid this repeating pattern, you should always call RAND() without any seed and let the server to randomly pickup a sequence. The tutorial exercise below shows some good examples on how to generate random numbers:
SELECT RAND(100), RAND(), RAND(); -- new sequence
SELECT RAND(100), RAND(), RAND(); -- same sequence again
SELECT RAND(), RAND(), RAND();
SELECT RAND(), RAND(), RAND();
GO
0.715436657367485 0.28463380767982 0.0131039082850364
0.715436657367485 0.28463380767982 0.0131039082850364
0.28769876521071 0.100505471175005 0.292787286982702
0.868829058415689 0.370366365964781 0.58334760467751
-- Random integer between 0 and 100
SELECT FLOOR(100*RAND());
SELECT FLOOR(100*RAND());
SELECT FLOOR(100*RAND());
SELECT FLOOR(100*RAND());
GO
68
29
20
82 |
9- |
|
|
|
|
| How To Round a Numeric Value To a Specific Precision? | |
|
Sometimes you need to round a numeric value to a specific precision. For example, you may want to round values in your financial statement to the precision of 1000.00. This can be done by the ROUND() function with the following syntax:
ROUND(value, precision, type)
value: The input value to be rounded.
precision: The location of the precision digit relative
to the decimal point.
type: 0 - Round to nearest value;
1 - Truncate to a lower value.
The tutorial exercise below gives some good examples of how to use the ROUND() function:
SELECT ROUND(1234.5678, 0, 0);
SELECT ROUND(1234.5678, -3, 0);
SELECT ROUND(1234.5678, -4, 0);
SELECT ROUND(1234.5678, 3, 0);
SELECT ROUND(1234.5678, 3, 1);
GO
1235.0000
1000.0000
0.0000
1234.5680
1234.5670
10-
| How To Convert Numeric Values to Integers in MS SQL Server? | |
|
Sometimes you need to round a numeric value into an integer. SQL Server 2005 offers you a number of ways to do this:
* FLOOR(value) - Returning the largest integer less than or equal to the input value. The returning data type is the same as the input value.
* CEILLING(value) - Returning the smallest integer greater than or equal to the input value. The returning data type is the same as the input value.
* ROUND(value, 0, 0) - Returning the integer most close to the input value. The returning data type is the same as the input value.
* CAST(value AS INT) - Returning the largest integer less than or equal to the input value. The returning data type is INT.
* CONVERT(INT, value) - Returning the largest integer less than or equal to the input value. The returning data type is INT.
The tutorial exercise below gives some good examples of converting numeric values to integers:
SELECT FLOOR(1234.5678);
SELECT CEILING(1234.5678);
SELECT ROUND(1234.5678, 0, 0);
SELECT CAST(1234.5678 AS INT);
SELECT CONVERT(INT, 1234.5678);
GO
1234
1235
1235.0000
1234
1234
|
|
|
No comments:
Post a Comment