John F. Miner III – Page 13 – Ramblings of a Crafty DBA

String Functions – FORMAT()

April 8, 2013October 11, 2017 admin

I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the FORMAT() function today. The FORMAT() function takes a value [V], a format string [F], and a optional culture specification [C] as input parameters. It returns a locale-aware formatted string. For general conversions, consider using the CAST or CONVERT functions. I personally think this string function was a valuable addition to SQL Server 2012. Please refer to the .NET Framework 4.5 formatting types for both standard and custom format…

String Functions – DIFFERENCE()

April 7, 2013October 11, 2017 admin

I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the DIFFERENCE() function today. The DIFFERENCE() function takes two string arguments and computes how different the two strings sound when spoken. A return value of 4 means the strings sound the same while a value of 0 means the strings sound totally different. When using the difference function, you should look at the SOUNDEX() function. This function calculates a four-character code that is based on how the string sounds…

String Function – CONCAT()

April 6, 2013October 11, 2017 admin

I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the CONCAT() function today. The CONCAT() function takes two or more string arguments and combines them into one string. Any arguements that are not strings are converted to a string. All NULL values are converted to the empty string ‘ ‘. The following example takes seven input arguements of various data types and returns one string output.

-- Local variable
declare @var_tmp varchar(32);

-- Combine strings into one
select @var_tmp = concat('Fi', 've', ' plus', NULL, ' four equals nine (', 9, ').');

-- Show the resulting string
select @var_tmp as one_string;

-- Local variable

declare @var_tmp varchar(32);

-- Combine strings into one

select @var_tmp = concat('Fi', 've', ' plus', NULL, ' four equals nine (', 9, ').');

-- Show the resulting string

select @var_tmp as one_string;

output: 

one_string
--------------------------------
Five plus four equals nine (9).

output:

one_string

--------------------------------

Five plus four equals nine (9).

One thing to always worry about when using TSQL functions…

String Function – CHARINDEX()

April 5, 2013October 11, 2017 admin

I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the CHARINDEX() function today. The CHARINDEX() function takes a expression to find, a expression to search and a optional start position. It returns the first position in which the search string was found. The following example is based upon the television jingle that I heard as a kid for Trix cereal. The example below contains four test cases: Word is found in target string. Word is not found in…

String Function – CHAR()

April 4, 2013October 11, 2017 admin

I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the CHAR() function today. The CHAR() function takes an integer value, ASCII code, from 0 to 255 and returns a single character string. The following example returns the character ‘0’ given the ASCII code of 48.

-- ASCII code for zero
declare @var_tmp int = 48;

-- Return the single character
select char(@var_tmp) as single_char;

-- ASCII code for zero

declare @var_tmp int = 48;

-- Return the single character

select char(@var_tmp) as single_char;

output:

single_char
-----------
0

output:

single_char

-----------

One thing to always worry about when using TSQL functions is how will it react with a UNKNOWN or NULL values?

-- UNKNOWN value returns NULL
select char(NULL) as single_char;

1 2	-- UNKNOWN value returns NULL select char(NULL) as single_char;

output:

single_char
-----------
NULL

output:

single_char

-----------

NULL

Another thing to test is how the function…

String Function – ASCII()

April 3, 2013October 11, 2017 admin

I am going to write a series of very short articles or tidbits on Transaction SQL (TSQL) functions. I will start exploring the string functions today in alphabetical order. Before the invention of UNICODE, we were able to express only 128 different characters with 7 bit ASCII code. Transaction SQL (TSQL) still supports the ASCII() function that takes a CHAR or VARCHAR datatype as input and returns a INT datatype as output. The following example returns the ASCII code for a space which is a value of 32.

-- A simple space
declare @var_tmp char = ' ';

-- Return ascii #
select ascii(@var_tmp) as ascii_code;

-- A simple space

declare @var_tmp char = ' ';

-- Return ascii #

select ascii(@var_tmp) as ascii_code;

output:

ascii_code
-----------
32

output:

ascii_code

-----------

…

Crafting Views with SSMS

March 28, 2013March 28, 2013 admin

Most of the articles that I presented so far for database development have been centered around the exact Microsoft T-SQL syntax to perform a certain operation. At the University of Rhode Island, I learnt how to program in Pascal before typing in my first ANSI SQL statement. In short, I will always be some type of developer in my heart. However, many people do not come from a computer science and math background. The SQL Server Management Studio (SSMS) has menus and dialog boxes to achieve many of the same…

MS Excel -N- Azure SQL Database

March 27, 2013March 28, 2013 admin

I knew Windows Azure SQL Databases have been around for a couple of years but never had the time or need to research them. With a possible data center consolidation in the future, I was wondering if my company could leverage a Windows Azure SQL Databases to reduce the total cost of ownership for certain applications? Today, I will be working with the sample [AUTOS] cloud database that I created in a previous blog. I want to test connectivity to this cloud database using common programs and tools that my…

MS Access -N- Azure SQL Database

March 26, 2013March 27, 2013 admin

Rhode Island BI User Group

March 19, 2013March 21, 2013 admin

I am delighted about the opportunity to present at the Rhode Island Business Intelligence User Group on March 20, 2013. I have not been to Bryant University in a long time. It will be great to see what is new since I last visited. I will be posting an updated version of the presentation with TSQL code before the end of the week. Topic: Effective Data Warehouse Storage Patterns Abstract: Many companies start off with a simple data mart for reporting. As the company grows, users become dependent on the…